Click here to Skip to main content
15,860,861 members
Articles / Programming Languages / Visual Basic
Article

Generate SQL Insert statements for your SQL Server 2000 Database

Rate me:
Please Sign up or sign in to vote.
4.77/5 (98 votes)
13 Apr 20045 min read 510.9K   8.3K   138   110
Deploy your SQL Server 2000 database with scripts.

Enter Settings on main form

Introduction

Have you ever had to deploy a SQL Server database to a customer? If you have, then you will know that it is certainly not as simple as deploying an application that uses an MS Access database.

With SQL Server databases, you have 3 deployment options that I know of:

  1. Distribute your .mdf and .ldf files to your customer and attach the database to their SQL Server instance using the system stored procedure sp_attach_db (or sp_attach_single_file_db). The downside is that the SQL Server must reside on the same machine as the setup application, which is not always the case.
  2. Use Enterprise Manager to generate your database scripts and BCP to export all of your data to files and import them back in during your setup process.
  3. Script out your entire database including SQL Insert statements for importing data into the database.

With options 2 and 3, you can deploy a SQL Server database locally or remotely. This article focuses on option number 3.

Background

There are a number of commercial programs that will script out an entire SQL Server database for you, but due to current financial reasons, I couldn't afford to buy one and so I had to write my own program which only took a few days.

The source code for this article uses the SQLDMO library extensively to interrogate a SQL Server database. SQLDMO is a COM library and is installed with SQL Server 7/2000. (Note: the code for this article will only work for SQL Server 2000 databases).

Using the code

(See figure 1 above)

  1. When you run the project, enter the connection information for the database you would like to generate scripts for.
  2. Enter an existing directory for the output. A subdirectory for the database will be placed in the output directory. Underneath the database folder, there is another folder which is used to store all of the BLOB values as .txt files for Text columns, or .dat files for Image columns.
  3. If you do not want to generate SQL Insert scripts for the data in the selected database, then clear this option.
  4. Press the [Generate] button to start the process. A progress screen will be displayed. Press the [Cancel] button at any time to cancel the process.
  5. After the generation process has finished, browse to the database folder in the output directory to see a list of files:
    osql.exeSQL utility to send batch statements to a SQL Server instance
    textcopy.exeUnsupported SQL utility used to copy 'text' and 'image' values into a SQL Server database
    createdatabase.sqlScript to create the database
    buildobjects.sqlScript to build all of the objects in the database
    populatedata.sqlScript containing SQL Insert statements to populate the database with data
    importblobvalues.bat

    Batch file to import the blob values into the database.

    Parameters required are server name, database, username, password, authentication method.

    setup.bat

    Batch file to invoke the createobject.sql, buildobjects.sql and populatedata.sql scripts. This batch file is generic to any database. Ability to invoke osql using either Windows Authentication or SQL Server Authentication.

    Parameters required are server name, database, username, password, authentication method.

    install.bat

    Batch file to test the installation.

    Normally, your Setup Project would invoke the setup.bat and importblobvalues.bat files directly passing the appropriate values gathered from a custom user interface.

    Before you test the installation:

    Please follow steps 6 and 7 carefully to avoid dropping the original database!!

  6. Open createdatabase.sql in Notepad and change all occurrences (3) of the original database name to something different.
  7. Open install.bat and change the database name to that chosen in step 6 above.
  8. To install the database, double-click on install.bat.

Points of Interest

Scripting out Dependent objects first

Even if you use SQL Server Enterprise Manager to script out all of the objects in your database, you will still be left to sort out the order in which objects should be created, as some tables/views/functions and stored procedures may depend on others. This process can be time consuming and tedious.

However, in the article code, the EnumDependencies method was called for each of the objects that may potentially have dependencies and the dependent objects are scripted out first. This should eliminate receiving error messages when the scripts run. Circular references in tables when both tables have foreign keys to the other table would still generate errors though.

Generating a batch file to import BLOB values into a database

When I first started out, I included all BLOB values in the actual INSERT statements. However, when I ran the scripts they produced errors, so I assumed there was a length limit for an INSERT statement.

After a bit of browsing on groups.google.com, I learnt that SQL Server already had sample code to import BLOB values, installed in the C:\Program Files\Microsoft SQL Server\MSSQL\Install directory.

The files of interest are: pubimage.bat, pubtext.bat and instpubs.sql.

The example uses TEXTCOPY.EXE to insert 'text' and 'image' values into the pubs database.

pubtext.bat reprinted below:

@echo off
if "%1" == "" goto usage
echo.
echo Inserting images into pubs database on server %1
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1389'" -Falgodata.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '0877'" -Fbinnet.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9901'" -Fgggg.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1622'" -F5lakes.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '0736'" -Fnewmoon.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9999'" -Flucerne.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1756'" -Framona.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9952'" -Fscootney.gif
echo Image update complete!
echo.
goto done
:usage
echo.
echo Usage: pubimage ServerName [SAPassword]
echo.
:done

It was only after I was successfully able to script out the pubs database that I learnt that TEXTCOPY.EXE cannot import 'ntext' values. I was very disheartened when I realized this and I still know of no good way to import 'ntext' data into a database. BCP needs format files and file length sizes and can only insert data, not update data.

If anyone knows a good way to import 'ntext' data into a SQL Server database, then please let me know so that I can update this article and provide a complete solution for scripting out an entire SQL Server database.

Conclusion

I've been a fan of CodeProject for some time now and this is my very first article. I hope it will help many of you out when it comes to deploying your SQL Server databases. If you have any suggestions for improvements, please let me know.

History

Version 1. Submitted on 14th April 2004.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior) Simbient
Australia Australia
Darren Weir is a senior .NET developer working for Simbient in North Sydney, Australia.

My blog is located at dotnetdarren.wordpress.com

Comments and Discussions

 
Questionthank Pin
muaad sehen4-Apr-18 19:58
muaad sehen4-Apr-18 19:58 
QuestionThank you Pin
kiilholm10-Apr-15 4:18
kiilholm10-Apr-15 4:18 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey17-Apr-12 2:28
professionalManoj Kumar Choubey17-Apr-12 2:28 
GeneralMy vote of 5 Pin
spidermauro13-Sep-10 6:06
spidermauro13-Sep-10 6:06 
GeneralMy vote of 3 Pin
ssa20107-Jul-10 23:01
ssa20107-Jul-10 23:01 
Generalhi writer i need your email address plz Pin
e-life17-Aug-09 19:28
e-life17-Aug-09 19:28 
GeneralThank you so much! Pin
Sameeullahqazi5-Jul-09 3:52
Sameeullahqazi5-Jul-09 3:52 
GeneralThank you! Pin
steveprowland28-May-09 20:01
steveprowland28-May-09 20:01 
Generalgood job Pin
lucciren3-Mar-09 7:49
lucciren3-Mar-09 7:49 
tks u so much! hope u will have more useful tools. cheer Big Grin | :-D Big Grin | :-D Wink | ;)
Answerthank you Pin
Muzztein9-Dec-08 2:35
Muzztein9-Dec-08 2:35 
GeneralCreate Backup and restore in Production Pin
Blumen9-Sep-08 4:13
Blumen9-Sep-08 4:13 
RantRe: Create Backup and restore in Production Pin
Muzztein9-Dec-08 2:18
Muzztein9-Dec-08 2:18 
GeneralRe: Create Backup and restore in Production Pin
Blumen10-Dec-08 6:00
Blumen10-Dec-08 6:00 
QuestionNo Generated Insert statements Pin
Robin Warren27-Jun-08 8:33
Robin Warren27-Jun-08 8:33 
AnswerRe: No Generated Insert statements Pin
Robin Warren27-Jun-08 8:51
Robin Warren27-Jun-08 8:51 
GeneralGreat Tool Pin
ManikandanP3-Jun-08 21:59
ManikandanP3-Jun-08 21:59 
GeneralGreat Application Tool Pin
nagalinga reddy8-Apr-08 13:56
nagalinga reddy8-Apr-08 13:56 
Generalnew user Pin
hnoorani20-Mar-08 5:26
hnoorani20-Mar-08 5:26 
GeneralThank you Pin
reza shirazi19-Jan-08 5:41
reza shirazi19-Jan-08 5:41 
GeneralGenerate SQL Scripts Pin
Darrell Dixon31-Aug-07 9:27
Darrell Dixon31-Aug-07 9:27 
GeneralIT DOESN"T CREATE TRIGGERS! Pin
aammrr23-Aug-07 4:04
aammrr23-Aug-07 4:04 
GeneralRe: IT DOESN"T CREATE TRIGGERS! Pin
mrwoodo13-Sep-07 21:26
mrwoodo13-Sep-07 21:26 
GeneralNewbie need help please Pin
knoop14-Aug-07 7:59
knoop14-Aug-07 7:59 
GeneralDont know how to thank you Pin
marcelo234-Jul-07 20:15
marcelo234-Jul-07 20:15 
GeneralThank you thank you thank you Pin
danlewisnet30-May-07 3:48
danlewisnet30-May-07 3:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.