Friday, 13 July 2012

How to insert data from one table to another table in SQL/Oracle

Method 1 : INSERT INTO SELECTThis method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
----Create TestTableCREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))----INSERT INTO TestTable using SELECTINSERT INTO TestTable (FirstName, LastName)SELECT FirstName, LastNameFROM Person.ContactWHERE EmailPromotion = 2----Verify that Data in TestTableSELECT FirstName, LastNameFROM TestTable----Clean Up DatabaseDROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table 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 INSERTSELECT FirstName, LastNameINTO TestTableFROM Person.ContactWHERE EmailPromotion = 2----Verify that Data in TestTableSELECT FirstName, LastNameFROM TestTable----Clean Up DatabaseDROP TABLE TestTable
GO



More Details

No comments:

Post a Comment