Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2007
    Posts
    18

    Unanswered: 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

    Code:
    	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)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Your column list has 5 cols and your value list has 4?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2007
    Posts
    18
    nope, tbl2 column list has four - ([ID2], [FN], [LN], emailAdd]) and the values has four - (@ID2, @fName, @lName, @emailAdd)
    Last edited by swb; 10-19-07 at 12:04.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK,

    What;'s this then?

    SET @IdentityHolder = (ID2)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Because your ID2 in your set statement is not correlated to ant table/column value

    Try this
    Code:
     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.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Aug 2007
    Posts
    18
    how does @IdentityHolder know to get the value of ID2? @IdentityHolder is a variable and needs to be set right?

  7. #7
    Join Date
    Aug 2007
    Posts
    18
    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.

  8. #8
    Join Date
    Aug 2007
    Posts
    18
    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?

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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.

    -- This is all just a Figment of my Imagination --

  10. #10
    Join Date
    Aug 2007
    Posts
    18
    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

  11. #11
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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

    -- This is all just a Figment of my Imagination --

  12. #12
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    With this schema
    tbl1
    id1 (pk)
    event

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


    Code:
    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

    -- This is all just a Figment of my Imagination --

  13. #13
    Join Date
    Aug 2007
    Posts
    18
    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.

  14. #14
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by swb
    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?

    -- This is all just a Figment of my Imagination --

  15. #15
    Join Date
    Aug 2007
    Posts
    18
    Quote Originally Posted by tomh53
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •