Insert Into

[NotSQL]

Hi,

Im having a problem inserting data into a table from another table and was wondering if anyone can help.

My table looks like this.

ConNum Year1 Year2 Year3 Year4
1234 667 886 444 337
5793 442 246 254 0
7633 766 543 764 235

However I need to insert it into a table which will end up looking like this..

ConNo Value Year
1234 667 1
1234 886 2
1234 444 3
1234 337 4
5793 442 1
5793 246 2
5793 254 3
7633 766 1

And so on, however when one of the years get to 0 i dont need to insert it into the new table.

Any help would be appreciated.
Thanks

 

[gmmastros]

Look in to the Union operator.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gixonita]

Hi,

you can try:

INSERT INTO Your_Second_Table

SELECT   ConNum,
         Year1,
         1

FROM     Your_First_Table

WHERE    Value > 0

UNION ALL

SELECT   ConNum,
         Year2,
         2

FROM     Your_First_Table

WHERE    Value > 0
:
:
:
-- For All The Different Years

Hope this helps

 

[NotSQL]

I have this, is this the simplest way of doing it?

insert into dbo.ConValueReal (ConNumber, [Value],[Year])
Select ConNum, Year1, 1
from dbo.ConValue
Where Year2 <> 0
UNION ALL
Select ConNum,Year2, 2
from dbo.ConValue
Where Year2 <> 0
UNION ALL
SELECT ConNum, Year3, 3
from dbo.ConValue
Where Year3 <> 0
UNION ALL
SELECT ConNum, Year4, 4
from dbo.ConValue
Where Year4 <> 0

 

[NotSQL]

Sorry Gixonita, didnt see your post. Thanks