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.
Pingback: Generating C# const declarations from table data « devioblog
Pingback: Synching SQL Server Databases « devioblog
Pingback: Source Control Management and Databases « devioblog
Pingback: Limitations of the T-SQL PRINT Command « devioblog