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

Question

This question comes close to what I need  but my scenario is slightly different  The source table and destination table are the same and the primary key is a uniqueidentifier  guid   When I try this   insert into MyTable     select   from MyTable where uniqueId    Id    I obviously get a primary key constraint violation  since I m attempting to copy over the primary key  Actually  I don t want to copy over the primary key at all  Rather  I want to create a new one  Additionally  I would like to selectively copy over certain fields  and leave the others null  To make matters more complex  I need to take the primary key of the original record  and insert it into another field in the copy  PreviousId field    I m sure there is an easy solution to this  I just don t know enough TSQL to know what it is

User · Answer

My table has 100 fields  and I needed a query to just work  Now I can switch out any number of fields with some basic conditional logic and not worry about its ordinal position     Replace the below table name with your table name  SQLcolums    SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE  TABLE NAME    TABLE-NAME     Set GetColumns   Conn Execute SQLcolums  Do WHILE not GetColumns eof  colName   GetColumns  COLUMN NAME    Replace the original identity field name with your PK field name  IF colName    ORIGINAL-IDENTITY-FIELD-NAME  THEN   ASSUMING THAT YOUR PRIMARY KEY IS THE FIRST FIELD DONT WORRY ABOUT COMMAS AND SPACES     columnListSOURCE   colName      columnListTARGET     PreviousId field name   ELSE     columnListSOURCE   columnListSOURCE  amp  colName     columnListTARGET   columnListTARGET  amp  colName END IF  GetColumns movenext  loop  GetColumns close      Replace the table names again  both target table name and source table name   edit your where conditions  SQL    INSERT INTO TARGET-TABLE-NAME     amp  columnListTARGET  amp     SELECT    amp  columnListSOURCE  amp    FROM SOURCE-TABLE-NAME WHERE  FIELDNAME   FIELDVALUE    Conn Execute SQL

User · Answer

Specify all fields but your ID field   INSERT INTO MyTable  FIELD2  FIELD3       FIELD529  PreviousId  SELECT FIELD2  NULL       FIELD529  FIELD1 FROM MyTable WHERE FIELD1    Id

User · Answer

I know my answer is late to the party  But the way i solved is bit different than all the answers   I had a situation  i need to clone a row in a table except few columns  Those few will have new values  This process should support automatically for future changes to the table  This implies  clone the record without specifying any column names    My approach is to    Query Sys Columns to get the full list of columns for the table and include the names  of columns to skip in where clause  Convert that in to CSV as column names   Build Select     Insert into script based on this     declare  columnsToCopyValues varchar max    query varchar max  SET  columnsToCopyValues       --Get all the columns execpt Identity columns and Other columns to be excluded  Say IndentityColumn  Column1  Column2 Select  columnsToCopyValues    columnsToCopyValues     name         from sys columns c where c object id   OBJECT ID  YourTableName   and name not in   IndentityColumn   Column1   Column2   Select  columnsToCopyValues   SUBSTRING  columnsToCopyValues  0  LEN  columnsToCopyValues   print  columnsToCopyValues  Select  query   CONCAT  insert into YourTableName     columnsToCopyValues    Column1  Column2  select     columnsToCopyValues      Value1     Value2        from YourTableName where IndentityColumn         searchVariable        print  query exec   query

User · Answer

insert into MyTable  uniqueId  column1  column2  referencedUniqueId  select NewGuid       don t know this syntax  sorry   column1    column2    uniqueId  from MyTable where uniqueId    Id

User · Answer

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

User · Answer

Ok  I know that it s an old issue but I post my answer anyway   I like this solution  I only have to specify the identity column s    SELECT   INTO TempTable FROM MyTable T WHERE id   1  ALTER TABLE TempTable DROP COLUMN id  INSERT INTO MyTable T SELECT   FROM TempTable  DROP TABLE TempTable    The  id -column is the identity column and that s the only column I have to specify  It s better than the other way around anyway   -   I use SQL Server  You may want to use  CREATE TABLE  and  UPDATE TABLE  at row 1 and 2  Hmm  I saw that I did not really give the answer that he wanted  He wanted to copy the id to another column also  But this solution is nice for making a copy with a new auto-id   I edit my solution with the id  as from Michael Dibbets   use MyDatabase   SELECT   INTO  TempTable FROM  MyTable  WHERE  IndexField     id  ALTER TABLE  TempTable DROP COLUMN  IndexField    INSERT INTO  MyTable  SELECT   FROM  TempTable   DROP TABLE  TempTable    You can drop more than one column by separating them with a      The  id should be replaced with the id of the row you want to copy  MyDatabase  MyTable and IndexField should be replaced with your names  of course

User · Answer

I have the same issue where I want a single script to work with a table that has columns added periodically by other developers   Not only that  but I am supporting many different versions of our database as customers may not all be up-to-date with the current version   I took the solution by Jonas and modified it slightly   This allows me to make a copy of the row and then change the primary key before adding it back into the original source table   This is also really handy for working with tables that do not allow NULL values in columns and you don t want to have to specify each column name in the INSERT   This code copies the row for  ABC  to  XYZ   SELECT   INTO  TempRow FROM SourceTable WHERE KeyColumn    ABC   UPDATE  TempRow SET KeyColumn    XYZ   INSERT INTO SourceTable SELECT   FROM  TempRow  DELETE  TempRow    Once you have finished the drop the temp table   DROP TABLE  TempRow

User · Answer

If  key  is your PK field and it s autonumeric   insert into MyTable  field1  field2  field3  parentkey  select field1  field2  null  key from MyTable where uniqueId    Id   it will generate a new record  copying field1 and field2 from the original record

User · Answer

You can do like this    INSERT INTO DENI FRIEN01P  SELECT     RCRDID 112     PROFESION     NAME     SURNAME     AGE      RCRDTYP      RCRDLCU      RCRDLCT      RCRDLCD  FROM     FRIEN01P         There instead of 112 you should put a number of the maximum id in table DENI FRIEN01P

User · Answer

Here is how I did it using ASP classic and couldn t quite get it to work with the answers above and I wanted to be able to copy a product in our system to a new product id and needed it to be able to work even when we add in more columns to the table   Cn Execute  CREATE TEMPORARY TABLE temprow AS SELECT   FROM product WHERE product id    12345    Cn Execute  UPDATE temprow SET product id    34567    Cn Execute  INSERT INTO product SELECT   FROM temprow   Cn Execute  DELETE temprow   Cn Execute  DROP TABLE temprow

User · Answer

I m guessing you re trying to avoid writing out all the column names  If you re using SQL Management Studio you can easily right click on the table and Script As Insert   then you can mess around with that output to create your query

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql