CODEWITHSUNDEEP
sqlsql-servert-sql
GroundIns
GroundIns

Reputation: 551

Duplicate SQL records to the same table with manually auto increment id

I'm trying to write a query which will return those records: 

select * 
from [CloneConfiguration] 
where InstrumentId = 2

enter image description here

and insert them into the same table with changing the following columns:

  1. Id - the new record will need a unique id number (because it is the primary key without that it defined as auto increment)

  2. Instrument id - change the instrument id to another number (3 for example)

I tried the following query which doesn't work.

INSERT INTO [CloneConfiguration] 
   SELECT 
       MAX(Id) + 1, 3,
       [SourceCCy1Id], [SourceCCy2Id], [SourceProviderId], 
       [TargetCCy1Id], [TargetCCy2Id], [TargetProviderId], [Remark]
   FROM 
       [CloneConfiguration] 
   WHERE 
       InstrumentId =2

Error:

Column 'CloneConfiguration.SourceCCy1Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 439

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15140

This is assuming sql-server, but I guess you get the point anyway:

DECLARE @MAXID INT = (SELECT MAX(Id) FROM [CloneConfiguration]) -- You probably want to number from the highest Id regardless of InstrumentId

INSERT INTO [CloneConfiguration] 
SELECT @MAXID + ROW_NUMBER() OVER(ORDER BY Id)
,      3
,      [SourceCCy1Id]
,      [SourceCCy2Id]
,      [SourceProviderId]
,      [TargetCCy1Id]
,      [TargetCCy2Id]   
,      [TargetProviderId]
,      [Remark]
FROM   [CloneConfiguration] 
WHERE  InstrumentId=2

The idea is to first get the MAX(Id) currently in the table, and add a ROW_NUMBER based on the selected Id's.

By the way, it's also a good idea to name the columns you want to insert into:

INSERT INTO [CloneConfiguration] (Id, InstrumentId...)
...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can do what you want in a single query by doing:

INSERT INTO [CloneConfiguration] 
    SELECT COALESCE(m.maxid + 1, 1), 3, [SourceCCy1Id], [SourceCCy2Id],
           [SourceProviderId], [TargetCCy1Id], [TargetCCy2Id], 
           [TargetProviderId], [Remark]
    FROM [CloneConfiguration] CROSS JOIN
         (SELECT max(id) as maxid FROM CloneConfiguration) m
    WHERE InstrumentId = 2 ;

If you are inserting multiple rows, then use row_number() as well:

INSERT INTO [CloneConfiguration] 
    SELECT COALESCE(m.maxid, 0) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
           3, [SourceCCy1Id], [SourceCCy2Id],
           [SourceProviderId], [TargetCCy1Id], [TargetCCy2Id], 
           [TargetProviderId], [Remark]
    FROM [CloneConfiguration] CROSS JOIN
         (SELECT max(id) as maxid FROM CloneConfiguration) m
    WHERE InstrumentId = 2 ;

That said, the correct solution is to define the id to be an identity column. Then the database takes care of assigning a unique id. Your queries also will not have race conditions. So, the above work if there is only one user, but can fail if there are multiple users.

Upvotes: 2

Related Questions