Importing data

Question

Importing data

raym

SSCrazy

Points: 2213
More actions
December 5, 2007 at 2:17 pm

#184526

I have these two statements. Both work. My question is if I wanted to import using statement 2 how would I batch it ?
--Truncate Table
TRUNCATE TABLE dbo.Test
--Insert Data Into Table
BULK INSERT dbo.Test FROM 'D:\Test.txt' WITH (BATCHSIZE = 100000,FIELDTERMINATOR = ',',ROWTERMINATOR = '')
INSERT tb_Test EXECUTE MASTER..xp_cmdShell 'Type \\M012edr\daily$\INFILES\TEST.TXT'
nb: are there any scripts or articles to insert,update,delete data in batches for large tables

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 1

in second option batch is not possible.....

BTW....did u tied second statement....it should fail cause all data will go in first row/ first column only....over 8k bites it will fail

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Besides, I believe that the first script will probably blow the doors off the second script.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

BTW....did u tied second statement....it should fail cause all data will go in first row/ first column only....over 8k bites it will fail

Prakash... did YOU try it? Your statement is not correct... it will NOT go to first row/ first column only... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Ray...

If, for some reason, you elect to go with the second form, you may also want to check out the following command as a possible source for your Insert...

EXECUTE MASTER.dbo.xp_ReadErrorLog 1, '\\M012edr\daily$\INFILES\TEST.TXT'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)