Page 1 of 4 123 ... LastLast
Results 1 to 15 of 57

Thread: Insert SP

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Insert SP

    I am trying to insert data that doesnt exsist in one table from another, but every time I execute my SP returns 0 values. I need to combine these two tables into one.

    Code:
    ALTER PROCEDURE [dbo].[InsertExclusions]
    AS 
    INSERT INTO dbo.[Extended Exclusions]
    ([Last Name], [Issue Date], [First Name], [Exp Date], [IR#], MI, Sex, DOB, [Reason for Exclusion])
    SELECT [Last Name], [Issue Date], [First Name], [Expire Date], [IR#], MI, Sex, DOB, [Reason for Exclusion]
    FROM dbo.[30 Day exclusion] 
    WHERE NOT EXISTS(SELECT * FROM dbo.[Extended Exclusions]
    WHERE [id] = [ID])

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What do you suppose your subquery returns?
    Code:
    SELECT * FROM dbo.[Extended Exclusions]
    WHERE [id] = [ID]
    ...every ID in the table. That's what.

    Try this:
    Code:
    INSERT INTO dbo.[Extended Exclusions]
    		([Last Name],
    		[Issue Date],
    		[First Name],
    		[Exp Date],
    		[IR#],
    		MI,
    		Sex,
    		DOB,
    		[Reason for Exclusion])
    SELECT	[Last Name],
    		[Issue Date],
    		[First Name],
    		[Expire Date],
    		[IR#],
    		MI,
    		Sex,
    		DOB,
    		[Reason for Exclusion]
    FROM	dbo.[30 Day exclusion] 
    WHERE NOT EXISTS
    		(SELECT	*
    		FROM	dbo.[Extended Exclusions]
    		WHERE [Extended Exclusions].[id] = [30 Day exclusion].[ID])
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Insert Fails

    Thats the message I get


    Msg 515, Level 16, State 2, Procedure InsertExclusions, Line 3
    Cannot insert the value NULL into column 'ID', table 'TestExclusion.dbo.Extended Exclusions'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    (1 row(s) affected)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd guess that you want to include the ID column on the end of both the INSERT column list and the SELECT column list. SQL doesn't seem to like the implicit insertion of a NULL value that comes from not including the column in the INSERT.

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Pat Phelan
    I'd guess that you want to include the ID column on the end of both the INSERT column list and the SELECT column list. SQL doesn't seem to like the implicit insertion of a NULL value that comes from not including the column in the INSERT.

    -PatP
    this what you mean pat??

    Code:
    ALTER PROCEDURE [dbo].[InsertExclusions]
    AS 
    INSERT INTO dbo.[Extended Exclusions]
    		([Last Name],
    		[Issue Date],
    		[First Name],
    		[Exp Date],
    		[IR#],
    		MI,
    		Sex,
    		DOB,
    		ID,
    		[Reason for Exclusion])
    SELECT	[Last Name],
    		[Issue Date],
    		[First Name],
    		[Expire Date],
    		[IR#],
    		MI,
    		Sex,
    		DOB,
    		id,
    		[Reason for Exclusion]
    FROM	dbo.[30 Day exclusion] 
    WHERE NOT EXISTS
    		(SELECT	*
    		FROM	dbo.[Extended Exclusions]
    		WHERE [Extended Exclusions].[ID] = [30 Day exclusion].[id])

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Executed it and this is all I get

    (0 row(s) affected)

    (1 row(s) affected)


    there's 1300 records in the dbo.30 Day exclusion table

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How many of the rows in dbo.[30 Day exclusion] are currently missing from dbo.[Extended Exclusions] ?

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    records missing

    1309 records

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    1309 rows where? What is the output from:
    Code:
    SELECT Count(*)
    ,  Sum(CASE WHEN e.id IS NULL THEN 1) AS [ext_missing]
    ,  Sum(CASE WHEN t.id IS NULL THEN 1) AS [30_missing]
    ,  Sum(CASE WHEN e.id = t.id THEN 1) AS matching
       FROM dbo.[Extended Exclusions] AS e
       FULL OUTER JOIN dbo.[30 Day exclusion] AS t
          ON (t.id = e.id)
    -PatP
    Last edited by Pat Phelan; 12-27-07 at 18:50.

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    These are two totally different tables, one gets the 30 day exclusions and the other gets everything else. All I want to do is combine the two. the 30 day has a total of 1300 records and the extended exclusions have 3275

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please run the query... It will show how many rows are in each table (and not the other) and how many are in both tables. I think you may already have what you want, but not know that.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Pat Phelan
    1309 rows where? What is the output from:
    Code:
    SELECT Count(*)
    ,  Sum(CASE WHEN e.id IS NULL THEN 1) AS [ext_missing]
    ,  Sum(CASE WHEN t.id IS NULL THEN 1) AS [30_missing]
    ,  Sum(CASE WHEN e.id = t.id THEN 1) AS matching
       FROM dbo.[Extended Exclusions] AS e
       FULL OUTER JOIN dbo.[30 Day exclusion] AS t
          ON (t.id = e.id)
    -PatP
    just getting an error message, sorry Pat. Ok thank you for your help

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ')'.

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Smile

    Quote Originally Posted by Pat Phelan
    Please run the query... It will show how many rows are in each table (and not the other) and how many are in both tables. I think you may already have what you want, but not know that.

    -PatP

    Its ok Pat, thank you anyways

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Problem has been discovered, Oh GOOD GRAVY THIS IS MESS

Posting Permissions

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