How do I insert to multiple tables in one SP without temptables

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Tables:

TAccount
AccountNo
Name
Address

all data in TAccount table will generate/insert in billing table

TBilling
BillingNo -on insert auto increment number
AccountNo -on insert get from TAccount table
Name -on insert get from TAccount table
Address -on insert get from TAccount table
Description -on insert get from @paramDescription
DueDate -on insert get from @paramDueDate

at the same time will also insert data to TBillingDetails table

TBillingDetails
No -on insert auto increment number
StatementNo -on insert get from TBilling table (from generated/auto increment)
Details -on insert 'RPT'
Amount -on insert 50.00

TBillingDetails table have multiple details per billing

Thanks in advanced! :)

Posted 12-Oct-14 22:22pm

dekdimaya

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mycroft Holmes · Answer 1 · 2014-10-12T22:30:00

Solution 1

Write a stored proc that inserts the tables sequentially trapping the SCOPE_IDENTITY() after each insert.

SQL

Declare @AccountID int, @BillingNo int

Insert TAccount...

Set @AccountID = SCOPE_IDENTITY()

Insert TBilling
AccountNo = @AccountID
...

Posted 12-Oct-14 22:30pm

Mycroft Holmes

Comments

Thanks7872 13-Oct-14 4:57am

Nice. Another option would be to use Triggers.

Mycroft Holmes 13-Oct-14 5:00am

No triggers are not valid for this solution, you have to assume the user is passing in a shed load of variables to be inserted, you don't want to be passing variables to triggers (not sure you CAN).

Actually triggers used for ANYTHING but auditing are a disaster and should be hunted out of the database.

Thanks7872 13-Oct-14 5:05am

First table will contain after insert trigger where the inserted values will already be available. Task is just to insert them into another table which will also contain the same trigger(To perform insert into 3rd one).

We can't pass values to trigger but they will be avaialble automatically.

Mycroft Holmes 13-Oct-14 5:10am

How are you going to get Description to the TBilling table and if you suggest inserting into the TAccount table I'll take your keyboard away from you!

dekdimaya 13-Oct-14 5:33am

hi thanks, no insert on the TAccount table, will only get data from TAccount table to be inserted to TBilling.

dekdimaya 13-Oct-14 5:00am

thanks guys, how about using CTE? can this be done by CTE? thanks

Mycroft Holmes 13-Oct-14 5:06am

[Personal opinion] CTE's are not intuitive, they read backwards and I have trouble dealing with them and I can't see any benefit by using a CTE!

Mycroft Holmes 13-Oct-14 5:11am

BTW after arguing with Rohan I noticed you are storing the address twice. This is a disaster, store it on the account table ONLY. NEVER, ever store data twice!

dekdimaya 13-Oct-14 5:32am

hi sir thanks, i know it is not a good idea to store same data, but the billing should be fixed once generated, address on the account can be modified.

dekdimaya 13-Oct-14 5:56am

Sir, here's the situation. Every month SP will be executed to generate billings of each of the account.

Sample Data:
812540 Michael Morer Nevada
935321 Isaac Newer San Antonio

Once SP is executed:
2 Billings are generated with 2 Billing Details each.

How can this be done using SP? thanks