[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql INSERT from One Table to Two

Posted on 2012-09-13
6
Medium Priority
?
409 Views
Last Modified: 2012-09-14
I need to post a record from our application table to the users table once the user has been approved.  The query below fails when it hits the second insert statement.  The reason being that I am updating one column from the application but I'm not sure how to handle the other 2 columns.  Since they do not allow nulls, the transaction fails.  I've noted the columns in the sql statement as well.  The UsersCOC.UserID is a foreign key with it's parent in users.id.  The last field, COCAdmin simply needs to be set to 'False'.

Any help for this rookie would be appreciated.

Thanks!

Begin Transaction

INSERT INTO users (church,contact,taxnum,phone,address,city,state,zip,email,username,password,code)
SELECT  church,contact,taxnum,phone,address,city,state,zip,email,username,password,code
FROM application
WHERE id = @id


INSERT INTO UserCOC (COC)
SELECT (COC)
FROM application
WHERE id=@id
-- UserCOC also has 2 fields which do not allow nulls
-- 1) UserID which is a FK with users.id being the primary key
-- 2) COCAdmin which is a bit which needs to be set to 'False' initially

UPDATE users                                                                                                      
SET DateStarted = getdate()
WHERE id = @id

DELETE FROM application
WHERE id = @id

ROLLBACK Transaction

END

Open in new window

0
Comment
Question by:pposton
6 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 38398351
Does this work:
INSERT INTO UserCOC (COC,UserID, COCAdmin)
SELECT (COC, @@identity, 0)
FROM application
WHERE id=@id

Open in new window

See http://msdn.microsoft.com/en-us/library/ms187342.aspx for an explanation of the @@identity function.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38398355
i did not understand which value you need for the second column but for the third one you can use


INSERT INTO UserCOC (COC,COCAdmin)
SELECT COC, 0
FROM application
WHERE id=@id
-- UserCOC also has 2 fields which do not allow nulls
-- 1) UserID which is a FK with users.id being the primary key
-- 2) COCAdmin which is a bit which needs to be set to 'False' initially
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 38398434
OK, from the insert into users, you need to get the identity value that was generated.

So, declare a variable (like @userid int) before the first insert then immediately after the first insert do :

set @userid = @@identity

then use @userid in the second insert.

As for COCAdmin then simply use the value 'False' or 0

Begin Transaction

declare @userid int

INSERT INTO users (church,contact,taxnum,phone,address,city,state,zip,email,username,password,code)
SELECT  church,contact,taxnum,phone,address,city,state,zip,email,username,password,code
FROM application
WHERE id = @id

set @userid = @@identity

INSERT INTO UserCOC (COC, Userid, cocadmin)
SELECT COC, @userid, 'False' 
FROM application
WHERE id=@id
-- UserCOC also has 2 fields which do not allow nulls
-- 1) UserID which is a FK with users.id being the primary key
-- 2) COCAdmin which is a bit which needs to be set to 'False' initially

UPDATE users                                                                                                      
SET DateStarted = getdate()
WHERE id = @id

DELETE FROM application
WHERE id = @id

ROLLBACK Transaction

END

Open in new window


Oh, and because the two inserts are part of the same transaction, you will want to check if @userid is not NULL before the second insert (in case there was an error in the first - which means @@identity will be NULL).
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 27

Expert Comment

by:Shaun Kline
ID: 38398442
Be careful using @@identity SQL value as it gets the last identity value created. Instead, use the SCOPE_IDENTITY function.
0
 

Author Closing Comment

by:pposton
ID: 38398767
That worked like a charm.  Thanks for the detail, that really helped!
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38400250
Hi,

One way to construct constants for @TRUE and @FALSE is one I learnt from early c++

#declare TRUE 1 == 1 // 1 is equivalent to one. Its also the C & C++ value for true
#declare FALSE !TRUE // False is simply not true.

HTH
  David

PS I think vb and maybe access have the bit values opposite for true/false.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

830 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