Insert multiple records using stored procedure

Question

phuynh77 0 Newbie Poster

16 Years Ago

Hi Everyone,
I would need your help. I have a project that I need to insert multiple records into my table. Here is more detail.
I design a purchase order table with 3 columns (a, b, c):
a: is primary key with identity (1,1) primary key
b: is defined integer (allow duplicate)
c: is varchar(5)

I want to have multiple records with same value of a.
For ex: when I create purchase order # 1. I have multiple records inside the PO# 1. Also with PO# 2, I have multiple records....also so on.

How can I write a stored procedure for this needed?

mssql

2 Contributors
19 Replies
3K Views
6 Days Discussion Span
Latest Post 16 Years Ago Latest Post by phuynh77

dickersonka 104 Veteran Poster

16 Years Ago

advice would be you don't want multiple records with the same value of a

for one your database is set to not allow it
for two, you need to uniquely identify this record

might want to rethink the schema

something with a purchaseorder
and purchaseorderdetails

purchaseorder
PURCHASE_ORDER_ID
PO_DATE
CLIENT_ID

purchaseorderdetails
PURCHASE_ORDER_DETAILS_ID
PURCHASE_ORDER_ID
ITEM_ID
ITEM_AMOUNT

this will allow you to have multiple records for a single purchase order

dickersonka 104 Veteran Poster

16 Years Ago

you normally wouldn't pass arrays to the stored procedure

for each insert you should make the call to insert purchaseorderdetails, ie 5 items 5 insert calls

passing arrays to a stored procedure i wouldn't consider good practice, the workaround is normally to pass a comma delimited string, but once again i would recommend making the database calls to insert from code side and not rely on it from database side

dickersonka 104 Veteran Poster

16 Years Ago

Although you aren't passing arrays, you should return the id of the purchase order to your code.

Then call a stored procedure for sp_AddPurchaseOrderDetails for each item you have, 1 item 1 call, 3 items 3 calls

dickersonka 104 Veteran Poster

16 Years Ago

sure, you had a good start on the first one
we'll do the purchaseorder

create procedure sp_AddPurchaseOrder
(
   @POInputDate smalldatetime,
   @PurchaseOrderId int OUTPUT
)
as

insert into PurchaseOrder (PO_DATE) values (@POInputDate)

select @PurchaseOrderId = @@IDENTITY

then you will use the same concept for creating purchaseorderdetails, except you will pass in the purchaseorderid as a parameter

(btw i just used the date column, and not client_id like we talked about before)

dickersonka 104 Veteran Poster

16 Years Ago

purchaseorderdetails
PURCHASE_ORDER_DETAILS_ID
PURCHASE_ORDER_ID
ITEM_ID
ITEM_AMOUNT

create procedure sp_AddPurchaseOrderDetails
(
   @PurchaseOrderId int,
   @ItemId int,
   @ItemAmount decimal(10,2),
   @PurchaseOrderDetailsId int OUTPUT
)
as

insert into PurchaseOrderDetails (PURCHASE_ORDER_ID, ITEM_ID, ITEM_AMOUNT) values (@PurchaseOrderId, @ItemId, @ItemAmount)

select @PurchaseOrderDetailsId = @@IDENTITY

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

phuynh77 0 Newbie Poster · Answer 1 · 2008-10-29T07:51:27+00:00

advice would be you don't want multiple records with the same value of a
for one your database is set to not allow it
for two, you need to uniquely identify this record
might want to rethink the schema
something with a purchaseorder
and purchaseorderdetails
purchaseorder
PURCHASE_ORDER_ID
PO_DATE
CLIENT_ID
purchaseorderdetails
PURCHASE_ORDER_DETAILS_ID
PURCHASE_ORDER_ID
ITEM_ID
ITEM_AMOUNT
this will allow you to have multiple records for a single purchase order

Hi There,
Thank you for your idea. I will update my purchaseorder table and add purchaseorderdetail table into my database. Could you please send me the stored procedure that allows how to have multiple records insert into. Thanks again.

phuynh77 0 Newbie Poster · Answer 2 · 2008-10-30T01:10:22+00:00

you normally wouldn't pass arrays to the stored procedure
for each insert you should make the call to insert purchaseorderdetails, ie 5 items 5 insert calls
passing arrays to a stored procedure i wouldn't consider good practice, the workaround is normally to pass a comma delimited string, but once again i would recommend making the database calls to insert from code side and not rely on it from database side

===================
Hi dickersonka
I'd need your help again. Per your help, I create 2 tables (PurchaseOrder & PurchaseOrderDetails)
PO table: POID int Identity(1,1) primary key
POInputDate smalldatetime

POD table: PODID int identity(1,1) primary key,
PODID int foreign key,
ItemID int, ItemPrice money
I have coded sp_AddPurchaseOrder with multiple insert (ex: 3 times insert). Somecases, I want to insert only 1 or 2 records and ignore the third insert. But my web application send me a message that Can not insert the records. Could you please share your idea? Your help is appriciated.

Below is my stored procedure:

create proc sp_AddPurchaseOrder
(
@PurchaseOrderInputDate smalldatetime,

@PurchaseOrderID_1 int,
@PurchaseOrderItemID_1 int,
@PurchaseOrderItemPrice_1 money,
@PurchaseOrderQuantity_1 decimal(10,2),

@PurchaseOrderID_2 int,
@PurchaseOrderItemID_2 int,
@PurchaseOrderItemPrice_2 money,
@PurchaseOrderQuantity_2 decimal(10,2),

@PurchaseOrderID_3 int,
@PurchaseOrderItemID_3 int,
@PurchaseOrderItemPrice_3 money,
@PurchaseOrderQuantity_3 decimal(10,2)
)
as 
declare @ident int
insert into purchaseorder values (@PurchaseOrderInputDate)

set @ident = @@IDENTITY

Insert into PurchaseOrderDetails (PurchaseOrderID, PurchaseOrderItemID,
				PurchaseOrderItemPrice, PurchaseOrderQuantity) 
values (@ident, @PurchaseOrderItemID_1, @PurchaseOrderItemPrice_1, @PurchaseOrderQuantity_1)

if @PurchaseOrderID_2 <> ' ' 
begin
	if @PurchaseOrderItemID_2 <> ' '
	begin
		if @PurchaseOrderItemPrice_2 <> ' ' 
		begin
		if @PurchaseOrderQuantity_2 <> ' '
			begin
Insert into PurchaseOrderDetails (PurchaseOrderID, PurchaseOrderItemID,
				PurchaseOrderItemPrice, PurchaseOrderQuantity) 
values (@ident, @PurchaseOrderItemID_2, @PurchaseOrderItemPrice_2, @PurchaseOrderQuantity_2)
				end
			end
		end
	end

if @PurchaseOrderID_3 <> ' ' 
begin
	if @PurchaseOrderItemID_3 <> ' '
	begin
		if @PurchaseOrderItemPrice_3 <> ' ' 
		begin
		if @PurchaseOrderQuantity_3 <> ' '
			begin
Insert into PurchaseOrderDetails (PurchaseOrderID, PurchaseOrderItemID,
				PurchaseOrderItemPrice, PurchaseOrderQuantity) 
values (@ident, @PurchaseOrderItemID_3, @PurchaseOrderItemPrice_3, @PurchaseOrderQuantity_3)
				end
			end
		end
	end

phuynh77 0 Newbie Poster · Answer 3 · 2008-10-30T01:23:10+00:00

dickersonka,
I am new with SQL server 2005 and still new to stored procedure. Could you please help me with the sp coding?

phuynh77 0 Newbie Poster · Answer 4 · 2008-10-30T06:37:30+00:00

sure, you had a good start on the first one
we'll do the purchaseorder
create procedure sp_AddPurchaseOrder
(
   @POInputDate smalldatetime,
   @PurchaseOrderId int OUTPUT
)
as

insert into PurchaseOrder (PO_DATE) values (@POInputDate)

select @PurchaseOrderId = @@IDENTITY
then you will use the same concept for creating purchaseorderdetails, except you will pass in the purchaseorderid as a parameter
(btw i just used the date column, and not client_id like we talked about before)

==================
Hi dickersonka,
I am very sorry to bother you many. I can't figure how to code the second sp. Could you please point to me the "purchaseorderdetails" ? Thanks much

phuynh77 0 Newbie Poster · Answer 5 · 2008-10-30T09:06:03+00:00

purchaseorderdetails
PURCHASE_ORDER_DETAILS_ID
PURCHASE_ORDER_ID
ITEM_ID
ITEM_AMOUNT

create procedure sp_AddPurchaseOrderDetails
(
   @PurchaseOrderId int,
   @ItemId int,
   @ItemAmount decimal(10,2),
   @PurchaseOrderDetailsId int OUTPUT
)
as

insert into PurchaseOrderDetails (PURCHASE_ORDER_ID, ITEM_ID, ITEM_AMOUNT) values (@PurchaseOrderId, @ItemId, @ItemAmount)

select @PurchaseOrderDetailsId = @@IDENTITY

=============================
Hi There,
I am confused now. Please...please...please help me.
Below is the stored procedure that I have learned from you:

create proc sp_AddPurchaseOrder
(
@PurchaseOrderInputDate smalldatetime,
@PurchaseOrderID int output
)
as
insert into purchaseorder (purchaseorderinputdate)
values(@PurchaseOrderInputDate)
select @PurchaseOrderID = @@IDENTITY

I executed this sp first... then execute the one below:


create proc sp_AddPurchaseOrderDetails
(
@PurchaseOrderID int,
@PurchaseOrderItemID int,
@PurchaseOrderItemPrice money,
@PurchaseOrderQuantity decimal(10,2),
@PurchaseOrderDetailsID int output
)
as
insert into purchaseorderdetails (purchaseorderid, purchaseorderitemid, purchaseorderitemprice, purchaseorderquantity)
values (@PurchaseOrderID, @PurchaseOrderItemID, @PurchaseOrderItemPrice, @PurchaseOrderQuantity)

select @PurchaseOrderDetailsID = @@IDENTITY

=====================

but I got the error message:
Msg 201, Level 16, State 4, Procedure sp_AddPurchaseOrderDetails, Line 0
Procedure or function 'sp_AddPurchaseOrderDetails' expects parameter '@PurchaseOrderID', which was not supplied.

Could you please help again? your help is much appreciated.

dickersonka 104 Veteran Poster · Answer 6 · 2008-10-30T09:12:49+00:00

the problem is that when you execute the first query, it is returning your purchaseorderid, which links the purchaseorderdetails to it

you need to pass this in as a parameter from code side

phuynh77 0 Newbie Poster · Answer 7 · 2008-10-30T09:18:01+00:00

the problem is that when you execute the first query, it is returning your purchaseorderid, which links the purchaseorderdetails to it
you need to pass this in as a parameter from code side

==================
Hi dickersonka
I am using C# to code the front application. Do you have any idea for passing the @IDENTITY or you know any link for these information. I'd need to learn about this.

dickersonka 104 Veteran Poster · Answer 8 · 2008-10-30T09:24:11+00:00

Here's a link

http://blogs.msdn.com/vsdata/archive/2006/08/21/711310.aspx

you can change your stored procedure, by removing the "OUT" parameter, and instead of
select @PurchaseOrderId = @@IDENTITY

you just use return @@IDENTITY

phuynh77 0 Newbie Poster · Answer 9 · 2008-10-30T09:32:49+00:00

Here's a link
http://blogs.msdn.com/vsdata/archive/2006/08/21/711310.aspx
you can change your stored procedure, by removing the "OUT" parameter, and instead of
select @PurchaseOrderId = @@IDENTITY
you just use return @@IDENTITY

=====

Do you have a link that has information for Web application using c# syntax? The link you have sent was for windows application.
Also, you mean that I don't need to state

select @PurchaseOrderID = @@IDENTITY
I can replace with
return @@IDENTITY

is that you mean?

dickersonka 104 Veteran Poster · Answer 10 · 2008-10-30T09:37:09+00:00

yes that is what i mean

it was just code, but anyways
here's a better link

http://www.dotnetjunkies.com/WebLog/skiff/archive/2005/02/12/54047.aspx

dickersonka 104 Veteran Poster · Answer 11 · 2008-10-30T09:38:49+00:00

sorry, this way does use the way we had first with
OUTPUT and select @PurchaseOrderID = @@IDENTITY

so change it back to that to follow that example, no more changes now :-)

phuynh77 0 Newbie Poster · Answer 12 · 2008-10-30T10:11:49+00:00

sorry, this way does use the way we had first with
OUTPUT and select @PurchaseOrderID = @@IDENTITY
so change it back to that to follow that example, no more changes now :-)

=====
Hi ,
Per coding, I almost get it, accept this piece:

newId = (int)cmdl.Parameters["@NewUserId"].Value;

based on my Visual studio 2005 (C#). It does not recognise
(int)cmdl.Parameters["@NewUserId"].Value;

do you have any idea?

dickersonka 104 Veteran Poster · Answer 13 · 2008-10-30T10:21:17+00:00

NewUserId will be PurchaseOrderId

make sure you have this in the stored procedure as well, like we said before

@PurchaseOrderId int OUTPUT

also make sure if you modified the code it is cmd not cmdl, unless you changed the name

phuynh77 0 Newbie Poster · Answer 14 · 2008-11-04T00:45:46+00:00

Hi dickersonka,
Awesome for your help. I got it. Thank millions. You are the best.