How can I copy a row into same table with SQL Server 2008
A) My way so far:
sqlCommand.CommandText =
"INSERT Table1 ([column1],[column2],[column3])" +
" SELECT [column1],[column2],[column3]" +
" FROM Table1 WHERE Id =" + param +
开发者_JAVA百科 ";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?
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
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.
精彩评论