Ways to Insert data from one table to another - SQL Server

Method 1 :  INSERT INTO SELECT


Use this method if the destination table is already created in the destination database.  
USE  MyDb
GO


INSERT INTO DestinationTable(FirstName, LastName)

SELECT FirstName, LastName

FROM SourceDatabase.SourceTable
WHERE condition= 1


Method 2 :  SELECT INTO


Use this method if the destination table is not already created in the destination database. The SELECT statement would create the new table with same data types in the destination database along with copying the data.

SELECT FirstName, LastName


INTO DestinationTable

FROM SourceDatabase.SourceTable

WHERE condition= 1

In this case new table 'DestinationTable' will be created with same data type as the selected columns.

Insert Multiple Records Using One Insert Statement - SQL Server

How to insert multiple records using one insert statement(using UNION ALL ) instead of going for individual INSERT statements for each row. Here is the sample:


USE YourDbName

GO

INSERT INTO YourTable(FirstColumn, SecondColumn)

SELECT 'First' ,1

UNION ALL

SELECT 'Second' ,2

UNION ALL

SELECT 'Third' ,3

UNION ALL

SELECT 'Fourth' ,4

UNION ALL

SELECT 'Fifth' ,5

GO