Insert multiple records from one row

Question

Insert multiple records from one row

Viewing 5 posts - 1 through 6 (of 6 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

GSquared SSC Guru Points: 260824 More actions · Answer 3

Jinx! 🙂

- 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 4

<---- Buys GSquared a Coke. 🙂

James Leeper
Database Administrator
WDS Global - Americas Region

KS-321165 Default port Points: 1442 More actions · Answer 5

KS-321165

Default port

Points: 1442

October 25, 2010 at 3:04 pm

#1241950

Thanks!