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

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.