Copy Row But With New Id

Copy row but with new id

Let us say your table has following fields:

( pk_id int not null auto_increment primary key,
  col1 int,
  col2 varchar(10)
)

then, to copy values from one row to the other row with new key value,
following query may help

insert into my_table( col1, col2 ) select col1, col2 from my_table where pk_id=?;

This will generate a new value for pk_id field and copy values from col1, and col2 of the selected row.

You can extend this sample to apply for more fields in the table.

UPDATE:

In due respect to the comments from JohnP and Martin -

We can use temporary table to buffer first from main table and use it to copy to main table again.
Mere update of pk reference field in temp table will not help as it might already be present in the main table. Instead we can drop the pk field from the temp table and copy all other to the main table.

With reference to the answer by Tim Ruehsen in the referred posting:

CREATE TEMPORARY TABLE tmp SELECT * from my_table WHERE ...;
ALTER TABLE tmp drop pk_id; # drop autoincrement field
# UPDATE tmp SET ...; # just needed to change other unique keys
INSERT INTO my_table SELECT 0,tmp.* FROM tmp;
DROP TEMPORARY TABLE tmp;

Hope this helps.

Copy row with one field changed and new autogenerated id in MySQL

SELECT * is antipattern. You should explicitly set columns:

INSERT INTO original(col1, col2, ...)  --skip id column
SELECT col1, col2, ...
FROM temp1;

Or even better skip the temp1 table at all. Single statement solution:

INSERT INTO original(col1, col2, ..., field) -- skip id column
SELECT col1, col2, ..., 'new_value'
FROM original
WHERE field='old value';

---

To get what you want you need mechanism like Oracle DEFAULT Values On Explicit NULLs.

But even then you need to drop column from temp1, because:

INSERT INTO original  -- only N column
SELECT NULL as id, *  -- this will return N+1 columns
FROM temp1;

So you have to use:

ALTER TABLE temp1 DROP COLUMN id;
-- now columns match

INSERT INTO original  -- only N column
SELECT NULL as id, *  -- this will return N columns and
                      -- NULL is handled by DEFAULT ON NULL
FROM temp1;

How do you copy a record in a SQL table but swap out the unique id of the new row?

Try this:

insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

Any fields not specified should receive their default value (which is usually NULL when not defined).

SQL Server : copy row and Insert into to the same table but with a different ID

Since LOAD_NO is an identity column, you should not be specifying a value for it when inserting. So you need to modify our INSERT statement to not include the LOAD_NO column - something like this:

INSERT INTO LOADS(col1, col2, ..., colN)  -- all columns *EXCEPT* LOAD_NO
   SELECT col1, col2, ...., colN          -- all columns *EXCEPT* LOAD_NO 
   FROM LOADS_TempTable;

SQL Server will automatically assign a new LOAD_NO to the row you're inserting - after all, that's the job of the identity column!

Copy row, and dependent rows in another table, and dependent rows in another, etc

You need to capture the new 'id' values as you insert into each table to use them for the foreign key references in the child tables. The most reliable way to capture the new 'id's is using the SQL output clause as that will always give the correct ids and can handle multiple ids (many of the other approaches to obtaining the newly created record id have pitfalls worth avoiding).

You also want to wrap it in a transaction to ensure its an all or nothing operation (you may also want additional error handling).

Because the output clause on an insert statement doesn't allow values from the original table I've used a merge statement for the second part, with a match condition of 1=0 i.e. it will never match and therefore always insert, and the merge statement does allow values from the original table in the output clause.

declare @Id int = 123;

declare @ItemOutput table (IdNew int);
declare @ItemOptionOutput table (IdNew int, IdOld int);

begin tran;

insert into dbo.items (title)
  output Inserted.id into @ItemOutput
  select title
  from dbo.items I
  where I.id = @Id;

MERGE INTO dbo.item_options as [Target]
USING (select id, [Option], (select IdNew from @ItemOutput) from dbo.item_options) AS [Source] (id, [Option], IdNew)
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (itemID, [Option])
  VALUES ([Source].IdNew, [Source].[Option])
  OUTPUT Inserted.id, [Source].id
  INTO @ItemOptionOutput (IdNew, IdOld);

insert into dbo.sub_options (item_optionsID, subOption)
  select (select O.IdNew from @ItemOptionOutput O where O.IdOld = SO.ID), SubOption
  from dbo.sub_options SO
  where SO.id in (select O.id from dbo.item_options O where O.ItemID = @Id);

commit;

Copy rows from the same table and update the ID column

INSERT INTO ProductDefinition (ProdID, Definition, Desc)
SELECT
  xxx, Definition, Desc
FROM
  ProductDefinition
WHERE
  ProdID = yyy

The xxx is your new ProdID and the yyy is your old one. This also assumes that DefID is automagically populated on INSERT.

How to copy rows with SQL and get new and old IDs as result?

With helpful links from Andriy M's link to 'How to copy tables avoiding cursors in SQL?', I managed to come up with this very elegant solution:

DECLARE @t TABLE (oID int, nID int);

MERGE T s
USING (
        SELECT TID, name, address
        FROM T [s]
      ) d on 0 = 1
WHEN NOT MATCHED
THEN INSERT (name, address)
    VALUES (name, address)
OUTPUT d.TID as oID, Inserted.TID as nID
INTO @t;

---

Related Topics