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+Update	IF (NOT EXISTS …) INSERT … ELSE UPDATE …
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)')
ELSE
    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.