MSSQL 08 Oct 2019

Copy all rows from one table to another new table in MSSQL

When you want to create a new table in a Database B and fill it with the rows of another table in Database A, then you can use the SELECT … INTO format like in the following query:

SELECT * INTO [DatabaseB].[dbo].[customer]
FROM [DatabaseA].[dbo].[customer]

Here are some important notes for this query:

You can also create a new table in the same database as the source table. The query in that case would look like that:
```
SELECT * INTO [DatabaseA].[dbo].[customerNew]
FROM [DatabaseA].[dbo].[customer]
```
The name of the new table has to be different with the name of the existing table, otherwise you will get the There is already an object named ‘customer’ in the database error.

You can create and copy also specific columns by defining their names:

SELECT id, firstname INTO [DatabaseB].[dbo].[customer]
FROM [DatabaseA].[dbo].[customer]

If you want to rename the columns in the new table and copy the content from the old table, then use the following query:

INSERT INTO [DatabaseB].[dbo].[customer]
(
    newColumn1,
    newColumn2,
    newColumn3
)
SELECT oldColumn1,
       oldColumn2,
       oldColumn3
FROM [DatabaseA].[dbo].[customer]

More information about the INTO keyword can be found here.