Tag Info

New answers tagged

2

Below is an example that uses a recursive CTE to create weekly values and a simple INSERT...SELECT to populate your table. I assumed a few identity IDs for testing's sake in addition to the postdate being the current date. You don't need a cursor to do this work. Enjoy! --CREATE SCENARIO TEMP TABLE IF OBJECT_ID('tempdb..#TempUserPaid','U') IS NOT ...


-1

DECLARE @SQL varchar(8000) SET @SQL = '' SELECT @SQL = @SQL + ' CAST(' + COLUMN_NAME + ' as ' + ISNULL(DATA_TYPE + '(' + CAST(CHARACTER_OCTET_LENGTH as varchar(20)) + ') )','') + ' as ' + COLUMN_NAME + ',' FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'YourPermTable' SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-1) SET @SQL = 'SELECT ' + @SQL + ' FROM ...


1

Have you tested or would it be possible to drop indexes on the destination DB table(s) where you are inserting into, insert those into smaller batched chunks (optimal as indicated above), and then rebuild the indexes on the destination table(s) once all inserts are complete? May be something easy enough to test to confirm.


0

A skipped id can happen in several situation. Keep in mind that an INSERT generally starts by allocating all the ids that it may need. Here's a few examples where AUTO_INCREMENT ids are 'burned': BEGIN; INSERT; ROLLBACK; INSERT IGNORE -- and some of them are ignored due to dup key REPLACE -- DELETE, then INSERT The one I dislike is: SET autocommit = ...


1

If you go to the following stack exchange link and scroll down to the second answer where you see Neeraj Prasad Sharma create a stored procedure named INS, if you go into that stored procedure and modify the line Select @CONDITIONS= 'Select '+@CONDITIONS +'FRom ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition ...


3

This would seem rather simple: INSERT INTO user_flags (user_id, flag) SELECT u.id, 'myflag' -- or whatever to write to this field FROM users u LEFT JOIN user_flags uf ON uf.user_id = u.id WHERE uf.user_id IS NULL; -- to exclude already existing How to .. Select rows which are not present in other table While this is susceptible to race ...


0

In next iteration, you may need to check if there are pending numbers to send sms. If pending count is greater than zero then need to send sms to those pending numbers. track success_count after resending sms to pending numbers. Then, need to increment delivered count and decrement pending count with this success_count. So, delivered = delivered + ...


0

I would normally use the following statement: LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\Users\\Desktop\\nameoffile.csv' REPLACE INTO TABLE `tmp_table` CHARACTER SET latin1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; You should take a look at this page too. http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql


1

Your Original Query INSERT INTO example (a, b, c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c); If you consider (a,b,c) a unique key, there are two things you need to do First, add a unique index ALTER TABLE example ADD UNIQUE KEY abc_ndx (a,b,c); so the table structure would become CREATE TABLE IF NOT EXISTS ...


2

Well this is the insert bit that you are using: INSERT INTO example (a, b, c) VALUES (1,2,3) .... here you are not specifying the id (the primary key to be checked for duplication). Since, it is set to auto-increment, it automatically sets the id for the next row with just the values for columns a, b and c. The row in this case is updated when you ...


2

I think SSIS is really what you want to use here. To just do a very basic import of Excel into SQL Server this is a great link to use - Simple Talk 10 Simple Steps From there, you will then need to use a combination of Conditional Splits as well as MultiCast in order to move the data to different fields/locations depending on your various rules. ...


1

You have PK clustered index as a uniqueidentifier? That index has massive fragmentation. You want a PK that is inserted in the order of the PK Why not just use an identity? Why are are you updating a value rather than just insert the correct value? Are you inserting just one row at a time? One row one round trip is not efficient. Fast inserts with ...


1

Consider batching your commits. A batch size of 1024 is a good starting size. Change batch sizes until you reach your optimum throughput.


1

One option is to create a "linked server". This allows you to access another server tables. To do this you use the server name as a prefix. Before addressing this option verifies with the DBAif is an option allowed in the organization. Also check permissions issues. This link from Microsoft has more information. The following example is taken from the same ...



Top 50 recent answers are included