How can I copy a row into same table with SQL Server 2008

Question

A) My way so far:

sqlCommand.CommandText =
"INSERT Table1 ([column1],[column2],[column3])" +
            " SELECT [column1],[column2],[column3]" +
            " FROM Table1 WHERE Id =" + param +
            ";select scope_identity() as id";

B) I wish to do something like this:

INSERT INTO "table1" (* (without the ID-column))
SELECT (* (without the ID-column))
FROM "table1"

Note: I'm copying to the same table. I just want to easy copy it all to another row, while ofcourse giving the new row a new ID.

Is that good practice and possible or not?

Answer 1

I had the same issue myself and wanted a nice and simple way of doing this.

I found a solution here which allows this. I've modified it slightly to remove the output id and also to make the IdColumnName have a default value of 'Id'.

IF OBJECT_ID('TableRowCopy') IS NOT NULL DROP PROCEDURE TableRowCopy
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TableRowCopy](
    @TableName VARCHAR(50),
    @WhereIdValue INT,
    @IdColumnName VARCHAR(50) = 'Id'
)
AS
BEGIN
    DECLARE @columns VARCHAR(5000), @query VARCHAR(8000);
    SET @query = '' ;

    SELECT @columns =
        CASE
            WHEN @columns IS NULL THEN column_name
            ELSE @columns + ',' + column_name
        END
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (
        TABLE_NAME = LTRIM(RTRIM(@TableName))
        AND
        column_name != LTRIM(RTRIM(@IdColumnName))
    );

    SET @query = 'INSERT INTO ' + @TableName + ' (' + @columns + ') SELECT ' + @columns + ' FROM ' + @TableName + ' WHERE ' + @IdColumnName + ' = ' + CAST(@WhereIdValue AS VARCHAR);
    EXEC (@query);
    SELECT SCOPE_IDENTITY();
END

Example usage:

EXEC TableRowCopy 'MyTable', 3

Answer 2

The only way of doing this is to list all the columns out as in your first example. There is no syntax like SELECT *, -Id

You should use parameterised SQL though for SQL injection and plan caching reasons.