inserting duplicate records with SQL

sql, sql-server, stored-procedures

Solution

However horrible it seems, I would recommend using queries that specify the full column list. But you don't need to type in all the fields yourself: using SQL Server Management Studio, right click on the table in Object Explorer and choose the menu item "Script Table as | INSERT To | New Query Window".

This will give you something like this:

INSERT INTO [dbo].[TableOfficerLogs]
           ([UniqueColID]
           ,[OtherField1]
           ,[OtherField2] -- etc.
           ,[OtherFieldN]
     VALUES
           (<UniqueColID, int,>
           ,<OtherField1, nvarchar(200),>
           ,<OtherField2, nvarchar(200),> --etc.
           ,<OtherFieldN, nvarchar(200),>)

Don't let this scare you-- you still won't type all the VALUES manually. Now leave that window alone for a sec and go back to Object Explorer. Use the context menu on the same table, to get another script: "Script Table as | SELECT To | New Query Window". This will be a totally standard select list, with all your fields listed out. Copy the whole query and paste it in over the VALUES clause in your first query window.

This will give you a complete INSERT ... SELECT query with all the fields listed out. Now, just replace UniqueColID in the SELECT portion as appropriate.

In addition, if you know your new UniqueColID values in advance, join to them (represented as a table-valued parameter, perhaps, if you do all this in a sproc) in the FROM clause: you'll end up with as many duplicates as you like, each with the ID you specify.

UPDATE:

What the two "Script Table as" operations will get you is something like this, with all fields listed:

INSERT INTO [dbo].[TableOfficerLogs]
               ([UniqueColID]
               ,[OtherField1]
               ,[OtherField2] -- etc.
               ,[OtherFieldN]
SELECT [UniqueColID]
      ,[OtherField1]
      ,[OtherField2] -- etc.
      ,[OtherFieldN]

Now, to actually do the copy, you tinker with it. Your code might end up looking like this:

CREATE PROCEDURE [dbo].[InsertDuplicateFields]        
    (@UniqueColID varchar(50), @NewUniqueColID varchar(50))         
AS    

INSERT INTO [dbo].[TableOfficerLogs]
               ([UniqueColID]
               ,[OtherField1]
               ,[OtherField2] -- etc.
               ,[OtherFieldN]
SELECT @NewUniqueColID
      ,[OtherField1]
      ,[OtherField2] -- etc.
      ,[OtherFieldN]
WHERE UniqueColID = @UniqueColID

This will duplicate the specified row and substitute the new ID for the old one. Of course, you would repeat this process for each of your 8 tables.

Problem

I am duplicating a record by searching UniqueColID and inserting the results back into the table....Doing so I have a duplicate entry which is what I want EXCEPT I want ONE column to not be duplicate and that column is UniqueColID.. Below is my code: ``` CREATE PROCEDURE [dbo].[InsertDuplicateFields] (@UniqueColID varchar(50), @IndividualID varchar(50) = null) AS Begin INSERT INTO TableCollisionBegin SELECT * FROM TableCollisionBegin WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableCollisionDetails SELECT * FROM TableCollisionDetails WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableCollisionLocation SELECT * FROM TableCollisionLocation WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableDriver SELECT * FROM TableDriver WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableFollowUp SELECT * FROM TableFollowUp WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableOfficerLogs SELECT * FROM TableOfficerLogs WHERE [UniqueColID] = @UniqueColID; INSERT INTO TablePolice SELECT * FROM TablePolice WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableRecVerified SELECT * FROM TableRecVerified WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableSignature SELECT * FROM TableSignature WHERE [IndividualID] = @IndividualID; INSERT INTO TableTrailer SELECT * FROM TableTrailer WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableValidateLog SELECT * FROM TableValidateLog WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableVehicle SELECT * FROM TableVehicle WHERE [UniqueColID] = @UniqueColID; INSERT INTO TableWitness SELECT * FROM TableWitness WHERE [UniqueColID] = @UniqueColID; End ``` Each table has NUMEROUS columns but they all share UniqueColID. When I insert the duplicate I want all the details to be duplicate but I want to insert a NEW UniqueColID..How can I do this? The new UniqueColID is generated by a special method in ASP.NET code. I want to have 2 or 3 or 4 DIFFERENT UniqueColID with the rest of the columns being duplicates. I tried the 2nd block of code that you said but got the error: ``` "Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement." CREATE PROCEDURE [dbo].[AmendInsertDuplicateFields] (@UniqueColID varchar(50), @NewUniqueColID varchar(50)) AS Begin CREATE TEMPORARY TABLE Temp AS SELECT * FROM [MVCNew].[dbo].[CollisionBegin] WHERE [UniqueColID] = @UniqueColID; UPDATE Temp SET UniqueColID = @NewUniqueColID; INSERT INTO [MVCNew].[dbo].[CollisionBegin] SELECT * FROM Temp; DROP TEMPORARY TABLE IF EXISTS Temp; End ``` I was just thinking....Is it possible for me to insert a new record with SOME Random UniqueColID value but all other columns are inserted with value "o" or "u" or "some random text" AND THEN I INSERT into this new record all of the columns of UniqueColID except UniqueColID? That way I would have a new UniqueColID with the rest of the columns having the same value. KCD I have tried both your blocks of code and none work.. :( I personally dont see how SELECT @NewUniqueColID....even works because there is NO column in the table named the value of @NewUniqueColID

Original source