SQL clone record with a unique index

Question

Is there a clean way of cloning a record in SQL that has an index(auto increment). I want to clone all the fields except the index. I currently have to enumerate every field, and use that in an insert select, and I would rather not explicitly list all of the fields, as they may change over time.

Answer 1

Not unless you want to get into dynamic SQL. Since you wrote "clean", I'll assume not.

Edit: Since he asked for a dynamic SQL example, I'll take a stab at it. I'm not connected to any databases at the moment, so this is off the top of my head and will almost certainly need revision. But hopefully it captures the spirit of things:

-- Get list of columns in table
SELECT INTO #t
EXEC sp_columns @table_name = N'TargetTable'

-- Create a comma-delimited string excluding the identity column
DECLARE @cols varchar(MAX)
SELECT @cols = COALESCE(@cols+',' ,'') + COLUMN_NAME FROM #t WHERE COLUMN_NAME <> 'id'

-- Construct dynamic SQL statement
DECLARE @sql varchar(MAX)
SET @sql = 'INSERT INTO TargetTable (' + @cols + ') ' +
    'SELECT ' + @cols + ' FROM TargetTable WHERE SomeCondition'

PRINT @sql -- for debugging
EXEC(@sql)

Answer 2

You could create an insert trigger to do this, however, you would lose the ability to do an insert with an explicit ID. It would, instead, always use the value from the sequence.

Answer 3

There's no easy and clean way that I can think of off the top of my head, but from a few items in your question I'd be concerned about your underlying architecture. Maybe you have an absolutely legitimate reason for wanting to do this, but usually you want to try to avoid duplicates in a database, not make them easier to cause. Also, explicitly naming columns is usually a good idea. If you're linking to outside code, it makes sure that you don't break that link when you add a new column. If you're not (and it sounds like you probably aren't in this scenario) I still prefer to have the columns listed out because it forces me to review the effects of the change/new column - even if it's just to look at the code and decide that adding the new column is not a problem.

Answer 4

You could create a trigger to do it for you. To make sure that trigger only works for cloning, you could create a separate username CLONE and log in with it. Or, even better, if your DBMS supports it, create a role named CLONE and any user can log in using that role and do the cloning. The trigger code would be something like:

if (CURRENT_ROLE = 'CLONE') then
   new.ID = assign new id from generator/sequence

Of course, you would grant that role only to the users who are allowed to clone records.

Answer 5

DROP TABLE #tmp_MyTable

SELECT * INTO #tmp_MyTable
FROM MyTable
WHERE MyIndentID = 165

ALTER TABLE #tmp_MyTable
DROP Column MyIndentID

INSERT INTO MyTable
SELECT * 
FROM #tmp_MyTable