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.
| Author |
Topic |
|
pablito100
Starting Member
3 Posts |
 Posted - 2002-10-03 : 04:07:30
|
| Hello Hi have to insert data into a table, from #tmp to mytable, #tmp has 4 million records and it takes it more than 40 minutes, anyone know a way to make it faster, it's just an insert into with no 'WHERE' clauses, just like this,INSERT INTO MyTable (Field1, Field2,Field3,Field4,Field5)SELECT (Field1, Field2,Field3,Field4,Field5) from #tmpI don't know why it takes so long.More details about MyTable: Field1 to Field5 is the Primary Key and there are 30 more fields which doesn't accept NULLS and the default value is 0.pls Help!!!Thanks in advance.Pablo |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-10-03 : 06:01:58
|
| Your current INSERT statement is one big transaction - this will be slower than a non-logged statement or smaller transactions.You could bcp the data from #tmp out to a text file and bcp/BULK INSERT into mytable. Or use logical breakpoints to create smaller batches of data to insert, eg SELECT * FROM #tmp WHERE id BETWEEN 1 AND 500000.HTH |
 |
|
|
pablito100
Starting Member
3 Posts |
Posted - 2002-10-03 : 06:14:51
|
| The second suggestion sounds very good I'll try it, thanks,, but I didn't understand your first suggestion, what do you mean by "bcp"? |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-10-03 : 06:28:36
|
| bcp (Bulk CoPy) is a utility to copy data into/out of SQL Server.Lookup in Books Online for syntax and full description. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-03 : 07:40:47
|
quote:
Your current INSERT statement is one big transaction - this will be slower than ... smaller transactions.
really? why? how much?Jay White{0} |
|
|
|
pablito100
Starting Member
3 Posts |
Posted - 2002-10-03 : 07:58:07
|
| but what is the difference between INSERTing 4 Million Records from # and INSERTing 4 million records from a text file? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-03 : 08:33:12
|
quote:
but what is the difference between INSERTing 4 Million Records from # and INSERTing 4 million records from a text file?
Logging. If your db configuration allows non-logged operations, bcp'ing in from a text file will not be logged.Jay White{0} |
|
|
|
|
|
|
|
|
Problems with INSERT INTO