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 Responses to Generating INSERT and UPDATE statements from table data

  1. […] C# const declarations from table data In my previous post I showed how to generate INSERT and UPDATE statements from table data stored in an SQL Server database using dbscript. Now we create a Value Script definition to generate C# classes with const int […]

  2. […] all your system tables (those tables which hold pre-defined “magic” values), and let dbscript generate a stored procedure which will script the data as Insert/Update statements, or have the live data scripted directly by […]

  3. […] generates T-SQL value scripts and C# constant declarations based on database […]

  4. […] One of the main functions of dbscript is to allow the user to select a set of tables and columns. This selection is used to generate T-SQL code (either a set of statements or the definition of a stored procedure) which can then be executed on a live database to generate INSERT/UPDATE statements reproducing the values of the selected tables and columns. An earlier post illustrated this functionality. […]

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: