Insert Multiple Rows Without Repeating the "Insert into ..." Part of the Statement

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 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;

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; 

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 multiple rows with single dynamic data

Simply define the correct columns in the select list:

INSERT INTO dbo.tblEmpDetails (EmpId, StateId, CountryId, Comments)
SELECT [<ReturnedColumn>], @StateId, @CountryId, @Comments
from dbo.FN_ListToTable (',', @EmpIds);

Also note that SQL Server has a built-in string_split() function.

Unique constraint on multiple rows..how just not insert duplicate without stopping all

Why not write your insert as select statement in such a way as to filter out duplicates using the where condition.

There's an example of this approach (though obviously not the exact same problem), but see this: Avoid duplicates in INSERT INTO SELECT query in SQL Server

In your case it would involve doing a join between AnnotationCommitReport and the insert as select, to avoid the duplicates.

Insert multiple rows into multiple table without repeating "INSERT INTO"

In Excel you can generate your SQL statements dynamically. You create a formula that has the sql in a string and concatenate the values dynamically. For example- see here.

---

Related Topics