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!
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Be careful using @@identity SQL value as it gets the last identity value created. Instead, use the SCOPE_IDENTITY function.
ASKER
That worked like a charm. Thanks for the detail, that really helped!
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.
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.
Open in new window
See http://msdn.microsoft.com/en-us/library/ms187342.aspx for an explanation of the @@identity function.