Insert From Select

The method of inserting records from one table to another differs depending on whether or not the target table already exists. Neither method is difficult, however one method requires more coding.

Insert where target table does not exist

If it does not, then you are in luck, there is a method that could not be simpler using SELECT..INTO. Select into automatically creates a table based on the result of a select statement. This select statement can be as simple or complex as you like. The only think you need to do, is add the line: INTO TABLENAME right above the FROM statement. Here goes:

[cc lang=”sql”]
— Copy all the records into a new table
SELECT *
INTO CustomerCopy
FROM SalesLT.Customer

— View the table
SELECT *
FROM CustomerCopy
[/cc]

It should be noted that this method does not create any keys that may be existing from the old table to the new table.

Insert where target table does exist

It’s not that this method is more difficult, it just requires more code. To do this, you need to use the INSERT INTO statement. When using this method, you need to specify the column names of the target table you want to insert, and the column names of the source table you are inserting from. Here is an example:

[cc lang=”sql”]

INSERT INTO [AdventureWorksLT2008].[SalesLT].[Customer]
(
[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[CompanyName]
,[SalesPerson]
,[EmailAddress]
,[Phone]
,[PasswordHash]
,[PasswordSalt]
,[ModifiedDate]
)

SELECT
[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[CompanyName]
,[SalesPerson]
,[EmailAddress]
,[Phone]
,[PasswordHash]
,[PasswordSalt]
,[ModifiedDate]
FROM [AdventureWorksLT2008].[SalesLT].[Customer2]
[/cc]

The brackets surrounding the column names above is optional. If you are using SQL Server Management Studio (ssms), the easiest way to do this is to right click on the table name, choose ‘Script Table As’ -> ‘Create To’ -> ‘New Query Window’. This will automatically generate the insert code for all the column names to insert into. The only thing you have to do is fill in your select statement.