DML INSERT with multiple ways in SQL Server 2005: (Multiple INSERT statement)

Whoever is aware with SQL-Server must be aware with simple INSERT statement. Generally we used INSERT records in table with either of the following SQL statements.
--create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

1.)

--must give column value in sequence of column defined in table.

Insert into Car values ('Honda CRV','SUV')

OR

2.)

--you can change the sequence order of column after table name

--and can give value in defined order in INSERT statement

Insert into Car (CarName,CarDesc) values ('Honda Pilot','SUV')

3.)

Above are the common INSERT statement and widely used as well. Apart from these, there are several ways for INSERT statement.

With SELECT……..UNION ALL

INSERT INTO Car(CarName,CarDesc)

SELECT 'Toyota a','toyota' UNION ALL

SELECT 'Toyota b','toyota' UNION ALL

SELECT 'Toyota C','toyota'

4.)

Suppose you have one more table from which you want to populate your CAR table.

--create first table for demonstration

CREATE TABLE CAR_Master

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

INSERT INTO Car_Master(CarName,CarDesc)

SELECT 'Mercedes s','Mercedes' UNION ALL

SELECT 'Mercedes c','Mercedes'

Now, I will populate CAR table from CAR_Master table.

INSERT INTO Car(CarName,CarDesc)

SELECT CarName,CarDesc FROM Car_Master where CarName like 'm%'

5.)

If you wish to populate your INSERT statement with stored procedure than do follow the below given query.

--Create SP which will return result set

--NOTE: You can use multiple resultset in one SP

--and all the records will be INSERTed to our table

--Make sure, you have same number of column with same datatype in

--all record set you choose in SP

CREATE PROC CarNames

AS

SET NOCOUNT ON

SELECT CarName,CarDesc FROM Car_Master

RETURN

Now simply, I can run following INSERT query.

INSERT INTO CAR (CarName,CarDesc)

EXEC carnames

Reference: Ritesh Shah