How to export all data from table to an insertable sql format?

sql sql-server copy export ssms
victorio picture victorio · Dec 12, 2013 · Viewed 248.3k times · Source

I have a Table (call it A_table) in a database (call it A_db) in Microsoft SQL Server Management Studio, and there are 10 rows.

I have another database (call it B_db), and it has a Table (call it B_table), which has the same column settings as A_table has. But the B_table is empty.

What I want:

  • Copy every rows from A_table to B_table.

Is there any option in Microsoft SQL Server Management Studio 2012, to create an insert SQL from a table? Or is there any other option to do that?

Answer

Armand picture Armand · Dec 12, 2013

Quick and Easy way:

  1. Right click database
  2. Point to tasks In SSMS 2017 you need to ignore step 2 - the generate scripts options is at the top level of the context menu Thanks to Daniel for the comment to update.
  3. Select generate scripts
  4. Click next
  5. Choose tables
  6. Click next
  7. Click advanced
  8. Scroll to Types of data to script - Called types of data to script in SMSS 2014 Thanks to Ellesedil for commenting
  9. Select data only
  10. Click on 'Ok' to close the advanced script options window
  11. Click next and generate your script

I usually in cases like this generate to a new query editor window and then just do any modifications where needed.

Related questions

Copy tables from one database to another in SQL Server

I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do …

Read More
SQL Converting Column type from float to varchar

I'm trying to change the datatype in a column in a table from Float (null) to Varchar(25) (null). The largest float in the current data is 12 digits, but more digits may have to be added in the future, hence varchar(25). …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More