Insert two records for each record

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

SQL Server Development (2000)

Insert two records for each record

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-18 : 09:50:00

I have a junction-table with id's and for every row in this table I need to insert the same rows in another table but with alternating 0's and 1's. How does one do that? I could do it using a loop, but it takes quite long and I wanna know how to do such a thing the good way.

--> Original table:
MarketID | CompID
--------------------
15         22
16         78

--> New table
MarketID | CompID | Request
---------------------------
15         22       0
15         22       1
16         78       0
16         78       1

And the order needs to be exactly the same in the new table as in the original.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-10-18 : 09:52:08

You could do something like this.

INSERT INTO [New Table]
SELECT [Old Table].MarketID, [Old Table].CompID, 0
FROM [Old Table]
UNION ALL
SELECT [Old Table].MarketID, [Old Table].CompID, 1
FROM [Old Table]

Dustin Michaels

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-18 : 09:53:36

insert newtbl
select t1.MarketID, t1.CompID, t2.i
from originaltbl t1
cross join (select i = 0 union all select i = 1) t2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-18 : 09:57:07

WOW! 3 mins and 36 secs to get two replies must be a record! Nigels reply is somthing in the line of what I was looking for, have to test it.

Dustin: wouldn't the union all make the first query finish first and then the second one, ruining the order?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-18 : 10:11:21

Yup, as I suspected...Nigels solution worked brilliantly and allthough Dustins solution inserted all the right records the order was messed up by the union all. But thanx alot to both of you! Have to get the hang of those cross joins some day...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-10-18 : 10:15:55

Would this work then??

INSERT INTO [New Table]
SELECT [Old Table].MarketID, [Old Table].CompID, 0 AS Request
FROM [Old Table]
UNION ALL
SELECT [Old Table].MarketID, [Old Table].CompID, 1 AS Request
FROM [Old Table]
ORDER BY [Old Table].MarketID ASC, [Old Table].CompID ASC, Request ASC

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-18 : 10:18:08

rows in a table aren't ordered.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources