Bulk Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Bulk Insert

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-20 : 09:33:48

I need to copy a large amount of data from one ERP database to another. All of the table structures are the same. I attempted this by doing a

TRUNCATE DestDB.DestOwner.DestTable
INSERT INTO DestDB.DestOwner.DestTable
(SELECT * FROM DestDB.DestOwner.DestTable)

This took over 10 hours and eventually failed when my transaction log filled up.

Is Bulk Insert the best way to do this? If so, can you give me an example? Isn't there a better way where I do not have to export the data to a tab delimited file first?

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-20 : 12:52:17

Yes BULK INSERT is the best way to do it. Example from BOL:


BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )

You could use DTS if you don't want to create a file. But BULK INSERT is faster.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-21 : 10:32:39

Seems odd that this is the only way to do this. How do I create the input file from a table? Can you give me an example of using BCP via SQL? Why does Bulk Insert have to come from a flat file instead of another SQL table? Of course, I may also ask why the sky is blue?

TimChenAllen
Starting Member

45 Posts

Posted - 2003-11-21 : 11:07:10

quote:
Originally posted by Ken Blum

I need to copy a large amount of data from one ERP database to another. <snip>
This took over 10 hours and eventually failed when my transaction log filled up.

Is Bulk Insert the best way to do this? If so, can you give me an example? Isn't there a better way where I do not have to export the data to a tab delimited file first?

I do stuff like this with DTS-- mainly because it's easier to leave in a clients' hands to do later. You can even schedule a DTS to run at night, making it more ID10T-proof.

One thing, and this might affect you in your current method: Does the destination table have any indices when you try to select into it? You should drop those and then recreate them after the data load is finished. Use enterprise manager to get the DDL for the indices and put this as a task in the same DTS you load the data with.

--
Timothy Chen Allen
[url]http://www.timallen.org[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-21 : 13:09:52

Keep in mind that DTS uses BULK INSERT as well.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-21 : 13:13:37

But I would still have to have flat files to import from correct?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-21 : 13:18:24

No, DTS does that stuff behind the scenes. With DTS, you can copy data from one table to another (even if it is on another server).

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-21 : 13:23:20

So if I setup a DTS table copy from one db to another db it will automatically use BULK INSERT?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-21 : 13:25:22

Yes, from what I understand at least. You can see it happening if you use SQL Profiler. But when you configure the DTS package, you will not need to specify a file.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-21 : 13:29:48

Cool. I'll try it - Thanks again Tara.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-21 : 20:04:52

quote:
Keep in mind that DTS uses BULK INSERT as well.

Only if you use a bulk copy task. A regular data transformation doesn't (it *might* if text files are used, but probably not)

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-24 : 13:33:48

OK, just where is this "Bulk Copy" Task in DTS? There is a "Bulk Insert" Task but that requires entry of a flat file as a source.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-24 : 13:43:43

Third row, on the right

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-24 : 14:33:43

are both databases SQL databases? why not just used a linked server and SELECT from one and INSERT right into the other?

- Jeff

TimChenAllen
Starting Member

45 Posts

Posted - 2003-11-24 : 14:39:16

quote:
Originally posted by jsmith8858

are both databases SQL databases? why not just used a linked server and SELECT from one and INSERT right into the other?

- Jeff

I've done this both ways-- does anyone know the performance comparison between doing a straight SQL Server-Connection to SQL Server-Connection transformation VS. selecting from a linked SQL Server to a local table? Thanks.

--
TimothyAllen
[url]http://www.timallen.org[/url]

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-24 : 14:58:24

To Jeff: Did that - too slow & would fill my transaction log on an overnight process.
To Brett - Is that the one titled "Copy SQL Server Objects" Task?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-24 : 15:40:45

Doh! sorry Ken, you totally told us that in your first post ... sorry !!!!!

- Jeff

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-24 : 15:46:55

What settings should I have in profiler to tell if a DTS execution is using Bulk Insert?

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-11-24 : 16:22:45

How can I get a Copy Object to work between databases where the object owner is different? i.e Bulk Insert data from Database1.DbOwner1.TableA into Database2.dbOwner2.TableA?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-24 : 17:07:25

Why not

select * INTO newTable from LinkedServer.DB..t

???

Then drop oldTable and rename newTable..

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-24 : 17:24:44

Ken,

Here is what I would do (and reguarly do)...

You must ensure that the two tables schema's are identical.. Collations, data type sizes, everything..

BCP OUT using the native format option (No need for format tables) and then BCP IN using lock hint.. Look in BOL for details
I can gurantee this will be blindingly fast...

You could fully automate this with a simple DOS batch file.. Use OSQL to truncate and then your BCP commands...

Stoad, way to slow mate....

DavidM

"SQL-3 is an abomination.."

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources