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

Question

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 row 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 1

Use the following steps to export all the data from the table to an insertable SQL format:

Step 1: Right-click on database

Step 2: Point to tasks. 

Step 3: Select the generate scripts

Step 4: Click on next >> Choose tables >> Click next >> Click advanced

Step 5: Now, scroll to data to script - i.e. called types of data to script in SMSS 2014.

Step 6: Select the data only. Click on 'Ok' to close the advanced script options window

Step 7: Click on next and generate your own script.

Answer 2

You can export all data from the table to an insertable sql format through SSMS. Go through the following steps:

1. Right click on the database and after that select Tasks> Generate Scripts.

2. Select the table(s) you wish to export.

3. In Set Scripting Options, select Save as File and Single File.

4. Under Advanced settings, tick Schema and Data.

5. At the end of the procedure, SSMS will give you a file containing all INSERT statements for all the data in the table.