Most database management tools can run a query and output it as CSV or text. But sometimes you want SQL "INSERT" statements in a script which you can run to initialize a database.

Load the database schema

const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();

Load the data into a DataTable

You can do this manually, or use the DatabaseSchemaReader's Data.Reader (which reads just the first 200 rows)

//just get the "Categories" table
var table = schema.FindTableByName("Categories");
//use the reader helper
var rdr = new DatabaseSchemaReader.Data.Reader(table, connectionString, providername);
DataTable dt = rdr.Read();

Run the InsertWriter

var insertWriter = new InsertWriter(table, dt);
//if there are identity columns, write them too? Default is false
insertWriter.IncludeIdentity = true;
//specify the dialect
string txt = insertWriter.Write(SqlType.SqlServer);

The dialect can be one of SqlServer, SqlServerCe, Oracle, MySql, PostgreSql, SqLite or Db2.

The Output

We ignore binary types and large strings (clobs) because you rarely want those in a SQL script. Other database-specific types, such as xml, will also be ignored.

Single quotes in varchars are doubled up.

Dates and times are written in the best way- for instance as a neutral string ('1996-07-04 00:00:00.000' or '1996-07-04T00:00:00.000') or function (TO_TIMESTAMP('1996-07-04 00:00:00.00000', 'yyyy-mm-dd hh24:mi:ss.FF').

This is SqlServer with IncludeIdentity=true

SET IDENTITY_INSERT [Categories] ON
GO
INSERT INTO [Categories] (
  [CategoryID],  [CategoryName],  [Description],  [Picture]) VALUES (
1 ,N'Beverages' ,N'Soft drinks, coffees, teas, beers, and bitters' ,NULL
);

INSERT INTO [Categories] (
  [CategoryID],  [CategoryName],  [Description],  [Picture]) VALUES (
2 ,N'Condiments' ,N'Sweet and savory' ,NULL
);

etc.

This is SqLite

INSERT INTO "Categories" (
  "CategoryID",  "CategoryName",  "Description",  "Picture") VALUES (
1 ,'Beverages' ,'Soft drinks, coffees, teas, beers, and bitters' ,NULL
);

INSERT INTO "Categories" (
  "CategoryID",  "CategoryName",  "Description",  "Picture") VALUES (
2 ,'Condiments' ,'Sweet and savory' ,NULL
);

This is MySql

INSERT INTO `Categories` (
  `CategoryID`,  `CategoryName`,  `Description`,  `Picture`) VALUES (
1 ,'Beverages' ,'Soft drinks, coffees, teas, beers, and bitters' ,NULL
);

INSERT INTO `Categories` (
  `CategoryID`,  `CategoryName`,  `Description`,  `Picture`) VALUES (
2 ,'Condiments' ,'Sweet and savory' ,NULL
);

Last edited Jul 5, 2011 at 7:52 PM by Martinjw, version 1

Comments

No comments yet.