inserting into multiple rows with one call

[Andy Smith]

I have had considerable trouble adding multiple rows into a MSSQL7 database.

I have tried looping through an array to create the SQL string, followed by
adding one long SQL query.

I have had no success with either method. The only joy is that the first
record is added successfully. If anyone can recommend any pointers I would
be very grateful.

All the variables are being passed correctly and everything works fine if
there is only one record to be added.

for i = lbound(array) to ubound(array)

sql = sql & "INSERT INTO TABLE_NAME (Var1,Var2,Var3,Var4,Var5,Var6,Var7)
VALUES ("
sql = sql & "'" & var1 & "',"
sql = sql & "'" & var2 & "',"
sql = sql & "'" & var3 & "',"
sql = sql & "'" & var4 & "',"
sql = sql & "'" & var5 & "',"
sql = sql & "'" & var6 & "',"
sql = sql & "'" & var7 & "');"
Next
conn.Execute sql

Thanks,
Andy

[Ray at]

What happens when you do:

for i = lbound(array) to ubound(array)

sql = sql & "INSERT INTO TABLE_NAME (Var1,Var2,Var3,Var4,Var5,Var6,Var7)
VALUES ("
sql = sql & "'" & var1 & "',"
sql = sql & "'" & var2 & "',"
sql = sql & "'" & var3 & "',"
sql = sql & "'" & var4 & "',"
sql = sql & "'" & var5 & "',"
sql = sql & "'" & var6 & "',"
sql = sql & "'" & var7 & "');"
Next
'''''''''''''''conn.Execute sql
response.write sql

Copy and paste that into query analyzer and execute.

And in your sample code, var1, var2, etc. are arrays also, or are you just
inserting the same duplicate data ubound(array) + 1 times?

Ray at home


--
Will trade ASP help for SQL Server help


"Andy Smith" <someone@microsoft.com> wrote in message
news:%23qgRuqHXDHA.608@TK2MSFTNGP12.phx.gbl...

> I have had considerable trouble adding multiple rows into a MSSQL7

database.

>
> I have tried looping through an array to create the SQL string, followed

by

> adding one long SQL query.
>
> I have had no success with either method. The only joy is that the first
> record is added successfully. If anyone can recommend any pointers I would
> be very grateful.
>
> All the variables are being passed correctly and everything works fine if
> there is only one record to be added.
>
> for i = lbound(array) to ubound(array)
>
> sql = sql & "INSERT INTO TABLE_NAME (Var1,Var2,Var3,Var4,Var5,Var6,Var7)
> VALUES ("
> sql = sql & "'" & var1 & "',"
> sql = sql & "'" & var2 & "',"
> sql = sql & "'" & var3 & "',"
> sql = sql & "'" & var4 & "',"
> sql = sql & "'" & var5 & "',"
> sql = sql & "'" & var6 & "',"
> sql = sql & "'" & var7 & "');"
> Next
> conn.Execute sql
>
> Thanks,
> Andy
>
>

[Andy Smith]

I get the following sql query

INSERT INTO ORDER_INFO (Var1,Var2,Var3,Var4,Var5,Var6,Var7) VALUES
('1','PID01','15.00','1','M','SID','BID');INSERT INTO ORDER_INFO
(Var1,Var2,Var3,Var4,Var5,Var6,Var7) VALUES
('2','PID06','15.00','1','M','SID','BID');

Andy

[Ray at]

And you can successfully execute that in query analyzer?

Are your columns really named var1, var2, etc.?

Ray at home

--
Will trade ASP help for SQL Server help


"Andy Smith" <someone@microsoft.com> wrote in message
news:OG93L9HXDHA.2516@TK2MSFTNGP09.phx.gbl...

> I get the following sql query
>
> INSERT INTO ORDER_INFO (Var1,Var2,Var3,Var4,Var5,Var6,Var7) VALUES
> ('1','PID01','15.00','1','M','SID','BID');INSERT INTO ORDER_INFO
> (Var1,Var2,Var3,Var4,Var5,Var6,Var7) VALUES
> ('2','PID06','15.00','1','M','SID','BID');
>
> Andy
>
>

[Andy Smith]

No my variables are not var1 etc. I wrote the loop for the e-mail. my sql
query uses cookies, session variables and response.form values so it was
neater to re-write it.

My SQL output is:

INSERT INTO ORDER_INFO (OrderID, ProductID, UnitPrice, Quantity, Size,
ShopID, BandID) VALUES ('60', 'META03', '18.00', '1', 'M', 'BSI', 'META');
INSERT INTO ORDER_INFO (OrderID, ProductID, UnitPrice, Quantity, Size,
ShopID, BandID) VALUES ('60', 'IRON07', '18.00', '1', 'M', 'BSI', 'IRON');

I have posted this into my query analyzer and I successfully add the second
row, but it appears the first record was overwritten.

Andy

[Ray at]

"Andy Smith" <someone@microsoft.com> wrote in message
news:RsiYa.2807$0p2.677@news-binary.blueyonder.co.uk...

>
> INSERT INTO ORDER_INFO (OrderID, ProductID, UnitPrice, Quantity, Size,
> ShopID, BandID) VALUES ('60', 'META03', '18.00', '1', 'M', 'BSI', 'META');
> INSERT INTO ORDER_INFO (OrderID, ProductID, UnitPrice, Quantity, Size,
> ShopID, BandID) VALUES ('60', 'IRON07', '18.00', '1', 'M', 'BSI', 'IRON');
>
> I have posted this into my query analyzer and I successfully add the

second

> row, but it appears the first record was overwritten.

Um, are you sure about that? Even if you do a select * afterwards in QA,
you do not see both records? Do you have any triggers setup that, I don't
know, delete records with the same OrderID, or anything odd like that?

What happens if you execute that same query but by disabling any triggers
that may exist using this method.
[url]http://www.aspfaq.com/2016[/url]

Ray at home

[Andy Smith]

I've solved the problem...

I reverse coded my query analyzer input and successfully added multiple
records.

Thanks for your help

Andy

[Unregistered]

Could you please post the solution that you came up with as I am having the same issue. Thanks.