One form Populating Multiple SQL Tables

I have a Transaction form that needs to populate the Transaction and TransactionDetail Tables in SQL Server 2005. The tables look like the following (Obviously trimmed down the field lists):

UNID - Primary Key - Autonumber

UNID - Primary Key - Autonumber
T_UNID - Transaction Table UNID

What I want to do is populate both tables. I need to get the Transaction Table UNID and place it into the TransactionDetail tables T_UNID field. I guess I could do this with a select to and then populate the id with another insert into the TransactionDetail table. Just wasn't sure if there was a better way. I know that when your in access you can create forms with the use of a subform and populate multiple tables and the id would appear correctly in the TransactionDetail table.
Michael WilliamsApplication DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SELECT @@Identity AS FirstTableUNID;
INSERT INTO SecondTable (FirstTableID, OtherColumns) VALUES (FirstTableUNID, #OtherColumns#);

You insert the first table information, select the unique id created and use that in the second table insert.
DBAduck - Ben MillerPrincipal ConsultantCommented:
You would need to use the identity value.

DECLARE @idvalue int
INSERT INTO firsttable (columns) values (values)
INSERT INTO secondtable (FirstTableID, columns) values (@idvalue, othervalues)

You need to pull the SCOPE_IDENTITY() value into a variable so that you can use it for the second table insert.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The way I did it wouldn't work?
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

DBAduck - Ben MillerPrincipal ConsultantCommented:
No, your statement does this:

SELECT @@identity as FirstTableID
which would return it as FirstTableID and not be in a variable for use in the second statement.  @@identity could be error prone and the correct way is to use SCOPE_IDENTITY() because it is scoped to the current session, where @@IDENTITY is a GLOBAL variable and could be affected by triggers, etc.

Michael WilliamsApplication DeveloperAuthor Commented:
dbaduck, that code worked perfectly.

My next question would be iwhere should I put this code ?

I'm creating a form via Dreamweaver and using coldfusion CFC's to insert the data. Would the best practice be to insert the data into a temp table and then process it on the SQL Server side via a stored procedure ? Or should I go ahead and process it at client side ?
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, the real answer to this is that you would be best off to create a stored procedure and then call it from the Cold Fusion side of things with parameters.

Then the code could simply use the variables that are passed in and the code above would be just part of it.

Can that work for you?  Do you have to have inline inserts?
Michael WilliamsApplication DeveloperAuthor Commented:
That should be fine..

Is there  a way to reverse any inserts that happen ? Or would you just have to do a delete row ?
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, if you wrap it in a transaction then you could rollback.


.... statements

IF condition to commit  AND @@TRANCOUNT = 1
   ROLLBACK -- this would completely rollback everything that was done since the BEGIN TRAN.

The reason you check @@trancount is to make sure that you only have one BEGIN TRAN.  If you have multiples and @@TRANCOUNT > 1 is that when you issue a COMMIT TRAN it decrements the @@TRANCOUNT by 1 so if you have it at 2 then you would have to issue 2 COMMITs and if you have more than 1 then you could have a bigger problem and you should resolve it before you decide to commit that many times.  Anyway, that is a bigger discussion, but thought you should be aware.
ColdFusion has <cftransaction> tag
If you wrap your queries in it, black0ps's version with "SELECT @@Identity AS FirstTableUNID" should work just as fine.

Of course, you can use pure SQL solution - that would mean stored procedure. Generally it is considered to be safer.
Michael WilliamsApplication DeveloperAuthor Commented:
I was going to use the CODE-A, but decided to use CODE-B. With using a combination of CFTransaction and CFTry I'm about to make sure I can isert data into 2 tables. When I receive an error I can use <cftransaction action = "rollback"/>.  I've tested this code and it works perfectly. CODE-B will be placed into a CFComponent.

Used the following url for help with setting up my CF tags.

Thanks for all of your help.
DECLARE @idvalue int
INSERT INTO [000-TEST] ([000_A], [000_B])
VALUES ('Damaged Goods', 'TEST');
SELECT [000_TESTID], [000_a], [000_B] FROM [000-TEST] WHERE [000_TESTID] = @idvalue;
<cfset commitIt = "Yes">
<cftransaction action = "begin">
  <cfquery name = "Transaction" result="Transactionresult" dataSource = "#application.datasource#">
    INSERT INTO [000-TEST] ([000_A], [000_B])
    VALUES ('NEW', 'TETE');
  <cfquery name = "TransactionDetail" result="TransactionDetailresult" dataSource = "#application.datasource#">
    INSERT INTO [001-SECOND] ([001_TESTID], [001_B])
    VALUES (#Transactionresult.identitycol#, 'TEST');
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
    	<cfdump var="#cfcatch#">
  <cfif commitIt>
	<cfdump var="#Transactionresult#" />
    <cftransaction action = "commit"/>

Open in new window

Michael WilliamsApplication DeveloperAuthor Commented:
I took pieces of each of the responses to identify my final coding solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.