Sql INSERT from One Table to Two

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

ppostonPresident/OwnerAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
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
 
Nico BontenbalCommented:
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
 
momi_sabagCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Shaun KlineLead Software EngineerCommented:
Be careful using @@identity SQL value as it gets the last identity value created. Instead, use the SCOPE_IDENTITY function.
0
 
ppostonPresident/OwnerAuthor Commented:
That worked like a charm.  Thanks for the detail, that really helped!
0
 
David ToddSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.