insert to two tables

I posted this question previously but am now getting another error and fixing it (months ago). I am trying insert The ID2 column (pk) from table 2 into ID2 column (fk) of table 1. What the T-SQL is trying to do is check to see if the record in table 2 already exists in then just pass the value to the insert query for table 1. If the record does not exist then insert data into table 2first then perform the table 1 insert.

This SP works fine if the user does not exist in table 2. However, if the user does exist in table2 then instead of passing the ID, a null value is passed.

Can anyone tell me why the first query does not work? If there is an easier way of doing this then I am all ears.

Thank you

DECLARE @.IdentityHolder int

BEGIN TRANSACTION
IF EXISTS (SELECT ID2 FROM [tbl2] WHERE ID2 = @.ID2)
BEGIN
(SELECT ID2 FROM [tbl2] WHERE ID2 = @.ID2)
END
ELSE
BEGIN
INSERT INTO [tbl2] ([ID2], [FN], [LN], emailAdd])
VALUES ( @.ID2, @.fName, @.lName, @.emailAdd)
END
COMMIT

SET @.IdentityHolder = (ID2)
INSERT INTO [tbl1]([ID2], [event])
VALUES (@.ID2, @.event)Your column list has 5 cols and your value list has 4?|||nope, tbl2 column list has four - ([ID2], [FN], [LN], emailAdd]) and the values has four - (@.ID2, @.fName, @.lName, @.emailAdd)|||OK,

What;'s this then?

SET @.IdentityHolder = (ID2)|||Because your ID2 in your set statement is not correlated to ant table/column value

Try this

BEGIN TRANSACTION
INSERT INTO [tbl2]
([ID2], [FN], [LN], emailAdd])
VALUES ( @.ID2, @.fName, @.lName, @.emailAdd)

INSERT INTO [tbl1]
([ID2], [event])
VALUES (@.IdentityHolder, @.event)

IF @.@.error = 0
COMMIT
ELSE
ROLLBACK


You begin the tran ... If the first insert fails, the row exists in tbl2, so you insert into tbl1. If the first insert succeeds, the row did not exist in tbl2, so you have performed both inserts without an extra call to the database. The final check of @.@.error looks for a failure to insert into tbl1. If that <> 0, the entire transaction is rolled back.

Advanced error handling is left as an excercise to the developer.|||how does @.IdentityHolder know to get the value of ID2? @.IdentityHolder is a variable and needs to be set right?|||Violation of PRIMARY KEY constraint 'PK_tbl2. Cannot insert duplicate key in object 'tbl2'.

Cannot insert the value NULL into column 'ID2', table 'tbl1; column does not allow nulls. INSERT fails.|||i know what the violation is, when using the query you gave me I am not searching to see if the user already exists in the db. The "cannot insert a null" is the prblem I am still having. I am still unable to pull the fk value for tbl1 from tble2. Any ideas on how to do this?|||ok ... post the schema for both tables and let's work this out. I assumed in my solution that you were providing an incoming value for @.IdentityHolder. My mistake (you know what happens when you assume).

If this is not the case and you are looking for an identity value upon a successful insert into tbl2, you will want to use the scope_identity() function.|||yeah, i did scope first but it is giving me some other number. For example, if the value is 102 then scope gives me 17.

tbl1
id1 (pk)
event

tbl2
ID2(pk)
ID1(fk)
FN
LN
emailAdd|||so id1 in tbl2 is not an identity column?

Then you must be passing in a value for id1 if the entry does not exist in tbl2|||With this schema
tbl1
id1 (pk)
event

tbl2
ID2(pk)
ID1(fk)
FN
LN
emailAdd



CREATE proc Two_Table_Insert @.ID2 = null, @.ID1, @.FN, @.LN, @.emailAdd, @.event = null
AS
IF @.ID2 is not null
BEGIN
IF EXISTS (select 1 from tbl2 where ID2 = @.ID2)
BEGIN
BEGIN TRANSACTION
SET @.ID1 = ID1
FROM tbl2
WHERE ID2 = @.ID2
COMMIT
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tbl2 (ID2, ID1, FN, LN, emailAdd)
VALUES (@.ID2, @.ID1, @.FN, @.LN, @.emailAdd)
-- optional check status of 1st insert ...
-- rollback if failed and exit or do insert into tbl1 if event is not null
-- check status of second insert ... rollback if failed and exit
COMMIT
END|||I don't under stand why this line is in the script. "WHERE ID2 = @.ID2" I will not know this ID, it will increment aotumatically with the insert.

Also I did mess up the scema, it should look like this

tbl1
ID1(pk)
ID2(fk)
event

tbl2
ID2(pk)
FN
LN
emailAdd

When the user enter a request it will look like this:
102, 'john', 'doe', 'jdoe@.email.com', 'request desription'

the data will enter the db like this;
tbl2
102, john, doe, jdoe@.email.com,
tbl1
+1, 102, request description.

my orginal code work fine except I wasn't pulling the pk for tbl2 to fill tbl1.|||I don't under stand why this line is in the script. "WHERE ID2 = @.ID2" I will not know this ID, it will increment aotumatically with the insert.


so ... is ID2 in tbk2 an identity column?

is ID1 in tbl1 an identity column?|||so ... is ID2 in tbk2 an identity column?

is ID1 in tbl1 an identity column?

I guess I don't know what the term "identity column" means. ID2 is the PK of tbl2.|||BOL is your friend:




IDENTITY (Property)

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

Note The IDENTITY property is not the same as the SQL-DMO Identity property that exposes the row identity property of a column.


This is why I asked for the schema of the tables. a column that has the IDENTITY property will script out sort of like this


CREATE TABLE tbl2 (
ID2 int not null IDENTITY,
FN varchar(32) null,
LN varchar(32) null,
emailAdd varchar(255) null )


so ... does ID2 in tbl2 have the IDENTITY property? How about ID1 in tbl1?

If ID2 is an identity column, you can capture the value when you insert a row by using the SCOPE_IDENTITY() function. If not, then we have to use another way to get the value of ID2 upon insert into tbl2.|||Sorry, I know that as the primary key, but yes both ID1 and ID2 are Identity columns. I tried using scope_Identity but it only grabs the insert value of ID2 and not the value of ID2if it exist.|||You have stated that tbl2.id2 is an identity column, but you also stated in a prior post to this thread that :

When the user enter a request it will look like this:
102, 'john', 'doe', 'jdoe@.email.com', 'request desription'

The user does not provide the value of an identity column for an insert where the column is a primary key, unless you set identity_insert on. They can for the table where the value would be the FK.

Nevertheless, I'm going to take the data as stated in the quote above and give you what would work.


CREATE proc Two_Table_Insert @.ID2, @.FN, @.LN, @.emailAdd, @.event
AS
DECLARE @.tbl2ID2 int, @.err int
IF EXISTS (select 1 from tbl2 where ID2 = @.ID2)
BEGIN
INSERT INTO tbl1 (ID2, event)
values (@.id2, @.event)

SELECT @.err = @.@.ERROR
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tbl2 (FN, LN, emailAdd)
VALUES (@.FN, @.LN, @.emailAdd)

SELECT @.err = @.@.ERROR
IF @.err = 0
BEGIN
select @.tbl2ID2 = scope_identity()
INSERT INTO tbl1 (ID2, event)
values (@.tbl2ID2, @.event)

SELECT @.err = @.@.ERROR
IF @.err = 0
COMMIT
ELSE
ROLLBACK
END
ELSE
ROLLBACK
END
RETURN @.err|||still not working, the scope_Identity is getting the value from the tbl1 not tbl2. Is there a way to get the value of strored procedure parameters. Also I was wrong, id2 is not an Identity column (it is the PK) because it is supplied by the user.|||I got it to work i just used the @.ID2 in the value of both tables and it worked. When I first set up the db both values were automatically generated by the db, but then i changed the scema and forgot i could do it this way. thanks|||glad to help. should you post again, besure to include all elements of the schema ... as you can see, it was important to arrive at a workable solution.|||no doubt, i can see clearly now the rain is gone. ;)