3

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?

witpo
  • 465
  • 3
  • 11
  • 23

5 Answers5

4

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)
Jodrell
  • 34,946
  • 5
  • 87
  • 124
  • I was using this approach with a single insert. Is there any way to avoid union all ? – witpo Jul 05 '12 at 08:31
  • colId is not an identity column – witpo Jul 05 '12 at 08:32
  • @witpo Do multiple inserts or work out a function for oldID to newID. – Jodrell Jul 05 '12 at 08:34
  • Could you think about some general solution knowing that colId (x,y,z,x) will be replaced with coldId(x1,y1,z1,x1) . Number of columns may change depending on user selection. – witpo Jul 05 '12 at 08:45
  • @witpo, the "right" way to do it is EDIT3 but, the more I think on it, what is the point of duplicating everything but the primary key? – Jodrell Jul 05 '12 at 08:59
  • This example shows a simplified version of my problem. Col1, col2... reference some data from other tables, the purpose is to copy it and allow user to modify it as needed. I wish I could change colId and make it identity column, but I think I will have to go with edit2 option – witpo Jul 05 '12 at 09:10
0

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)
Diego
  • 34,802
  • 21
  • 91
  • 134
0

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

Kshitij
  • 8,474
  • 2
  • 26
  • 34
0

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.

phillyd
  • 777
  • 8
  • 15
0

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

Jay Thakkar
  • 1