pass the parameters with more then one insert into statement

We could do this
Insert into tablename (field1, field2) values ('apple','23'), ('organes',
34), etc.
How can we pass the parameters so I can do that in stored procedure?
Thanks.
Grant
Who gives a {censored} if I am wrong.Grant,
Create an xml document and pass it to the stored procedure. See function
"openxml" in BOL for more info.
AMB
"Grant" wrote:

> We could do this
> Insert into tablename (field1, field2) values ('apple','23'), ('organes',
> 34), etc.
>
> How can we pass the parameters so I can do that in stored procedure?
>
> Thanks.
>
> --
> Grant
> Who gives a {censored} if I am wrong.
>
>|||Grant wrote:
> We could do this
> Insert into tablename (field1, field2) values ('apple','23'), ('organes',
> 34), etc.
>
> How can we pass the parameters so I can do that in stored procedure?
>
> Thanks.
>
> --
> Grant
> Who gives a {censored} if I am wrong.
INSERT INTO tablename (col1, col2)
SELECT @.p1, @.p2 UNION ALL
SELECT @.p3, @.p4 UNION ALL
SELECT @.p5, @.p6 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||but you cannot do that in SP, right? We will not know how many records will
be inserted.
Grant
Who gives a {censored} if I am wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1144093592.580516.164870@.i40g2000cwc.googlegroups.com...
> Grant wrote:
> INSERT INTO tablename (col1, col2)
> SELECT @.p1, @.p2 UNION ALL
> SELECT @.p3, @.p4 UNION ALL
> SELECT @.p5, @.p6 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||You could pass the data as a comma separated string, there's a good
article here:
http://www.sommarskog.se/arrays-in-sql.html
that will describe efficient ways to split it back out into a temporary
table, from which you could easily call a multirow insert.|||Grant wrote:
> but you cannot do that in SP, right? We will not know how many records wil
l
> be inserted.
> --
> Grant
You can do it like the following example. I'm assuming Col1 is the key
column here. Procs can have over 2000 optional parameters. If you need
as many as that then I'd consider the XML solution.
CREATE PROC dbo.usp_tablename_insert
(
@.p1 INTEGER = NULL,
@.p2 INTEGER = NULL,
@.p3 INTEGER = NULL,
@.p4 INTEGER = NULL,
@.p5 INTEGER = NULL,
@.p6 INTEGER = NULL
)
AS
BEGIN
INSERT INTO tablename (col1, col2)
SELECT col1, col2
FROM
(SELECT @.p1, @.p2 UNION ALL
SELECT @.p3, @.p4 UNION ALL
SELECT @.p5, @.p6)
AS T(col1, col2)
WHERE col1 IS NOT NULL ;
END ;
RETURN ;
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||>> but you cannot do that in SP, right? We will not know how many records [sic] w
ill
be inserted. <<
teh syntax you guessed at in your first posting was Standard SQL, but
SQL Server does not support it yet.
You can have just over 1000 parameters in T-SQL, so you could use
David's suggestion with a really long union of pairs. This code will
port, avoid proprietary XML tricks and dynamic SQL. Do you often have
over 500 rows (NOT records) to insrt?|||One table has up to 20 records and another one up to 30 so I am using David
suggestion. I do have one table that could go over hundred so I may use
openxml method for that one but I may just send out 100 at a time because I
think opensml is not good for performance.
Grant
Who gives a {censored} if I am wrong.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144270087.388066.264100@.v46g2000cwv.googlegroups.com...
> be inserted. <<
> teh syntax you guessed at in your first posting was Standard SQL, but
> SQL Server does not support it yet.
> You can have just over 1000 parameters in T-SQL, so you could use
> David's suggestion with a really long union of pairs. This code will
> port, avoid proprietary XML tricks and dynamic SQL. Do you often have
> over 500 rows (NOT records) to insrt?
>