multiple insert statment in a single stored procedure

Question

multiple insert statment in a single stored procedure

priya.preejaa

SSC Veteran

Points: 267
More actions
June 15, 2009 at 11:23 pm

#384776

I have three tables.Am using asp.net,c# and sql server 2005 for my application.
So for a single insert button i wanna to store the data in three diff tables using a single sp.
My tables are
category(cat_id(Pk-->autoincriment),description
articleMaster(cat_id(foreign key,article_id(auto incriment primary key),title,description
articleAttachmnet(article_id foreign key),attachment_id(autoincriment-primary key),path
using a single save button i wanna to store the relavent data into these three tables using a single store procedure
Please help me ....its urgent

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

priya.preejaa (6/15/2009)
I have three tables.Am using asp.net,c# and sql server 2005 for my application.
So for a single insert button i wanna to store the data in three diff tables using a single sp.
My tables are
category(cat_id(Pk-->autoincriment),description
articleMaster(cat_id(foreign key,article_id(auto incriment primary key),title,description
articleAttachmnet(article_id foreign key),attachment_id(autoincriment-primary key),path
using a single save button i wanna to store the relavent data into these three tables using a single store procedure
Please help me ....its urgent

You can write one SP with all parameters that are required for insertions in these three tables. Within the sp you write something like this.

Insert into categories(col names...) values(col values from the parameters passed)

Insert into articleMaster(col names...) values(col values from the parameters passed)

Insert into articleAttachmnet(col names...) values(col values from the parameters passed)

In the click event of the button you call this SP will all required parameters.

Pradeep Singh

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Hi

If you are using a Stored procedure use transactions.

"Keep Trying"

priya.preejaa SSC Veteran Points: 267 More actions · Answer 3

Hi,

but how to get the primary key of the first table from there?

first i wanna store data in category table....that cat_id i wanna store it on the other Article table

how to get that?

priya.preejaa SSC Veteran Points: 267 More actions · Answer 4

can u expain more...i dont know how to use transaction

can u demonstate the code....may be thats helpful for me.

ps. One Orange Chip Points: 29422 More actions · Answer 5

here is the logic

1.Begin a transaction

2. insert into category table

3. get max identity from this table

4. insert this max identity into article table

5. commit transaction

Create table test1(col1 int)

create table test2

(col1 int identity,

col2 varchar(10))

begin tran

insert into test2(col2) values('window1')

insert into test1 select max(col1) from test2

commit

write ur code with this logic and let us know the results

Pradeep Singh

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

priya.preejaa (6/16/2009)
can u expain more...i dont know how to use transaction
can u demonstate the code....may be thats helpful for me.

Transactions are used for maintaing data integrity. Lets says that you are inserting into two tables inside a procedure. Insertion to one table happens correctly and insertion to the second table results in a error. What you would want is to insert data into both of the tables or to neither of the tables. This is where transactions help by letting you roll back in case of errors. If there are no errors you commit the transaction.

Previous mails have described how to go about them. Revert back in case you have doubts.

"Keep Trying"

PhilPacha Hall of Fame Points: 3645 More actions · Answer 7

Where "PS" said "get max identity from this table", instead use SCOPE_IDENTITY. Read about it in Books Online (BOL). The problem with selecting the maximum identity from a table is that there is no guarantee that the row was inserted from the current invocation of *your* process. The SCOPE_IDENTITY function returns the just-added IDENTITY value from *your* process only.

ps. One Orange Chip Points: 29422 More actions · Answer 8

Good point. however i tested it with the example mentioned in the same post. I used begin tran which gave correct identity value for logged-in user's scope... however i'll try that as well..

Pradeep Singh