Saturday 22 August 2009

Different Ways of INSERTING Data INTO a Table

Approach1: Using INSERT VALUES

INSERT INTO Table1 (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO Table1 (FirstCol, SecondCol)
VALUES ('Second',2);

Approach2: Using INSERT SELECT

INSERT INTO Table1 (FirstCol, SecondCol)
SELECT 'First',1;
INSERT INTO Table1 (FirstCol, SecondCol)
SELECT 'Second',2;

Approach3: Using INSERT UNION ALL

INSERT INTO Table1 (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2

Approach4: Using INSERT SELECT FROM

INSERT INTO Table1 (FirstCol, SecondCol)
SELECT FirstName, number FROM Table2


Approach 5: Using SELECT INTO – Temp table

The following statement creates an in-memory table named “TempTable” and inserts the specified data into that:

SELECT FirstName, LastName
INTO TempTable
FROM Person.Contact

Approach 6: Using SELECT INTO – Permanent table

The following statement inserts the data from an existing table named “Table2” into another existing table named “Table1”:

SELECT FirstName, LastName
INTO db1Name.dbo.Table1
FROM dbo2Name.dbo.Table2

Tip: this approach is helpful when you want to send data from a table in a server into another table in another server

No comments: