DECLARE @tblName VarChar(50) @tblName VarChar(50)
DECLARE @columns VarChar(5000) @columns VarChar(5000)
DECLARE @query VarChar(8000) @query VarChar(8000)
DECLARE @id int @id int
--Initialize the table and id.
SELECT @tblName = 'tableName' @tblName = 'tableName'
SELECT @id = 1234
@id = 1234
--GET the column names
SELECT @columns = CASE
WHEN @columns IS NULL THEN
column_name
ELSE
@columns + ', ' + column_name
END @columns = CASE
WHEN @columns IS NULL THEN
column_name
ELSE
@columns + ', ' + column_name
END WHEN @columns IS NULL THEN
column_name
ELSE
@columns + ', ' + column_name
END ELSE
@columns + ', ' + column_name
END + ', ' + column_name
END END
FROM information_schema.columns information_schema.columns
WHERE table_name = @tblName table_name = @tblName
--Show all columns
SELECT @columns
@columns
--Create query
SET @query = '' @query = ''
SELECT @query = ' INSERT ' + @tblName + '(' + @columns + ')' +
' SELECT ' + @columns +
' FROM ' + @tblName + ' WHERE ID = ' + convert(varchar(10), @id) @query = ' INSERT ' + @tblName + '(' + @columns + ')' +
' SELECT ' + @columns +
' FROM ' + @tblName + ' WHERE ID = ' + convert(varchar(10), @id)' SELECT ' + @columns +
' FROM ' + @tblName + ' WHERE ID = ' + convert(varchar(10), @id)' FROM ' + @tblName + ' WHERE ID = ' + convert(varchar(10), @id)
--Show query
SELECT @query
@query
--Execute the query
Exec (@query)(@query)
Source: http://aspadvice.com/blogs/ssmith/archive/2007/01/18/COPY-One-Table-Row-in-SQL.aspx/archive/2007/01/18/COPY-One-Table-Row-in-SQL.aspx
Add a comment