BULK INSERT SCRIPT PLS

Question

BULK INSERT SCRIPT PLS

Agnii

Ten Centuries

Points: 1192
More actions
August 11, 2014 at 1:48 pm

#294360

Hi, I need to use Bulk insert statement for copying a table with 200 million rows to another table on the same server...the table has no primary key or identity column....can someone provide the script for BULK INSERT please...am pretty new to scripting.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Doug J. SSC Eights! Points: 832 More actions · Answer 1

http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(BULK_INSERT_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true

-Doug

http://xkcd.com/327/

Agnii Ten Centuries Points: 1192 More actions · Answer 2

Thanks for the input doug...but do you think I posted the question without checking/searching this in msdn.microsoft?? 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

So if you've checked MSDN, seen the syntax description and looked at the examples at the bottom of the page, what specifically are you stuck with?

What have you got so far (from the documentation) and what isn't it doing that it should be?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Doug J. SSC Eights! Points: 832 More actions · Answer 4

v4vaas 19815 (8/11/2014)
Thanks for the input doug...but do you think I posted the question without checking/searching this in msdn.microsoft?? 🙂

Yes.

-Doug

http://xkcd.com/327/

Lowell SSC Guru Points: 323518 More actions · Answer 5

your target table needs to have the same number of columns, ideally, for this simple example

CREATE TABLE BULKACT(SomeColumns VARCHAR (30),MoreColumns varchar(30))

BULK INSERT BULKACT FROM 'c:\Export_o.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW = 1

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Agnii Ten Centuries Points: 1192 More actions · Answer 6

Gail/Doug: As I mentioned, am pretty new to sql scripting....and am not sure what FROM 'data_file' in the statement listed. I don't have a file...I need to import rows from one table to another table on the same server.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

That's not what BULK INSERT does.

Bulk insert, specifically and only, loads data from a file (text file usually, or comma-separated value) in the file system into a database table. It's for times when someone sends you a file and tells you to load that data into the database.

If you're copying data from one table to another, you don't want bulk insert.

A simple INSERT INTO ... SELECT ... FROM will do the job.

Have a stab at it (with reference to the docs as needed) and post if you get stuck with exactly what you're stuck with.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Agnii Ten Centuries Points: 1192 More actions · Answer 8

Ok Thank you. Thing is, I need to use BULK INSERT for some performance tests...so, am thinking I can create a file (text or csv) with all the records of Table 1 and save it on server and then use Bulk insert to import the data from that file into Table 2...hope I got it right.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Why?

If you're copying data from one table to another on the same server, you would use INSERT ... SELECT, not bulk insert. Bulk insert is for when you're starting with a file, it's the wrong tool for when you're copying data from one table to another.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

v4vaas 19815 (8/11/2014)
Hi, I need to use Bulk insert statement for copying a table with 200 million rows to another table on the same server...the table has no primary key or identity column....can someone provide the script for BULK INSERT please...am pretty new to scripting.

Quick thought, use SSIS, you'll have an option for bulk inserting there.

😎

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 11

v4vaas 19815 (8/11/2014)
Ok Thank you. Thing is, I need to use BULK INSERT for some performance tests...so, am thinking I can create a file (text or csv) with all the records of Table 1 and save it on server and then use Bulk insert to import the data from that file into Table 2...hope I got it right.

Well, if what you want to test is the performance of a BULK INSERT from a file, then yes, this is what you need to do. 😀

As was previously noted, your Table 2 must be defined with the same number of columns represented in your file and the data types of those columns must be appropriate for the data in the file for a simple BULK INSERT to work. Otherwise, you'll need a format file - see BOL for guidance.

bcp will probably be the way to go to get your 200 million rows into a flat file.

Jason Wolfkill

Agnii Ten Centuries Points: 1192 More actions · Answer 12

Thank you all for the inputs...I was able to perform the test using BULK Insert