Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


One form Populating Multiple SQL Tables

Posted on 2008-11-18
Medium Priority
Last Modified: 2013-12-24
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.
Question by:Michael Williams
  • 4
  • 4
  • 2
  • +1

Assisted Solution

black0ps earned 400 total points
ID: 22988384
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.
LVL 25

Accepted Solution

DBAduck - Ben Miller earned 800 total points
ID: 22989682
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.

Expert Comment

ID: 22990065
The way I did it wouldn't work?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 22990087
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.


Author Comment

by:Michael Williams
ID: 22994251
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 ?
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 23001351
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?

Author Comment

by:Michael Williams
ID: 23004126
That should be fine..

Is there  a way to reverse any inserts that happen ? Or would you just have to do a delete row ?
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 23004717
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.

Assisted Solution

eszaq earned 800 total points
ID: 23010021
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.

Author Comment

by:Michael Williams
ID: 23028555
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


Author Closing Comment

by:Michael Williams
ID: 31518003
I took pieces of each of the responses to identify my final coding solution.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question