Inserting Multiple Rows with One Insert Command

Insert multiple rows using single insert statement

Your syntax is slightly off:

Insert Into @AddressRecordsToPurge (GuidValue, GuidColumn, GuidTable)
SELECT EMPLOYMENTSEQUENCENUMBER, 'EMPLOYMENTSEQUENCENUMBER', 'ACCOUNTANTSREFERENCE'
FROM ACCOUNTANTSREFERENCE
WHERE CustomerNumber =  @CustomerNumber
    AND Customerversionnumber = @CustomerVersionNumber
    AND EMPLOYMENTSEQUENCENUMBER IS NOT NULL; 

inserting multiple rows with one insert command

Two solutions (source : http://appsfr.free.fr/spip.php?article21 ):

INSERT ALL
INTO table (column1, column2)
VALUES (value1, value2)
INTO table (column1, column2)
VALUES (value1, value2)
...etc...
SELECT * FROM DUAL ;

or

INSERT INTO table (column1, column2)
SELECT value1, value2 FROM DUAL UNION ALL
SELECT value1, value2 FROM DUAL UNION ALL
...etc...
SELECT value1, value2 FROM DUAL ;

How to add multiple rows using a single insert statement. Using the way in the description

This error is almost certainly caused by the column dob. You're not inserting a date or a date literal, you're inserting a random string, that is being interpreted as a column name.

As a general rule always explicitly convert dates into dates.

You have 2 options in SQL*Plus, you can insert an ANSI date literal

insert into patient (dob)
values ( date '&dob')

where &dob is in the form YYYY-MM-DD.

You can also explicitly convert the column into a date using the to_date function.

insert into patient (dob)
values ( to_date('&dob','yyyy-mm-dd'))

where &dob is of the form yyyy-mm-dd. This is known as a datetime format-model of which there's a long list you can play around with.

All of this assumes that the column dob is a date. If it's not please change it to one. It's never worth the hassle of storing dates as a string. I would highly recommend always explicitly storing century as well. How is Oracle to know whether you're talking about 2067 or 1967?

Guffa's answer takes care of the multiple row element of your question.

Insert multiple rows WITHOUT repeating the INSERT INTO ... part of the statement?

INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'

For SQL Server 2008, can do it in one VALUES clause exactly as per the statement in your question (you just need to add a comma to separate each values statement)...

Insert multiple rows of data in a single SQL statement

Multi-row insert has been part of the SQL standard since SQL-92, and many of the modern DBMS' support it. That would allow you to do something like:

insert into MyTable ( Name,     Id,   Location)
             values ('John',    123, 'Lloyds Office'),
                    ('Jane',    124, 'Lloyds Office'),
                    ('Billy',   125, 'London Office'),
                    ('Miranda', 126, 'Bristol Office');

You'll notice I'm using the full form of insert into there, listing the columns to use. I prefer that since it makes you immune from whatever order the columns default to.

If your particular DBMS does not support it, you could do it as part of a transaction which depends on the DBMS but basically looks like:

begin transaction;
insert into MyTable (Name,Id,Location) values ('John',123,'Lloyds Office');
insert into MyTable (Name,Id,Location) values ('Jane',124,'Lloyds Office'),
insert into MyTable (Name,Id,Location) values ('Billy',125,'London Office'),
insert into MyTable (Name,Id,Location) values ('Miranda',126,'Bristol Office');
commit transaction;

This makes the operation atomic, either inserting all values or inserting none.

Insert Query to insert multiple rows in a table via select and output clause. SQL Server 2008

Change your procedure like below,and try again.

ALTER PROCEDURE usp_newServerDetails(@appid int, @envid int,@serType varchar(20),@servName varchar(20))
    AS
BEGIN
  BEGIN TRY

   DECLARE @Output TABLE (ID int,TableName VARCHAR(50),cmKey VARCHAR(50)) --table variable for keeping Inserted ID's

        BEGIN TRAN

         IF EXISTS ( SELECT 1 FROM configpthmaster cm  WHERE cm.appid = @appid ) 
                        AND  ( SELECT 1 FROM configkeydetails ck  WHERE ck.appid = @appid ) --add a conditon to satisfy the valid insertions

         BEGIN

            INSERT INTO server_detials(envid,servertype,servername)
                OUTPUT inserted.serverid,'server_detials',NULL INTO @Output(ID,TableName,cmKey )
            VALUES(@envid ,@serType ,@servName)

            INSERT INTO configdetails(serverid,servertype,configpath,configtype)
                OUTPUT inserted.configid,'configdetails',cm.Key INTO @Output(ID,TableName,cmKey )
            SELECT t.ID,cm.servertype,cm.configpath,cm.configtype 
            FROM configpthmaster cm 
              CROSS APPLY (SELECT ID FROM @Output WHERE TableName='server_detials')t
            WHERE cm.appid = @appid

            INSERT INTO configkeydetails(configId,keyname)
            SELECT ID,cmKey FROM @Output 
            WHERE TableName='configdetails'

        END

       COMMIT TRAN
  END TRY

    BEGIN CATCH

        IF @@TRANCOUNT > 0 
        ROLLBACK 

    END CATCH

END

Inserting multiple rows into some table using values from select statement

Yes, but you need to be careful how you do it. In this case, it appears you want a cross join:

INSERT INTO SomeTable (UserID, ModuleID, ModuleRights)
    SELECT u.UserId, v.ModuleID, v.ModuleRights
    FROM Users u CROSS JOIN
         (VALUES (1, 15), 
                 (2, 1), 
                 (4, 3), 
                 (8, 7)
         ) v(ModuleID, ModuleRights);

Insert multiple rows in a single query using results of a select statement

You can unpivot using cross apply:

insert into @stringList (val) 
    select v.col
    from table1 t1 cross apply
         (values (t1.col1), (t1.col2)) v(col)
    where t1.id = something;

---

Related Topics