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.

Answer 3

To copy rows from A_table  in A_db to B_table in B_db,

You can try the given below SQL query in Microsoft SQL 

Server Management studio:

INSERT INTO  B_db.dbo.B_table

SELECT * FROM A_db.dbo.A_table;

Steps:

1. Open a new query window in SSMS

2. Run the mentioned query

3. Ensure that databases are accessible, and you have the necessary permissions.

This will make copy of all the rows from A_table to B_table if their schema matches