Quickest way to insert data to a table

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 2005 Forums

SQL Server Administration (2005)

Quickest way to insert data to a table

Author

Topic

chih
Posting Yak Master

154 Posts

Posted - 2009-07-27 : 22:13:52

Hi all,

Is there a way to insert data to a table faster? Due to we often need to insert large amount of data to a table, it creates locks and logs during insert.

I was thinking to insert the data to a file and then use bulk insert statement to import data back to the table. but i don't think it is the best way to do so.

Any suggestions? Thank you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-27 : 22:19:57

The best way is to remove indexes and bulk-insert and reapply indexes once done.

chih
Posting Yak Master

154 Posts

Posted - 2009-07-27 : 22:27:03

The issue is we have more than 10 millions records in the table. It can take a while to creat index

quote:
Originally posted by sodeep

The best way is to remove indexes and bulk-insert and reapply indexes once done.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-28 : 00:20:13

best way is still going to be bulk insert. how many non-clustered indexes do you have? can source be pre-sorted into clustered index order?

chih
Posting Yak Master

154 Posts

Posted - 2009-07-28 : 00:43:05

The table has 2 non clustered index.
I am still newbie, can you give me an example about how you implement bulk insert?
Is that possible to bulk insert data from table to table?
Thank you

quote:
Originally posted by russell

best way is still going to be bulk insert. how many non-clustered indexes do you have? can source be pre-sorted into clustered index order?

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-28 : 02:38:25

quote:
can source be pre-sorted into clustered index order?

Hi

Am not aware of this...can any one explain this..

-------------------------
R..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-28 : 02:43:05

quote:
Originally posted by chih

The table has 2 non clustered index.
I am still newbie, can you give me an example about how you implement bulk insert?
Is that possible to bulk insert data from table to table?
Thank you
quote:
Originally posted by russell

best way is still going to be bulk insert. how many non-clustered indexes do you have? can source be pre-sorted into clustered index order?

Is that possible to bulk insert data from table to table?

Insert bulk data from a table is simple

Select * into target_tbl from source_tbl

Bulk insert from other sources..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-28 : 02:46:37

quote:
Originally posted by rajdaksha

quote:
can source be pre-sorted into clustered index order?

Hi

Am not aware of this...can any one explain this..

-------------------------
R..

Its nothing Raj,

The data in the source must be in order

if the source data is in table it must be in a order of clustered index(Primary key), then the insert will be as fast..

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-28 : 02:49:42

Hi

Ohh....I got .....thanks.....

-------------------------
R..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-28 : 02:50:22

quote:
Originally posted by rajdaksha

Hi

Ohh....I got .....thanks.....

-------------------------
R..

Ya Welcome!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

chih
Posting Yak Master

154 Posts

Posted - 2009-07-28 : 02:56:14

how if the target table has data already? don't think you can use select into.

quote:
Originally posted by senthil_nagore

quote:
Originally posted by chih

The table has 2 non clustered index.
I am still newbie, can you give me an example about how you implement bulk insert?
Is that possible to bulk insert data from table to table?
Thank you
quote:
Originally posted by russell

best way is still going to be bulk insert. how many non-clustered indexes do you have? can source be pre-sorted into clustered index order?

Is that possible to bulk insert data from table to table?

Insert bulk data from a table is simple

Select * into target_tbl from source_tbl

Bulk insert from other sources..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-28 : 03:00:33

Ya ofcourse if the target is already in DB we can use Select into. I did't know about your exact requirement friend!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-28 : 03:08:57

hi chih

Am not sure...

Remove indexes , change the recovery model to bulk logged and bulk-insert . Finally reapply indexes once done and change the recovery model.

-------------------------
R..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-28 : 03:19:42

One you change multiple indexes and do bulk import

It is advisable to run the following DBCC Commands

DBCC DBREINDEX

DBCC CHECKTABLE

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-28 : 03:41:38

Hi chih

Some fully logged, large-scale index operations can generate large data loads that can cause the transaction log to fill quickly whether the operation is executed offline or online. This can affect performance. You can have these index operations be minimally logged by setting the recovery model of the database to bulk-logged or simple for the duration of the index operation. Minimal logging is more efficient than full logging and reduces the chance of the index operation filling the log space.

-------------------------
R..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources