CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Raj
Raj

Reputation: 35

How to insert multiple rows into multiple column of single table

i want to insert multiple rows in multiple columns in single query using two different temp tables named #temp1 having col QID and #temp2 having col RID and i tried like this

insert into table1(ID,quetion,rating) select @ID,QID,RID from #Temp1,#temp2

when i am inserting the data it looking like this

 ID  Quest   Rate
    2   1   5
    2   2   5
    2   3   5
    2   1   4
    2   2   4
    2   3   4
    2   1   3
    2   2   3
    2   3   3

but i want to insert like this

ID  Quest Rate
2   1   5
2   2   4
2   3   3

Thank in Advance:

Upvotes: 0

Views: 2178

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

As table #temp1 and #temp2 don't have common columns you can use CTE and row_number() to get the required results as:

;WITH #temp1tbl (commonid, qid) 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY qid) AS CommonId, 
                qid 
         FROM   #temp1), 
     #temp2tbl (commonid, rid) 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT 1)) AS CommonId, 
                rid 
         FROM   #temp2) 
INSERT INTO table1 
            (id, 
             quest, 
             rating) 
SELECT @ID, 
       qid, 
       rid 
FROM   #temp1tbl 
       LEFT OUTER JOIN #temp2tbl 
                    ON #temp1tbl.commonid = #temp2tbl.commonid

Upvotes: 1

Andomar
Andomar

Reputation: 238088

You could use cross join to insert NxM rows, where N is the number of rows in #Temp1 and M the number of rows in #temp2:

insert into table1(ID, quetion, rating) 
select @ID, t1.QID, t2.RID 
from #Temp1 t1
cross join #Temp2 t2

If the rows in both temporary tables are related, use a regular join:

insert into table1(ID, quetion, rating) 
select @ID, t1.QID, t2.RID 
from #Temp1 t1
join #Temp2 t2
on t1.id = t2.id

This usually inserts less than NxM rows.

Upvotes: 0

Related Questions