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

  • BOR15K

    SSCertifiable

    Points: 5767

    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.

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    Is the ID column an IDENTITY column?

  • BOR15K

    SSCertifiable

    Points: 5767

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

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    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)

     

  • BOR15K

    SSCertifiable

    Points: 5767

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

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    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.

  • BOR15K

    SSCertifiable

    Points: 5767

    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.

  • DesNorton

    SSC-Insane

    Points: 23061

    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'');

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    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.

Viewing 9 posts - 1 through 9 (of 9 total)

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