Insert multiple records into a SQL table using a single query

Hello Experts;

I have a series of variables listed in the rows of a Delphi StringGrid, for example;

                 Column1      Column2        Column3        Column4        Column5      
Row1 |  variable A1 | variable B1 | variable C1 | Variable D1 | Variable E1
Row2 |  variable A2 | variable B2 | variable C2 | Variable D2 | Variable E2
Row3 |  variable A3 | variable B3 | variable C3 | Variable D3 | Variable E3
Row4 |  variable A4 | variable B4 | variable C4 | Variable D4 | Variable E4

The SQL query to insert Row 1 variables;

Declare @variableA1 as int =:variableA1
Declare @variableB1 as varchar(20) =:variableB1
Declare @variableC1 as varcahr(20) =:variableC1
Declare @variableD1 as smallint =:variableD1
Declare @variableE1 as decimal(11,4) =:variableE1

INSERT INTO SQLTableName
(variableA1,variableA2,variableA3,variableA4,variableA5
)

VALUES
(@variableA1,@variableA2,@variableA3,@variableA4,@variableA5
)

How do I insert the remaining rows into the SQLTableName using a single SQL query?

Thank you for the help,
Visionetv
visionetvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brant SnowCommented:
Looks like you are using a sql server, for sql server you can define the columns once and seperate multiple rows by columns

FOR EXAMPLE
INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

So for you, you would have to declare everything but then you could do this (note your original sql had errors, you need to declare the columns by the column names

INSERT INTO SQLTableName
(Column1, Column2, Column3, Column4, Column5
)

VALUES
(@variableA1,@variableB1,@variableC1,@variableD1,@variableE1)
, (@variableA2,@variableB2,@variableC2,@variableD2,@variableE2)
,(@variableA3,@variableB3,@variableC3,@variableD3,@variableE3)
, (@variableA4,@variableB4,@variableC4,@variableD4,@variableE4)
, (@variableA5,@variableB5,@variableC5,@variableD5,@variableE5)
visionetvAuthor Commented:
Thank you for the response. The query structure you suggest is what I'm doing (Column1, etc. was used for the example); here is the actual query;

Declare @INVNO as int =:INVNO
Declare @NOTATION as char(500) =:NOTATION
Declare @INVOICENO as int =:INVOICENO
Declare @DESCRIPTION as char(40) =:DESCRIPTION
Declare @INVOICEITEM as smallint =:INVOICEITEM
Declare @QUANTITY as decimal(11,4) =:QUANTITY
Declare @PRICE as decimal(11,4) =:PRICE
Declare @ITEMCOST as decimal(11,4 )=:ITEMCOST
Declare @BARCODE as char(20) =:BARCODE
Declare @STATUS as char(1) =:STATUS
Declare @CUSTID as int =:CUSTID
Declare @TAX1 as int =:TAX1
Declare @SALES1 as char(3) =:SALES1
Declare @DATE as int =:DATE
Declare @COST as decimal(11,4) =:COST
Declare @QUAN as decimal(11,4) =:QUAN
Declare @TaxAmount as decimal(11,4) =:TaxAmount
Declare @Site as char(4) =:Site
Declare @PrimaryID as int =:PrimaryID

INSERT INTO PINVDET
(PND_INVNO,PND_NOTATION,PND_INVOICENO,PND_DESCRIPTION,
PND_INVOICEITEM,PND_QUANTITY,PND_PRICE,PND_ITEMCOST,
PND_BARCODE,PND_STATUS,PND_CUSTID,PND_TAX1, PND_SALES1,
PND_DATE,PND_COST,PND_QUAN,PND_TaxAmount,PND_Site,PND_PrimaryID
)

VALUES
(@INVNO,@NOTATION,@INVOICENO,@DESCRIPTION,
@INVOICEITEM,@QUANTITY,@PRICE,@ITEMCOST,@BARCODE,@STATUS,
@CUSTID,@TAX1, @SALES1,@DATE,@COST,@QUAN,@TaxAmount,@Site,
@PrimaryID
);

Where I am having a problem is how do I repeat the query with a different set of variables for the next row in the table. The first record inserts fine but the second set tries to overwrite the first record in the first row, at least I think that's what happening. There are a possible 40 rows of variables in a single session, do I need to declare each variable for each row and assign a value to each through a parameter?

If so, how do I compensate for empty data rows as the number of records will vary between 1 and 40 per session. Will the query create a series of blank rows in the SQLTable when less than 40 rows have data entries?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
where does this data come from?

for sure, as long as you only perform INSERT, it cannot UPDATE an existing row (unless you INSERT into a VIEW that has a INSTEAD OF trigger that behind the scenes then indeed would do a UPDATE, but I think we can exclude that scenario here?)
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

visionetvAuthor Commented:
As mentioned in the original post, the data is coming from entries into a Delphi TStringGrid which has 10 columns, 40 rows and functions as a POS register interface. Data entries are UPC codes that match their string to a database that displays an item's description, price, etc. in individual rows in the TStringGrid.

I'm not trying to UPDATE a row rather write data into a new row that contains different variables displayed in the TStringGrid rows. The TStringGrid entries can be anywhere from 1 to 40 rows depending on the number of items being purchased. The data entries from the TStringGrid rows are passed as  parameters to the SQL query.

Apparently I cannot reuse the same DECLARE @ variable for the next row even though the values have changed. As Brant Snow suggested "you would have to declare everything" which I take to mean declare the variables in each TStringGrid row whether the row contains data entries or not.
visionetvAuthor Commented:
After some further research I found SQL 2008 INSERT INTO VALUES was expanded to allow multiple row inserts at once. I rewrote the query as follows and it seems to be working fine;

Declare @INVNO as int =:INVNO
Declare @NOTATION as char(500) =:NOTATION
Declare @INVOICENO as int =:INVOICENO
Declare @DESCRIPTION as char(40) =:DESCRIPTION
Declare @INVOICEITEM as smallint =:INVOICEITEM
Declare @QUANTITY as decimal(11,4) =:QUANTITY
Declare @PRICE as decimal(11,4) =:PRICE
Declare @ITEMCOST as decimal(11,4 )=:ITEMCOST
Declare @BARCODE as char(20) =:BARCODE
Declare @STATUS as char(1) =:STATUS
Declare @CUSTID as int =:CUSTID
Declare @TAX1 as decimal(11,4) =:TAX1
Declare @SALES1 as char(3) =:SALES1
Declare @DATE as int =:DATE
Declare @COST as decimal(11,4) =:COST
Declare @QUAN as decimal(11,4) =:QUAN
Declare @TaxAmount as decimal(11,4) =:TaxAmount
Declare @Site as char(4) =:Site
Declare @PrimaryID as int =:PrimaryID

Declare @INVNOa as int =:INVNOa
Declare @NOTATIONa as char(500) =:NOTATIONa
Declare @INVOICENOa as int =:INVOICENOa
Declare @DESCRIPTIONa as char(40) =:DESCRIPTIONa
Declare @INVOICEITEMa as smallint =:INVOICEITEMa
Declare @QUANTITYa as decimal(11,4) =:QUANTITYa
Declare @PRICEa as decimal(11,4) =:PRICEa
Declare @ITEMCOSTa as decimal(11,4 )=:ITEMCOSTa
Declare @BARCODEa as char(20) =:BARCODEa
Declare @STATUSa as char(1) =:STATUSa
Declare @CUSTIDa as int =:CUSTIDa
Declare @TAX1a as decimal(11,4) =:TAX1a
Declare @SALES1a as char(3) =:SALES1a
Declare @DATEa as int =:DATEa
Declare @COSTa as decimal(11,4) =:COSTa
Declare @QUANa as decimal(11,4) =:QUANa
Declare @TaxAmounta as decimal(11,4) =:TaxAmounta
Declare @Sitea as char(4) =:Sitea
Declare @PrimaryIDa as int =:PrimaryIDa

INSERT INTO PINVDET
(PND_INVNO,PND_NOTATION,PND_INVOICENO,PND_DESCRIPTION,
PND_INVOICEITEM,PND_QUANTITY,PND_PRICE,PND_ITEMCOST,
PND_BARCODE,PND_STATUS,PND_CUSTID,PND_TAX1, PND_SALES1,
PND_DATE,PND_COST,PND_QUAN,PND_TaxAmount,PND_Site,PND_PrimaryID
)

VALUES
(@INVNO,@NOTATION,@INVOICENO,@DESCRIPTION,
@INVOICEITEM,@QUANTITY,@PRICE,@ITEMCOST,@BARCODE,@STATUS,
@CUSTID,@TAX1, @SALES1,@DATE,@COST,@QUAN,@TaxAmount,@Site,
@PrimaryID),

(@INVNOa,@NOTATIONa,@INVOICENOa,@DESCRIPTIONa,
@INVOICEITEMa,@QUANTITYa,@PRICEa,@ITEMCOSTa,@BARCODEa,@STATUSa,
@CUSTIDa,@TAX1a, @SALES1a,@DATEa,@COSTa,@QUANa,@TaxAmounta,@Sitea,
@PrimaryIDa);
Brant SnowCommented:
Yes visionetv, you can see in my original answer it demonstrates multiple row inserts

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.