For training related to .net,OOPS,design patterns, MVC,WCF and .net fundamentals
contact at sukeshmarla@gmail.com or Click Here

02 May 2011

Insert Into Vs Select Into


INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table or From any other sources like views,Derived Tabes,TempTables etc.
USE AdventureWorks
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO


SELECT INTO 
This method is used when table is not created earlier and needs to be created when data from one table Or any other sources is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Both of the above method works with database temporary tables (global, local).