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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-03-02 : 08:00:37
|
| Ryan writes "I am inserting 1000 rows for each single entry on a source table. I have 410,000 rows in the source table so I should end up with 410 million rows in the other table. Ok. So what i do is use the cursor to step through the source table and insert a row for each of the 410 million rows i will insert. So in effect I am executing 410 million inserts. Would it be faster to generate 1000 rows in a temp table and then execute one insert into the main table that inserts 1000 rows?Thanks,Ryan" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-02 : 09:04:02
|
| not sure what the heck you are doing, but a CROSS JOIN is all you need. Create 1 table with 1000 rows (it can be a temp table). Then just cross join the 1000 row table with your 410,000 table. The result of a cross join is all combinations of rows from both tables, so the result is 410,000,000 rows. So you'd have:INSERT INTO DestinationTable (list of columns)SELECT (list of columns)FROM Your410ThousandRowTableCROSS JOIN Your1000RowsTableIn the table of 1000s rows, you shoud store whatever info you need to be able to produce the results -- i.e., if you want to number each row copy from 1-1000, then store numbers 1-1000 in your 1000 row table.Keep in mind that the pyshical writing of 410,000,000 rows, no matter how efficient your SQL is, will take a LONG time. The bottleneck will end up being I/O as SQL Server tries to log the entire insert and write the rows to the disk, as well as updating indexes and such if needed. Again, I am really curious as to why you feel this is necessary or is a good idea. YOu can just keep your 410,000 row table as is and do CROSS JOIN's on the fly to produce the results as needed in a SELECT statement or in a VIEW, w/o having to physically store copies of your data.- Jeff |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-02 : 21:51:37
|
| Not to mention you'll almost certainly fill up the disk for both the database and the log files. With luck the process will simply abort (and take just as long to roll back); if you're unlucky your SQL Server will freeze and you'll have to reboot to get it going again (losing all the work that was done) |
|
|
|
|
|
|
|
|
cursor insert