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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
  • to choose certain table to script data , try [SQL Formatter Add-In for SSMS](http://ssmsaddins.codeplex.com/) – Mandoleen Mar 07 '12 at 11:44
  • There was a [question](http://stackoverflow.com/questions/18199/how-do-i-generate-scripts-that-will-rebuild-my-ms-sql-server-2005-database-with) about this yesterday, and the best solution was the [Database Publishing Wizard](http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard). – Forgotten Semicolon Aug 21 '08 at 16:13
  • Another way would be to use Redgate's excellent SqlCompare tool – edosoft May 19 '09 at 07:59
  • If you're using Visual Studio 2008 Team Edition for Database Professionals, this can be done with a simple wizard: – Slavo Aug 21 '08 at 16:22

7 Answers7

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

dbry23
  • 3
  • 2
The Matt
  • 6,618
  • 7
  • 44
  • 61
  • 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. – Nick Westgate May 19 '11 at 06:29
  • Fantastic. Just what I was looking for! – Antony Jul 08 '11 at 15:55
  • 3
    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 – Kristopher Ives Feb 28 '12 at 00:15
  • 3
    Its worth noting that in SQL MGMT 2008 the advanced button on the options screen - is where the option for the data is – Rob Mar 27 '12 at 14:07
  • can you run the scrip with a where clause? – Piotr Kula Aug 01 '12 at 13:47
  • 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](http://i.imgur.com/MNtKRKV.png) shows. – Sebastian Mar 20 '13 at 21:21
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.

SteveC
  • 15,808
  • 23
  • 102
  • 173
Unsliced
  • 10,404
  • 8
  • 51
  • 81
  • oh this is perfect. scriptable, flexible, well-supported and very fast. thanks! – kenwarner Sep 28 '10 at 17:40
  • See also [this answer](http://dba.stackexchange.com/a/41044/30685) for a script which will generate a batchfile calling bcp for every table. – wimh Nov 19 '13 at 08:20
  • 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. – Ken Richards Jan 14 '14 at 16:42
  • How to perform on multiple tables? – shzyincu Aug 31 '17 at 06:12
22

You can check the following article to see how you can do this by using both SQL Server native tools and the third party tools: SQL Server bulk copy and bulk import and export techniques

Disclaimer: I work for ApexSQL as a Support Engineer

Hope this helps

Milica Medic Kiralj
  • 3,580
  • 31
  • 31
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
Bryan Roth
  • 10,479
  • 15
  • 47
  • 56
0

For Data Expoer as SQL script in SQL server 2005,

http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Able Alias
  • 3,824
  • 11
  • 58
  • 87
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]: image missing because I do not have the right reputation :(

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

![Types of data to script]: image missing because I do not have the right reputation :(

Lars Ladegaard
  • 116
  • 1
  • 10
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
Nordin
  • 3,087
  • 5
  • 28
  • 35
  • This did not work for me. I went with "select * into newtablename from oldtablename" as shown in this thread (http://stackoverflow.com/questions/680552/table-level-backup) – Gary Thomann Aug 01 '13 at 00:58