Data Insert Question

Question

Data Insert Question

apat

SSCertifiable

Points: 5929
More actions
May 12, 2010 at 12:19 pm

#222495

Hi Experts,
Here is my question,
I have one table named Table1 with two columns. One columns is Id (int, identity(1,1)) and another column has records like A, B, C, D etc... There are 50 records in this table.
I have other table named Table2, which has A, B, C, D etc. as columns. So there are 50 such columns which match to the 50 records in Table1. Each column has 100 records.
There is third table where I want to insert data based on above two tables. Third table has three columns. I would like to populate third table choosing Id from table1, with choosing first value e.g. "A" from table1 and all the relevant records from Column A in second table. This will follow for all the 50 records in table1. So basically there will be 50 x 100 = 5000 records populated in third table.
What will be the best solutions for this?

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

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

you will probably need to unpivot the second table to join, but without any DDL and sample data it's hard to give you the code to do it

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi there,

As Mike said, without any information of your system , it is very hard to work on your requirement! But still, i have coded a piece for you here. This will surely get you started:

Now, lets set up the environment:

Sample Tables and Sample Data:

IF OBJECT_ID('TEMPDB..#Col_Ref') IS NOT NULL

DROP TABLE #Col_Ref

CREATE TABLE #Col_Ref

(

ColID INT IDENTITY(1,1) ,

ColName VARCHAR(2)

)

INSERT INTO #Col_Ref (ColName)

SELECT 'A' UNION ALL

SELECT 'B' UNION ALL

SELECT 'C' UNION ALL

SELECT 'AA' UNION ALL

SELECT 'BB' UNION ALL

SELECT 'CC'

IF OBJECT_ID('TEMPDB..#Col_Vals') IS NOT NULL

DROP TABLE #Col_Vals

CREATE TABLE #Col_Vals

(

ColID INT IDENTITY(1,1) ,

A INT,

B INT,

C INT,

AA INT,

BB INT,

CC INT

)

INSERT INTO #Col_Vals (A,B,C,AA, BB,CC)

SELECT 10 , 10 , 10 , 10 , 10 , 10 UNION ALL

SELECT 20 , 20 , 20 , 20 , 20 , 20 UNION ALL

SELECT 30 , 30 , 30 , 30 , 30 , 3 UNION ALL

SELECT 40 , 40 , 40 , 40 , 40 , 4 UNION ALL

SELECT 50 , 50 , 50 , 50 , 50 , 5 UNION ALL

SELECT 60 , 60 , 60 , 60 , 60 , 6 UNION ALL

SELECT 70 , 70 , 70 , 70 , 70 , 7 UNION ALL

SELECT 80 , 80 , 80 , 80 , 80 , 8 UNION ALL

SELECT 90 , 90 , 90 , 90 , 90 , 9 UNION ALL

SELECT 100 , 100 , 100 , 100 , 100 , 100

Now the code for your requirement; this code will

1. reverse-transpose, aka UNPIVOT data in the second table .

2. Join with the first table (Col_Reference table) to get the values and column names in a single table!

Here is the code:

;WITH UNPIVOTDATA AS

(

SELECT COLNAME,COL_VAL FROM

(SELECT ColID,A,B,C,AA, BB,CC FROM #Col_Vals) UNPIVOT_TABLE

UNPIVOT

(COL_VAL FOR COLNAME IN (A,B,C,AA, BB,CC)) UNPIVOT_HANDLE

)

SELECT * FROM

#Col_Ref COLREF

INNER JOIN

UNPIVOTDATA UNPIV_DATA

ON

COLREF.ColName = UNPIV_DATA.COLNAME

Work's done; lets clean-up the envirornment!

IF OBJECT_ID('TEMPDB..#Col_Ref') IS NOT NULL

DROP TABLE #Col_Ref

IF OBJECT_ID('TEMPDB..#Col_Vals') IS NOT NULL

DROP TABLE #Col_Vals

Hope this gets you started! Get back to us if you wish to get any details about this code!

Cheers!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Also please take a cue from my post on how i presented the problem with the necessary sample, te table structures! This wil help the volunteers here immensely so that they produce the best code for you!

I also recommend you going through the following article:

CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

😎