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 with the source table. The query in that case would look like that:
    SELECT * INTO [DatabaseA].[dbo].[customer]
    FROM [DatabaseA].[dbo].[customer]
  • The name of the new table does not have to be the same with the name of the existing table
  • If the table in the INTO clause already exists then you will get the following error message: There is already an object named ‘customer’ in the database.
  • You can create and copy also specific columns by defining their names:
    SELECT id, firstname INTO [DatabaseA].[dbo].[customer]
    FROM [DatabaseA].[dbo].[customer]

More information about the INTO keyword can be found here.

comments powered by Disqus