Insert multiple records from one row

Question

Post reply

Insert multiple records from one row

KS-321165

Default port

Points: 1442
More actions
October 25, 2010 at 2:06 pm

#228418

Hi,

I have the following record in the source table:

EmpID Type1 Type1Value Type2 Type2Value Type3 Type3Value

123456 1 11111 2 22222 3 33333

Now for each of the Types (1,2,3), I need to insert a record in the source table so it looks like:

EmpID AssetType AssetValue

123456 1 11111

123456 2 22222

123456 3 33333

Is this easy to do? How do I do this?

Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

There are a number of different ways to do this. Perhaps the easiest is to import it into a staging table, and do a select with Unions to select each column into its own rowset.

Something like:

select EmployeeID, Type1, Type1Value



from MyStagingTable

union all

select EmployeeID, Type2, Type2Value

from MyStagingTable

...etc

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

james.leeper SSC Veteran Points: 274 More actions · Answer 2

The easiest way is something like this:



INSERT INTO Table2 (EmpID, AssetType, AssetValue)

SELECT EmpID, Type1, Type1Value

  FROM Table1

 UNION ALL

SELECT EmpID, Type2, Type2Value

  FROM Table1

 UNION ALL

SELECT EmpID, Type3, Type3Value

  FROM Table1

James Leeper

DBA/DB Developer

WDS

James Leeper
Database Administrator
WDS Global - Americas Region