Copy Record Inside Table?

[jrapp]

Hey all. What I'm trying to do seems to be very simple, yet I can't get it to work correctly. I need to make a stored procedure that takes in an ID number (unique key), finds that record and appends a copy of that record to the bottom of the table, with a new ID number. SQL Server keeps throwing errors at me, no matter what I do. I've tried to copy the record to a temp table and drop the identitiy column, and then copy back to the original table, but that doesn't work either. Anyone have a few suggestions? Thanks!!

 

[checkai]

Post your current code...

 

[JohnDTampaBay]

Here's a very simple example of what you seem to want. The parameter @RecordID must be the same datatype as the unique key value of the table. ColumnList should be a list of all columns in the table that you want to insert LESS THE UNIQUE KEY COLUMN (assuming that the unique key column is an IDENTITY column). Good luck!

CREATE PROC uspDupeRecord
  @RecordID int
AS BEGIN
  INSERT INTO SomeTable (ColumnList)
  SELECT ColumnList
  FROM SomeTable
  WHERE RecordID = @RecordID
END

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[jrapp]

Here's what I'm doing. Angel, I haven't tried yours yet, but that's my next step. Thanks!

CREATE PROCEDURE proc_copyItem @ItemID int AS

SELECT * INTO #temp_items FROM Items WHERE ItemID=@ItemID

ALTER TABLE #temp_items DROP COLUMN ItemID

SELECT * INTO Items FROM #temp_items
GO

Jeff Rapp

 

[SQLBill]

If you are still having problems, it might help to let us see the error message(s).

-SQLBill

 

[jrapp]

I went ahead and used Angel's idea, however that somewhat changes what I was trying to do (keep coping even if new columns are added). The error I was experiencing was "There is already an object named 'Items' in the database." eh? Thanks for all the help!

Jeff Rapp

 

[RiverGuy]

When you use a Select Into statement, it tries to create a new table with your provided table name. If you already have a table with the same name, you cannot create a new one.