Insert new rows based on column values

[scabral79]

Hi,

i have the following table:

Name ID1 ID2 ID3 ID4
Scott 1111 2222 3333 4444
Jim 5555
Joe 6666 7777

I need to create a new table based on these values to look like this:

Name ID
Scott 1111
Scott 2222
Scott 3333
Scott 4444
Jim 5555
Joe 6666
Joe 7777

is there a way to do this in SQL?

thanks

 

[SQLSister]

insert newtable (Name, ID)
select Name, Id1 from oldtable
union all
select Name, Id2 from oldtable
union all
select Name, Id3 from oldtable
union all
select Name, Id4 from oldtable

"NOTHING is more important in a database than integrity." ESquared

 

[scabral79]

that will work for a small table, but i have a table with over 10,000 rows.

Is there a more efficient way to do it?

 

[jbenson001]

10,000 rows is a small table.