Stored procedures to script table data in SQL Server 2005

Tags:
SQL Server 2005
SQL Server stored procedures
SQL Server tables
Are there any stored procedures or command line utilities that I can use to script out the data from a particular table in SQL Server 2005?

Answer Wiki

Thanks. We'll let you know when a new response is added.

(kccrosser) I think the original answer was correct, assuming that the question term “to script out the data” meant “generate a complete set of Insert statements to reproduce the contents of a table”. There isn’t any native function that does this. The “sqlcmd” example below simply dumps the data, but a “script” should contain a fully qualified “Insert into <table> ( <column_list> ) values ( <value_list> );” statement for each row of data.

I disagree with the below answer, there is a native utility that can be used to script data in a Microsoft SQL Server (2005 and above) called “SQLCMD”. In previous versions of SQL, this command was ‘osql’. This can be used in interactive mode from a command prompt as well as used in scripting. See the below example, also, from a command prompt on your SQL server type ‘sqlcmd -?’ for a full list of the command parameters.

@echo off
sqlcmd -H “HOSTNAME of Server” -S ServerName\InstanceName -d Employees -U Userid -P Password -Q “SELECT * From Personal_Info” -o F:\Data\SQLTest.txt

Breaking this simple batch file down, it initiates sqlcmd, passes the hostname of the database server, defines the database server\sql instance (if an instance is defined, otherwise just the database server name), passes a simple SELECT * from Employees.Personal_Info table and redirects the output to a text file. If you need the output in unicode format, pass the -u flag in addition to the others. You can also use a previously saved sql query by using the -i flag instead of -Q.

There are some third party utilities which can do this for you. I believe that Red-Gate and Quest Software both have one.

There are no native tools that can be used to script out the data. You can export the data using the BCP command line utility or SSIS.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    SQLCMD shouldn't be used to export data as there is no guarantee that the data will be dumped in a format which is reimportable. This is because it is not a data export tool. It is a query tool. The data export tools would be the ones which I listed BCP and SSIS.
    66,130 pointsBadges:
    report
  • msi77
    I wrote sp which generate inserts for own usage :-) It does not work with all data types, blobs for example.
    1,670 pointsBadges:
    report
  • Henny
    Hi Msi77: please could you send me that SP to use it myself? Thanks.
    10 pointsBadges:
    report
  • SQL Server Ask the Experts
    [...] Stored procedures to script table data in SQL Server 2005 [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following