1. 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
    0

    Add a comment

Blog Archive
Topics
Topics
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.