How to copy rows from the same table and update the ID column?

  • I appreciate it is utterly trivial question, and yet... When you have a small amount of columns, there is a simple solution, sort of

    INSERT INTO myTable
    SELECT MAX(table_id) + 1, column2, column3
    FROM myTable
    WHERE table_id IN (SELECT list of table id's to be replicated);

    alas I have circa 200 columns, so I don't want to spend time writing them all down. Instead I INSERT INTO temp table and update the ID there. In Oracle you could define %ROWTYPE variable and simply update table_id column, but nothing of this sort exists in SQL 2014, as far as I know at least.

    I wonder if there is a good practice in place on how to copy records?

    At  the bottom line, what I need to achieve is to replicate existing accounts and some other account related tables, assigning them new account id.

    Much appreciated, as always.

  • Is the ID column an IDENTITY column?

  • IDENTITY would make it simpler indeed. But it is not. Primary Key though.

  • If you right-click the table in SSMS then script the table as INSERT, then do the same again and script the tables as SELECT. Paste this SELECT statement at the end of the INSERT replacing the VALUES clause, then just edit that statement a bit to get the current maximum ID and add a ROW_NUM to it.

    You should end up with something like this:

    INSERT INTO [dbo].[myTable]
    (ID
    ,Col1
    ,Col2
    ,Col3
    ,Col4)
    SELECT T.MaxId +ROW_NUMBER() OVER (ORDER BY ID) NewRowNum,
    Col1,
    Col2,
    Col3,
    Col4)
    FROM [dbo].[myTable]
    CROSS APPLY(SELECT MAX(Id) FROM [dbo].[myTable]) T(MaxId)
    WHERE Id IN (SELECT list of table id's to be replicated)

     

  • Agree that will work too, yet still would not be a clean solution with over 200 rows.

  • BOR15K wrote:

    Agree that will work too, yet still would not be a clean solution with over 200 rows.

    Do you mean rows or columns?

    If you mean columns then you don't actually have to type anything as SQL Server generates the column list for both the insert and the select.

    If you meant rows then you have to somehow identify which 200 rows you want to copy.

  • I have meant 200 rows of code

    Column1,

    Column2,

    ----

    Column200

     

    I appreciate SSMS will generate it for me, yet again, I was hoping to have neater solution, something like one would do in Oracle, which will also work in a future - if at some point a new column(s) will be added.

    For now I have addressed it with INSERT INTO #tmp table and updating id with the relevant one.

    Thank you for your time.

  • BOR15K wrote:

    I have meant 200 rows of code

    Column1,

    Column2,

    ----

    Column200

    I appreciate SSMS will generate it for me, yet again, I was hoping to have neater solution, something like one would do in Oracle, which will also work in a future - if at some point a new column(s) will be added.

    For now I have addressed it with INSERT INTO #tmp table and updating id with the relevant one.

    Thank you for your time.

    You can use dynamic SQL to build up your statement.  The following will build the list of columns

    DECLARE @ColumnList nvarchar(4000);

    SET @ColumnList = STUFF( (SELECT N',' + ac.name
    FROM sys.all_columns AS ac
    WHERE ac.object_id = OBJECT_ID(N'Schemaname.TableName', N'U')
    ORDER BY ac.column_id
    FOR XML PATH('')), 1, 1, N'');

  • BOR15K wrote:

    I have meant 200 rows of code

    Ok, it's normal convention to refer to rows of code as lines of code. What you actually meant was columns as each column is put on a separate line of code. It gets really confusing if you start calling columns rows when you are talking about databases.

  • DECLARE @ColumnList nvarchar(4000);

    SET @ColumnList = STUFF( (SELECT N',' + ac.name
    FROM sys.all_columns AS ac
    WHERE ac.object_id = OBJECT_ID(N'Schemaname.TableName', N'U')
    ORDER BY ac.column_id
    FOR XML PATH('')), 1, 1, N'');

    Would it be necessary to exclude the Fk columns ? if my table had Fks , also once I would do the INSERT can I INSERT based on the count of rows that occur in the table ( in its existing form ).

    For example if a row with TEXT1 = Do Task occurs 5 times , but another row with TEXT1= Do Task for Company ABC occurs 3 times and my objective is to duplicate these rows ( with TEXT1 = Do Task 5 times since it occured 5 times leading to a toal row with with TEXT1 = Do Task to 10 rows ) and so on ? Thanks

  • alig wrote:

    DECLARE @ColumnList nvarchar(4000);

    SET @ColumnList = STUFF( (SELECT N',' + ac.name
    FROM sys.all_columns AS ac
    WHERE ac.object_id = OBJECT_ID(N'Schemaname.TableName', N'U')
    ORDER BY ac.column_id
    FOR XML PATH('')), 1, 1, N'');

    Would it be necessary to exclude the Fk columns ? if my table had Fks , also once I would do the INSERT can I INSERT based on the count of rows that occur in the table ( in its existing form ). For example if a row with TEXT1 = Do Task occurs 5 times , but another row with TEXT1= Do Task for Company ABC occurs 3 times and my objective is to duplicate these rows ( with TEXT1 = Do Task 5 times since it occured 5 times leading to a toal row with with TEXT1 = Do Task to 10 rows ) and so on ? Thanks

     

    I have no idea what your process is doing.  I simply provided you with a method to dynamically create a list of column names for a given table.

  • BOR15K wrote:

    I appreciate it is utterly trivial question, and yet... When you have a small amount of columns, there is a simple solution, sort of

    INSERT INTO myTable
    SELECT MAX(table_id) + 1, column2, column3
    FROM myTable
    WHERE table_id IN (SELECT list of table id's to be replicated);

    alas I have circa 200 columns, so I don't want to spend time writing them all down. Instead I INSERT INTO temp table and update the ID there. In Oracle you could define %ROWTYPE variable and simply update table_id column, but nothing of this sort exists in SQL 2014, as far as I know at least.

    I wonder if there is a good practice in place on how to copy records?

    At  the bottom line, what I need to achieve is to replicate existing accounts and some other account related tables, assigning them new account id.

    Much appreciated, as always.

    You could go the way of dynamic SQL but would you really want to move data to a new table (with new accountID's) at the push of a run button , without eyeballing the intricate details ? Like making sure column types are the same.

    ----------------------------------------------------

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply