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

Advertisements

4 Responses to “DML INSERT with multiple ways in SQL Server 2005: (Multiple INSERT statement)”

  1. Anonymous Says:

    I want to insert values in multiple tables at a time using one insert statement anybody answer please

  2. Anonymous Says:

    I want to insert values in multiple tables at a time using one insert statement anybody answer please

  3. Ritesh Shah Says:

    you have to write separate INSERT statement for that. can't be accomodate in single INSERT statement.

  4. Ritesh Shah Says:

    you have to write separate INSERT statement for that. can't be accomodate in single INSERT statement.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: