53

I have a database with a table Customers that have some data

I have another database in the office that everything is the same, but my table Customers is empty

How can I create a sql file in SQL Server 2005 (T-SQL) that takes everything on the table Customers from the first database, creates a, let's say, buildcustomers.sql, I zip that file, copy it across the network, execute it in my SQL Server and voila! my table Customers is full

How can I do the same for a whole database?

Improve this question
4

5 Answers 5

Reset to default
76

This functionality is already built in to Sql Server Management Studio 2008.

Just download the trial and only install the client tools (which shouldn't expire). Use Management Studio 2008 to connect to your 2005 database (its backwards compatible).

  1. Right click your database
  2. Choose Tasks > Generate Scripts
  3. Press Next, select your database again
  4. On the 'Choose Script Options' screen, there is an option called Script Data which will generate SQL insert statements for all your data.

(Note: for SQL Server Management Studio 2008 R2, the option is called "Types of data to script" and is the last one in the General section. The choices are "data only", "schema and data", and "schema only")

alt text alt text

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for pointing out the R2 change! Gotcha for others: schema is the default, and it gets reset to that if you change the target objects.
This is a great answer. One thing to note they screwed with it and now instead of "Script Data" set to "True" you have to set "Types of data to script" to "Schema and data" Just for the record, Microsoft is horrible at this crap. They provide easily the worst database tools and interface I've ever got near, and I truly pity anyone who has to work with this nonsense. Seriously, instead of "Export" we had to "Generate" ?! WTF
Its worth noting that in SQL MGMT 2008 the advanced button on the options screen - is where the option for the data is
can you run the scrip with a where clause?
I would like to point out that in SQL Server Management Studio 2008 R2 the option is different. Instead of setting Script Data to true you will find that option missing. The option is Types of data to script and you will need to set it to "Schema and Data", as this screenshot shows.
26

Use bcp (from the command line) to a networked file and then restore it.

e.g. 

bcp "SELECT * FROM CustomerTable" queryout "c:\temp\CustomerTable.bcp" 
     -N -S SOURCESERVERNAME -T 

bcp TargetDatabaseTable in "c:\temp\CustomerTable.bcp" -N -S TARGETSERVERNAME -T
  • -N use native types
  • -T use the trusted connection
  • -S ServerName

Very quick and easy to embed within code. (I've built a database backup(restore) system around this very command.

Improve this answer

2 Comments

See also this answer for a script which will generate a batchfile calling bcp for every table.
This worked great for me. However, I suggest modifying the import command with -E to handle identity columns and -q to quote identifiers. The -E will make sure the correct identity values are inserted.
1

You could always export the data from the Customers table to an Excel file and import that data into your Customers table.

To import/export data:

  1. Right click on database
  2. Go to Tasks
  3. Go to Import Data or Export Data
  4. Change the data source to Microsoft Excel
  5. Follow the wizard
Improve this answer

Comments

0

If both databases resides in the same instance of SQL Server, ie use same connection, this SQL might be helpful:

INSERT INTO [DestinationDB].[schema].[table] ([column])
SELECT [column] FROM [OriginDB].[schema].[table]
GO
Improve this answer

1 Comment

This did not work for me. I went with "select * into newtablename from oldtablename" as shown in this thread (stackoverflow.com/questions/680552/table-level-backup)
0

I just like to add some screen shoots for Sql Server Management Studio 2008. It is correct to use the steps describe previously. When you have the 'Generate and Publish Script' -> 'Set Script Options' then press Advance to see script options:

Where to find Advanced script options

For Sql Server Management Studio 2008 the option to included data is 'Types of data to script'

Types of data to script

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.