How do you export all tables to a CSV file using SQL Management Studio?

Question

When I right click on the database I want to export data from, I only get to select a single table or view, rather than being able to export all of the data. Is there a way to export all of the data?

If this is not possible, could you advise on how I could do the following:

• I have two databases, with the same table names, but one has more data than the other
• They both have different database names (Table names are identical)
• They are both on different servers

I need to get all of the additional data from the larger database, into the smaller database.

Both are MS SQL databases

Answer 1

Being that both are MS SQL Servers, on different hosts... why bother with CSV when you can setup a Linked Server instance so you can access one instance from the other via a SQL statement?

1. Make sure you have a valid user on the instance you want to retrieve data from - it must have access to the table(s)
2. Create the Linked Server instance

3. Reference the name in queries using four name syntax:

   INSERT INTO db1.dbo.SmallerTable
   SELECT *
     FROM linked_server.db.dbo.LargerTable lt
    WHERE NOT EXISTS(SELECT NULL
                       FROM db1.dbo.SmallerTable st
                      WHERE st.col = lt.col) 
   

Replace WHERE st.col = lt.col with whatever criteria you consider to be duplicate values between the two tables.

Answer 2

There is also a very good tool by Redgate software that syncs data between two databases.

I've also used SQL scripter before to generate a SQL file with insert statements that you can run on the other database to insert the data.

Answer 3

If you right-click on the database, under the Tasks menu, you can use the Generate Scripts option to produce SQL scripts for all the tables and data. See this blog post for details. If you want to sync the second database with the first, then you're better off using something like Redgate as suggested in mpenrow's answer.