Insert by Parameter

6 Jun 2006 7:04 PM

Paul

How can one insert by parameter?
For example, in a table "Products" that has a column called StoreID, how can
one insert in the same way one selects, i.e.,
SELECT col1, col2 from Products WHERE StoreID = @StoreID ?

I would like to say something like

Insert Col1, col2, etc... WHERE StoreID = @StoreID.

(More specifically, I am writing a asp.net page, and would like to add a
product by StoreID, so I would like to pass the param StoreID to the Stored
Procedure.)

TIA,

Paul

6 Jun 2006 7:09 PM

Tom Moreau

Inside your stored proc, try:

insert Products values (StoreID, ColA, ColB) values (@StoreID, @ColA, @ColB)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..

"Paul" <PaulContac***@TheCornerStore.com> wrote in message
news:Kkkhg.46$Wd5.19@fe10.lga...

How can one insert by parameter?
For example, in a table "Products" that has a column called StoreID, how can
one insert in the same way one selects, i.e.,
SELECT col1, col2 from Products WHERE StoreID = @StoreID ?

I would like to say something like

Insert Col1, col2, etc... WHERE StoreID = @StoreID.

(More specifically, I am writing a asp.net page, and would like to add a
product by StoreID, so I would like to pass the param StoreID to the Stored
Procedure.)

TIA,

Paul

6 Jun 2006 7:13 PM

David Portas

Paul wrote:
Show quoteHide quote

> How can one insert by parameter?
> For example, in a table "Products" that has a column called StoreID, how can
> one insert in the same way one selects, i.e.,
> SELECT col1, col2 from Products WHERE StoreID = @StoreID ?
>
> I would like to say something like
>
> Insert Col1, col2, etc... WHERE StoreID = @StoreID.
>
> (More specifically, I am writing a asp.net page, and would like to add a
> product by StoreID, so I would like to pass the param StoreID to the Stored
> Procedure.)
>
> TIA,
>
> Paul

Maybe this is what you mean:

INSERT INTO table1 (col1, col2, col3)
SELECT col1, col2, col3
FROM table2
WHERE storeid = @storeid ;

Take a look at Books Online for the full INSERT syntax.

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

6 Jun 2006 7:22 PM

arc

Paul wrote:
Show quoteHide quote

> How can one insert by parameter?
> For example, in a table "Products" that has a column called StoreID, how can
> one insert in the same way one selects, i.e.,
> SELECT col1, col2 from Products WHERE StoreID = @StoreID ?
>
> I would like to say something like
>
> Insert Col1, col2, etc... WHERE StoreID = @StoreID.
>
> (More specifically, I am writing a asp.net page, and would like to add a
> product by StoreID, so I would like to pass the param StoreID to the Stored
> Procedure.)
>
> TIA,
>
> Paul

Where conditions apply to rows already in the table. Insert adds a new
row. So logically, what you have asked doesn't make sense.
I have two guesses as to what you really want
1) Perhaps you want an update, not an insert?
2) Perhaps you want to insert "after" a particular row that already
exists. But tables are ordered only (logically) in selects with an order
by. The clustered index lays out the physical order on disk (or the heap
structure if there isn't a clustered index), but that's largely
irrelevant. Just do the insert with the storeId that you want, and when
you make your select statements, order by storeId.

6 Jun 2006 7:44 PM

Paul

Thank you all, it has been very instructive.

I see what you are saying, arc - very interesting. I guess I need to do
this in Visual Studio, rather than in Sql Server 2005.
Thanks, all (but if you do come up with a brilliant and simple solution for
how to do that, please tell me).

Paul

Show quoteHide quote

"arc" <notarealaddr***@sorry.com> wrote in message
news:128blgq9svdr75f@corp.supernews.com...
> Paul wrote:
>> How can one insert by parameter?
>> For example, in a table "Products" that has a column called StoreID, how
>> can one insert in the same way one selects, i.e.,
>> SELECT col1, col2 from Products WHERE StoreID = @StoreID ?
>>
>> I would like to say something like
>>
>> Insert Col1, col2, etc... WHERE StoreID = @StoreID.
>>
>> (More specifically, I am writing a asp.net page, and would like to add a
>> product by StoreID, so I would like to pass the param StoreID to the
>> Stored Procedure.)
>>
>> TIA,
>>
>> Paul
>
> Where conditions apply to rows already in the table. Insert adds a new
> row. So logically, what you have asked doesn't make sense.
> I have two guesses as to what you really want
> 1) Perhaps you want an update, not an insert?
> 2) Perhaps you want to insert "after" a particular row that already
> exists. But tables are ordered only (logically) in selects with an order
> by. The clustered index lays out the physical order on disk (or the heap
> structure if there isn't a clustered index), but that's largely
> irrelevant. Just do the insert with the storeId that you want, and when
> you make your select statements, order by storeId.

6 Jun 2006 8:30 PM

David Portas

Paul wrote:

> Thanks, all (but if you do come up with a brilliant and simple solution for
> how to do that, please tell me).
>
> Paul
>

Do what? I don't think any of us are sure what it is you are trying to
do.

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