Inserting multiple rows with a single INSERT INTO

[hartley_aaron]

Hi,

I have an application running on a wireless device and being wireless I
want it to use bandwidth as efficiently as possible. Therefore, I want
the SQL statement that it uploads to the SQL Server to be as efficient
as possible. In one instance, I give it four records to upload, which
currently I have as four seperate SQL statements seperated by a ";".
However, all the INSERT INTO... information is the same each time, the
only that changes is the VALUES portion of each command. Also, I have
to have the name of each column to receive the data (believe it or not,
these columns are only a small subset of the columns in the table).

Here is my current SQL statement:

INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,
dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,
dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)

VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]',
'3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',
520);
INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,
dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,
dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)

VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]',
'3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);
Since the SQL statement INSERT INTO portion remains the same every
time, it would be good if I could have the INSERT INTO portion only
once and then any number of VALUES sections, something like this:

INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,
dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,
dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)
VALUES (1, 'Raw Material Receiving', '[MachineNo]',
'[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,
'610T142', 'K8', 520)
VALUES (1, 'Raw Material Receiving', '[MachineNo]',
'[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,
'14841', 'B9', 344);

But this is not a valid SQL statement. But perhaps someone with a more
comprehensive knowledge of SQL knows of way. Maybe there is a way to
store a string at the header of the command then use the string name in
each seperate command(??)

[David Portas]

Use UNION :

INSERT INTO tblInvTransLog (...)
SELECT ... UNION ALL
SELECT ... UNION ALL
... etc

Avoid using ambiguous local formats for date literals. The formatted
date strings in your code will cause an error under certain server or
client settings. As a bonus, the more reliable ISO format 'YYYYMMDD'
will save you an additional 2 characters per row!

--
David Portas
SQL Server MVP
--

[Thomas R. Hummel]

A quick and easy way to cut down on the number of characters sent
across the line would be to turn this into a stored procedure instead
of using a direct insert. This is generally recommended anyway for
performance and security reasons. You would still have a few extra
characters with each call, but the number would be reduced. For
example, you could have:

EXEC tblInvTransLog_Insert 1, 'Raw Material Receiving', '[MachineNo]',
'[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,
'610T142', 'K8', 520;
EXEC tblInvTransLog_Insert 1, 'Raw Material Receiving', '[MachineNo]',
'[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,
'14841', 'B9', 344

You might want to use a more descriptive stored procedure name, but
hopefully you get the idea here.

HTH,
-Tom.