Generate Update statements for an existing table

  • Requirement:

    A SQL Script that can be used to generate the 'Update' Statements for a Table which has data

    I found a superb piece of code @ http://www.keylimetie.com/blog/2009/3/24/sql-server-2005-script-to-generate-insert-statements for a 'Insert Statement Generator'. Well, call me lazy or a person who would not want to re-invent the wheel, Can anyone post their code if any that can generate Update statements for any given table.

    I do not want to specify the Primary Key Name or the Primary Key Value. The script should be able to pull the primary key and get the value from the existing rows. 😉

    I have started working on this already, but thought it would be a good idea to post this in this intellectual forum! Thanks for your time to read through this. 🙂

    To be more specific:

    If I have a table with this structure

    CREATE TABLE [Address](

    [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [AddressLine1] [nvarchar](60) NOT NULL,

    [AddressLine2] [nvarchar](60) NULL,

    [City] [nvarchar](30) NOT NULL,

    CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED

    (

    [AddressID] ASC

    ))

    And it has the following data

    INSERT INTO Address( [AddressID], [AddressLine1], [AddressLine2], [City])

    SELECT 32506, '5407 Cougar Way', ' ', 'Seattle'

    Go

    INSERT INTO Address( [AddressID], [AddressLine1], [AddressLine2], [City])

    SELECT 32507, '2284 Azalea Avenue', ' ', 'Bellevue'

    Go

    INSERT INTO Address( [AddressID], [AddressLine1], [AddressLine2], [City])

    SELECT 32508, '108 Lakeside Court', ' ', 'Bellevue'

    Go

    INSERT INTO Address( [AddressID], [AddressLine1], [AddressLine2], [City])

    SELECT 32509, '535 Greendell Pl', ' ', 'Sammamish'

    This script should return the following output:

    Update Address

    SET

    AddressLine1 = '5407 Cougar Way',

    AddressLine2 = ' ',

    City = 'Seattle',

    WHERE = 32506

    Update Address

    SET

    AddressLine1 = '2284 Azalea Avenue',

    AddressLine2 = ' ',

    City = 'Bellevue',

    WHERE = 32507

    Update Address

    SET

    AddressLine1 = '108 Lakeside Court',

    AddressLine2 = ' ',

    City = 'Bellevue',

    WHERE = 32508

    Update Address

    SET

    AddressLine1 = '535 Greendell Pl',

    AddressLine2 = ' ',

    City = 'Sammamish',

    WHERE = 32509

    In case you have more time to read and guessing why I need this script?

    My current project is a SQL Server Migration project.

    A new version of the Application (V2) has been released and we are migrating data from the older version (V1) to (V2). The table schema has changed considerably between these 2 versions and we have a table where we maintain the Column Mapping details and couple of scripts that can dynamically generate the V2 Table Schema, Views and the Migration script also.

    There is a special requirement :w00t: where we are doing a 2-way Synchronization between 2 databases which have totally different structure - V1-->V2 and V2-->V1. We are right now implementing it by capturing all changes by triggers and logging it in to a Staging table and then a job runs which pushes the data either ways.

    After all these hard work, we are at the point of testing this Synchronization and we need a automated way to Generate Test Data.

    Since this is a 2 way Synch, we have a couple of test scenarios like the following

    Insert rows into V1 tables

    Insert a row into a table. Update this row by changing the values of one or more columns

    Insert a row into a table. Follow this up with a delete of the same row

    Update an existing row in the V1 table

    Update an existing row followed by a second update

    Update an existing row followed by a delete

    Delete an existing row from V1

    Delete an existing row from V1 followed by an insert with the same PK value

    The above 8 should result in these below 8

    The rows should get inserted in the corresponding V2 tables

    The row in V2 should reflect the changes done in the update statement

    The row should not appear in V2

    The changes should get reflected in V2

    The changes of the second update should get reflected in V2

    The row should be deleted from V2

    The row should get deleted from V2

    The new row should get inserted into V2 and the old row should not be present

    Thanks

    Kayal

  • Here you go but you need to customize it for yourself.

    This script will generate script to insert/update from a source table in one database to an

    identical destination table in another database or server. It can be run for inserts or updates,

    and can be run for a single row in insert and update mode, or all rows in a table for insert mode.

    */

    declare @tab varchar(50)

    ,@pk1Val varChar(10)

    ,@pk1Name varChar(50)

    ,@qt char(1)

    ,@StatementType varChar(10)

    set nocount on

    /*

    Instructions:

    1) open script and connect to the source database

    2) Change the variable values to change the output options for the script below (@tab, @statementtype etc)

    3) execute the script (best to use text output)

    4) copy the script output into a script window, and run on the destination table.

    @tab = the name of the source table

    @pk1Val = if selecting a single row or doing an update statement, the value of the primary key for that row

    @pk1Name = if inserting a single row or doing an update statement, the name of the column for the primary key

    @StatementType = either 'INSERT' to create an insert statement or 'UPDATE' for an Update statement

    */

    select @tab = 'mytable', @pk1Val = '', @pk1Name = '', @StatementType = 'INSERT'

    declare @tabName varchar(50)

    , @colName varchar(50)

    , @colType varchar(50)

    , @collength varChar(50)

    , @colOrder int

    , @IsIdent char(1)

    create table #output (Line varChar(4000), LineOrder int)

    create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))

    declare @out varchar(4000)

    ,@lineCounter int

    ,@ColValue varchar(4000)

    ,@sortCol varchar(50)

    /* get the ordering column */

    select @sortCol = sc.Name

    from sysobjects so

    inner join syscolumns sc

    on so.id= sc.id

    inner join systypes st

    on sc.xtype = st.xusertype

    where so.Name = @tab

    and ((sc.status = 0x80) OR (ColOrder = 1 and not sc.status = 0x80 ))

    /* put in the repeating values based on the columns*/

    declare objCurs CURSOR FOR

    select so.name, sc.name, st.name, sc.length, Case when sc.status = 0x80 then 'Y' else 'N' END as IsIdent, ColOrder

    from sysobjects so

    inner join syscolumns sc

    on so.id= sc.id

    inner join systypes st

    on sc.xtype = st.xusertype

    where so.Name = @tab

    DECLARE @counter int, @numCols int, @RowNumber int, @LastRowNumber int, @maxRowNumber int, @maxColOrder int

    select @numCols = count(sc.id)

    from sysobjects so

    inner join syscolumns sc

    on so.id= sc.id

    where so.Name = @tab

    --select @numCols --debug code

    open objCurs

    Fetch from objCurs

    into @tabname, @colName, @colType, @colLength, @isIdent, @colOrder

    while @@fetch_status = 0

    begin

    SET @counter = 0

    /* get the value from the table */

    if @IsIdent = 'N'

    BEGIN

    --select @TabName,@ColName, @ColType, @ColLEngth, @isIdent, @ColOrder --debug code

    /* increase better type handling by inserting more case statments, handling different data types */

    if datalength(@pk1Name) = 0 or datalength(@pk1Val) = 0

    begin

    /* getting all rows in the table */

    exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)

    select ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName + ' order by ' + @SortCol + ' ' +

    ' declare @counter int set @counter = 0 ' +

    ' update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10) where ColName = ''' + @colName + '''' )

    end

    else

    begin

    /* filtering by a pk val */

    exec ('insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)

    select 0, ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName +

    ' where ' + @pk1Name + ' = ' + @pk1Val)

    end

    end /* if @isIdent = 'n' */

    Fetch Next from objCurs

    into @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder

    end

    --select * from #ColumnValues --debug code

    select @MaxRowNumber = Max(rowNumber) from #columnValues --keep highest row number so we know when we are finished

    select @MaxColOrder = max(ColOrder) from #ColumnValues where RowNumber = @MaxRowNumber

    /* next cursor for outputting the results from the retval table into the output table */

    declare ColVal_Curs cursor for

    select ColName , ColOrder , RowNumber , ColValue , colType

    from #ColumnValues

    order by RowNumber, ColOrder

    open ColVal_Curs

    --set the last row number to the first in the table, so post loop checking works

    select @lastRowNumber = min(rowNumber) from #ColumnValues

    set @lineCounter = @LastRowNumber --initialise at the first row

    fetch from ColVal_Curs into

    @colName, @ColOrder, @RowNumber, @colValue, @ColType

    while @@Fetch_status = 0

    BEGIN /* cursor loop */

    /* get the quote type to enclose the value from the column type */

    select @QT = case @colType

    when 'nvarchar' then ''''

    when 'nchar' then ''''

    when 'DateTime' then ''''

    when 'ntext' then ''''

    else ''

    end

    --select @linecounter, @colName, @ColOrder, @RowNumber, @colValue, @ColType

    if not @ColValue is null

    if @rowNumber = @lineCounter

    select @out = case @statementType

    when 'UPDATE' THEN 'Update ' + @tab + ' SET '

    when 'INSERT' then 'INSERT INTO ' + @tab + ' ('

    end

    begin

    if @StatementType = 'UPDATE'

    BEGIN

    select @Out = @out + @colName + ' = ' + @QT + COALESCE(@ColValue, 'NULL') + @QT + ',' -- + @ColType

    insert into #output (Line, LineOrder)

    values (@out, @lineCounter)

    end

    if @statementType = 'INSERT'

    BEGIN

    /* put comma in */

    if @lineCounter > @RowNumber --not first line in set of values for row

    select @out = @out + ','

    /*put in the name of the column */

    insert into #output (Line, LineOrder)

    values (@out + @colName

    , @lineCounter)

    if @lineCounter > @RowNumber --not first line in set of values for row

    select @out = ','

    else

    select @out = ''

    /* put in the value of the column */

    insert into #output (Line, LineOrder)

    values (@out + @QT + COALESCE(@ColValue, 'NULL') + @QT

    , @lineCounter + 10 + @numCols)

    END

    end /*not @ColValue is null */

    select @lineCounter = @lineCounter + 1

    set @out = ''

    /* get the next record*/

    fetch from ColVal_Curs into

    @colName, @ColOrder, @RowNumber, @colValue, @ColType

    --select @ColOrder, @MaxColOrder, @@Fetch_Status --debug code

    if (@rowNumber > @lastRowNumber) or (@RowNumber = @MaxRowNumber and @MaxColOrder = @ColOrder and @@FEtch_Status = -1)

    BEGIN

    /* this bit of processing is done whenever the set of columsn in a row changes to the next row of the original table*/

    /* ie we are building a record to insert, and the PK changes because we are at the next record */

    /* remove the last comma from the last line */

    declare @lastLine int

    if @statementType = 'UPDATE'

    begin

    /*remove last comma*/

    update #output

    set Line = left(Line,datalength(Line)-1)

    where lineOrder = @LineCounter

    /* insert a 'where' clause */

    insert into #output (line, LineOrder)

    select ' WHERE ' + @pk1Name + ' = ' + @pk1Val, Max(LineOrder) + 1 from #output

    end

    if @statementType = 'INSERT'

    BEGIN

    /* put in a 'values' statement between the column names and the column values */

    insert into #output (Line, LineOrder)

    values (') VALUES (', @LastRowNumber + @numCols + 5)

    /* close off the lot */

    insert into #output (line, lineorder)

    select ')', Max(LineOrder) + 1 from #output

    END

    set @lastRowNumber = @RowNumber

    set @lineCounter = @RowNumber /* reset linecounter for next set */

    End /* if rownumber > last row number */

    end /* cursor loop */

    close objCurs

    deallocate objCurs

    close ColVal_Curs

    deallocate ColVal_Curs

    /* get the statements out from the list*/

    select line as [Copy script from output window below] from #output order by lineorder

    /* bug tracking code - uncomment to diagnose problems

    select distinct RowNumber from #ColumnValues order by 1

    select * from #ColumnValues

    order by RowNumber, ColOrder, ColName, ColValue

    */

    drop table #output

    drop table #ColumnValues

    set nocount off

    Alex S
  • Alex,

    Thanks for your response! This is a great help.

    Thanks

    Kayal

  • How we can ignore the timestamp field to generate statements in this script ?

    Please give some idea, how we can specify the specific columns for the generation of update statement.

    Thanks in Advance.

  • Below is an Example:
    ALM_ID -- This is primary key of my table.
    Submitter  -- Column 1 to be updated.
    BPEG -- Column 2 to be updated

    ------- Script Starts Here-----------------
    declare @pkName varChar(100)
      ,@UpdateCol1 varChar(100)
      ,@UpdateCol2 char(100)
     
    set nocount on

    declare objCurs CURSOR FOR
    select ALM_ID, Submitter, BPEG from sampledata ;
    open objCurs
    Fetch from objCurs
    into @pkName, @UpdateCol1, @UpdateCol2

    WHILE @@FETCH_STATUS = 0 
    BEGIN
    Print 'UPDATE SampleData5 SET Submitter = ''' + @UpdateCol1 + '''' + ', BPEG = ''' + RTRIM(@UpdateCol2) + '''' + ' WHERE ALM_ID = ''' + @pkName + ''''

    Fetch Next from objCurs
    into @pkName, @UpdateCol1, @UpdateCol2
    END

    CLOSE objCurs; 
    DEALLOCATE objCurs;

    ---------------Import data from excel to sql server-----------------------

    https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

Viewing 5 posts - 1 through 4 (of 4 total)

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