witpo
witpo

Reputation: 465

Sql insert select from – multiple rows with unique column id

I am trying to copy multiple records using one query using insert select from.

Insert into tab_A(colId, col1, col2, col3)
Select colId, col1, col2, col3  form tab_A
Where colId in ( 2,4,6)

Would it be possible to assign different colId for new entries? For example colid 2 should be replaced with 23, 4 with 24 and 6 with 25. How could I achieve it in a single query?

Upvotes: 3

Views: 15246

Answers (5)

Jay Thakkar
Jay Thakkar

Reputation: 1

You could also write case in the select. when 2 then 23 or whatever value.

Upvotes: 0

Jodrell
Jodrell

Reputation: 35696

this would work

Insert into tab_A(colId, col1, col2, col3)
Select 23 , col1, col2, col3  form tab_A Where colId = 2 UNION ALL
Select 24 , col1, col2, col3  form tab_A Where colId = 4 UNION ALL
Select 25 , col1, col2, col3  form tab_A Where colId = 6 

If you give some more info I could provide somthing more reusable. Should/is colId (be) an identity column?


EDIT

This would work in this very specialised case

Insert into tab_A(colId, col1, col2, col3)
Select ((colId - 4) * (-1)) + colId + 20 , col1, col2, col3  
    form tab_A Where colId IN (2, 4, 6)

The function newId = ((oldId - 4) * (-1)) + oldId + 20 is obviously specific to the stated problem.


EDIT2

I suspect somthing like this is more generic approach is appropriate.

DECLARE @MaxColID INT

BEGIN TRANSACTION

SELECT @MaxColID = MAX(ColID) FROM tab_A

INSERT tab_A(colId, col1, col2, col3)
SELECT row + @MaxColID, col1, col2, col3
    FROM
    (
        SELECT ROW_NUMBER() OVER (ORDER BY ColID) row, col1, col2, col3
        FROM tab_A WHERE colID IN (2, 4, 6)
    )

COMMIT

EDIT 3

If you think EDIT 2 is actually what you want then you really want to make ColID an IDENTITY column, then you could do this.

INSERT tab_A (col1, col2, col3)
SELECT col1, col2, col3  FROM tab_A WHERE colId IN (2, 4, 6)

Upvotes: 4

phillyd
phillyd

Reputation: 797

There are a few options:

Add the new ID column to the original table and populate it with the new values before you do this insert, selecting the new ID column instead of the old. This would be the tidiest solution I think.

Alternative - Modify the ID value on the insert based on a rule e.g.

INSERT INTO tab_A(colID, col1, col2, col3)
SELECT colId + 20, col1, col2, col3
FROM tab_A
WHERE colID IN(2,4,6)

Last resort - Process the insert sequentially with a cursor, modifying the ID value each time.

Upvotes: 0

Kshitij
Kshitij

Reputation: 8614

have you just tried adding the disired difference to colId - In your case, since you need to replace 2 by 23, difference is 21.

Insert into tab_A(colId, col1, col2, col3) 
Select colId+21, col1, col2, col3 
form tab_A Where colId in ( 2,4,6) 

Note: I missed the part, that the differnce is not consistent in your case. The proposed solution will work only if difference is same

Upvotes: 0

Diego
Diego

Reputation: 36126

I dont see col4 or col6 in your query, but is this what you want:

Insert into tab_A(colId, col1, col2, col3)
Select colId, col1, 23, col3  form tab_A
Where colId in ( 2,4,6)

Upvotes: 0

Related Questions