Copying Entire Row in SQL Server
It would be nice for SQL Server to copy rows like Excel. It is a pain to include all the fields. Below is dynamic SQL to copy the entire row based on the auto id number:
[code]
declare @sql varchar(8000)
DECLARE @TableName varchar(100)
SELECT @tableName = 'tblMyTable' --put urTableName Here
SELECT @SQL = COALESCE(@sql+',','')+ COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName AND COLUMNPROPERTY(OBJECT_ID(@tableName),COLUMN_NAME, 'IsIdentity') = 0
EXEC( 'INSERT INTO '+@tableName+' SELECT '+@SQL+' FROM '+@TableName +' WHERE auto_id = theIDNum') -- put auto id fieldname and id num
[/code]