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 |
|
achamarthy
Starting Member
26 Posts |
 Posted - 2004-08-09 : 11:43:47
|
| My table structure and some some sample data is Col1 | Col2 | Col3 | Col4 | Col5 | Col6---------------------------------------a1 b1 c1 d1 30 <NULL>a1 b1 c1 d1 65 <NULL>the possible values for the column col5 are from 1 to 70. My task is to create new records with same values for first four columns and all possible new values for col5. After new inserts the table should look like below.a1 b1 c1 d1 1 <NULL>a1 b1 c1 d1 2 <NULL>a1 b1 c1 d1 3 <NULL>a1 b1 c1 d1 4 <NULL>a1 b1 c1 d1 5 <NULL>...a1 b1 c1 d1 30 <NULL>....a1 b1 c1 d1 65 <NULL>..a1 b1 c1 d1 70 <NULL>There are around 500,000 records in the present table and now i need to insert new records for every combination of teh first four columns...What is the best of doing that? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-09 : 11:50:22
|
| maybe cross join??Go with the flow & have fun! Else fight the flow :) |
 |
|
|
achamarthy
Starting Member
26 Posts |
Posted - 2004-08-09 : 12:09:52
|
| What do you mean by cross join? I'm new to DB programming and a not aware of that..Can you explain that? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-09 : 12:26:31
|
| A CROSS JOIN operator is analogous to an INNER JOIN operator without a condition. run select * from table1, table2on two of your tables and see what you get.you basically get all possible data combinations for two tables.Go with the flow & have fun! Else fight the flow :) |
|
|
|
achamarthy
Starting Member
26 Posts |
Posted - 2004-08-09 : 13:41:08
|
| I dont have two tables..I just have only one table TempA and i need to append the new rows into the same table....When i use Cursors and while loop to do that, it is taking AGES...does anyone have any idea? Thanks in advance.. |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-08-09 : 16:09:20
|
| Try this:Select TOP 70 ID = identity(int,1,1) Into #70NumbersFrom Sysobjects s1, sysobjects s2Select distinct col1, col2, col3, col4, ID Col5, NULL Col6 from MyTable, #70Numbers--Ken"Knowledge is a process of piling up facts; wisdom lies in their simplification." |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-10 : 07:24:55
|
| Other issue to be aware of....is LOGGING....inserting 500,000 records without a COMMIT will make a VERY LARGE LOG....and a long TRANSACTION LOGGING problem.Look to break this 'insert' up into multiple smaller phases to improve the performance. |
|
|
|
|
|
|
|
|
how can i insert multiple records efficiently?