How to ? Insert multiple records in a table from a single record in another table

Question

How to ? Insert multiple records in a table from a single record in another table

SteveEClarke

SSCertifiable

Points: 5161
More actions
June 12, 2012 at 9:21 am

#257443

I have a table that contains department and names ;
Department char(20)
Name1 char(20)
Name2 char(20)
Name3 char(20)
Name4 char(20)
Name5 char(20)
Name6 char(20)
I want to be able to read the above table and then insert into another table in one pass and end up with a table that contains ;
Department char(20)
Name char(20)
This will give me multiple departments against the list of names ;
I have read about using Cursors to complete this - But am totally new to using cursors - I have read and re-read exampels of using cursors but am still struggling with the output data.
I have simplified the data above - but if I can get this clear in my own mind then I can expand the project to do what I am after.
Please can somebody help ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

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

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

Jared SSC Guru Points: 61793 More actions · Answer 1

steve.clarke-1124563 (6/12/2012)
I have a table that contains department and names ;
Department char(20)
Name1 char(20)
Name2 char(20)
Name3 char(20)
Name4 char(20)
Name5 char(20)
Name6 char(20)
I want to be able to read the above table and then insert into another table in one pass and end up with a table that contains ;
Department char(20)
Name char(20)
This will give me multiple departments against the list of names ;
I have read about using Cursors to complete this - But am totally new to using cursors - I have read and re-read exampels of using cursors but am still struggling with the output data.
I have simplified the data above - but if I can get this clear in my own mind then I can expand the project to do what I am after.
Please can somebody help ?

Please take a look at the article referenced in my signature. At this point, I see 1 table with 1 column and a second table with 1 column.

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 2

Maybe this is what you are saying?

CREATE TABLE departments (Department char(20),

Name1 char(20),

Name2 char(20),

Name3 char(20),

Name4 char(20),

Name5 char(20),

Name6 char(20))

CREATE TABLE unpivot (Department char(20),

Name char(20))

INSERT INTO departments

SELECT 'Chemistry', 'Suzanne','Joe','Gertrude','Brett','Katie','Rebecca'

AND you want results to be

Department Name

Chemistry Suzanne

Chemistry Joe

Chemistry Gertrude

Chemistry Brett

Chemistry Katie

Chemistry Rebecca

Yeah?

Jared
CE - Microsoft

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 3

Jared,

That is exactly what I am saying .....

Regards

Steve

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Jared SSC Guru Points: 61793 More actions · Answer 4

SteveEClarke (6/12/2012)
Jared,
That is exactly what I am saying .....
Regards
Steve

Try this and also look up PIVOT and UNPIVOT T-SQL

SELECT department, name

INTO #temp

FROM

(SELECT department, name1, name2, name3, name4, name5, name6

FROM departments) p

UNPIVOT

(name FOR colName IN

(name1, name2, name3, name4, name5, name6)

)AS unpvt;

SELECT *

FROM #temp

Jared
CE - Microsoft

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 5

Brilliant - a fine example.

Many Thanks

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.