Generating INSERT and UPDATE statements from table data

In dbscript, a Project contains the various versions of a database schema as well as database information related to the project, and definitions of Value Scripts, which allow you to generate T-SQL code that represents data stored in your (development) database.

This post deals with generating SQL statements from data of selected tables.

First we need to define which tables are contained in the project (or in the current project version). Create a Database entry, which is either just a name (if you upload CREATE statements later on), or the connection string information to a database that is directly available to dbscript.

Create a Project Version entry, and select from the Actions menu Upload if you want to upload an SQL file containing CREATE TABLE statements, or Import if you have entered a valid connection string for one of the project’s databases.

Finally, create a Value Script entry, selecting “T-SQL Value Script” as Script Type. Behind the Tables link, we can now define the contents of the value script.

Select one of the tables from the dropdown, and choose a Script Mode:

Insert Only INSERT …
Insert with Check IF (NOT EXISTS …) INSERT
Update Only UPDATE …
Delete Before Insert DELETE … INSERT …

Press Insert to add the table to your script.

From the list of tables, select the newly created entry. Select a column name as the primary key column from the dropdown.

If a table contains foreign key references to another table, you may control the sequence of tables in the script by setting the Sequence field.

Go to the Columns page of your currently selected table. Press “Insert All” if you want your script to contain all columns of the table, or select individual columns.

Finally, click Generate in the Value Script’s Action menu. Select the project version the script is based on (a script may contain tables or columns that need not be present in every project version).

Press Generate. The Generated Script textbox will now contain T-SQL statements that generate Insert and Update statements according to your definition. If you defined a name for a Stored Procedure in the Value Script properties, the script will also contain the CREATE PROCEDURE header.

Alternatively, select a database from the dropdown to retrieve values directly from that database. This is a sample based on the ObjectType table used by dbscript:

-- table dbo.ObjectType

IF (SELECT COUNT(*) FROM dbo.ObjectType
    WHERE    OID = 1
    ) = 0
    INSERT INTO dbo.ObjectType (OID, ID, OTT_OID, ProgID, SysobjectType,
                                Seq2000, Seq2005, WikiTitleFormat)
    VALUES    (1, 'Table', 1, 'Table', 'U', 100, 100, N'_(table)')
    UPDATE    dbo.ObjectType
    SET ID = 'Table', ProgID = 'Table', OTT_OID = 1, SysobjectType = 'U',
        Seq2000 = 100, Seq2005 = 100, WikiTitleFormat = N'_(table)'
    WHERE    OID = 1

Copy the generated script directly from the textbox, or click the download link to save the generated script.

dbscript is available for download here.

4 thoughts on “Generating INSERT and UPDATE statements from table data

  1. Pingback: Generating C# const declarations from table data « devioblog

  2. Pingback: Synching SQL Server Databases « devioblog

  3. Pingback: Source Control Management and Databases « devioblog

  4. Pingback: Limitations of the T-SQL PRINT Command « devioblog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.