Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Insert Failing

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

    Unanswered: Insert Failing

    Hello I have two tables that have the same data in them but not all the data is in the new table. the old one has 397 more records then the new one and I need to insert that data in the new table but it keeps giving me a primary key violation rule.

    Code:
    SELECT     dbo.Revised_MainTable.[IR Number], dbo.Report.[Incident Report No], dbo.Report.Date, dbo.Report.[I/RDocument], dbo.Report.TypeOfIncident
    FROM         dbo.Revised_MainTable RIGHT OUTER JOIN
                          dbo.Report ON dbo.Revised_MainTable.[IR Number] = dbo.Report.[Incident Report No]
    WHERE     (dbo.Revised_MainTable.[IR Number] IS NULL)
    Can anyone help me please???

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's the name of the table you're inserting into?

    What code did you use to find the reows that are missing?
    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
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Hi Brett

    The name of the table is Revised_MainTable

    Code:
    INSERT INTO dbo.Revised_MainTable
                          ([IR Number], Date, [I/RDocument], [Violation Type])
    SELECT     [Incident Report No], Date, [I/RDocument], TypeOfIncident
    FROM         dbo.Report

  4. #4
    Join Date
    May 2007
    Posts
    23
    Insert into dbo.Revised_MainTable
    Select * From dbo.Report
    Where [IR Number] NOT IN (Select [IR Number] From dbo.Revised_MainTable)
    Last edited by poison; 07-11-07 at 12:41.

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

    Help please

    I keep getting an error message

    Msg 213, Level 16, State 1, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.



    Code:
    Insert into dbo.Revised_MainTable
    Select * From dbo.Report
    Where [Incident Report No] NOT IN (Select [IR Number] From dbo.Revised_MainTable)

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Which is why you should always supply a column list AND never, ever use SELECT * in this situatin

    INSERT INTO Table(Col1, Col2, Col3)
    SELECT Col1, Col2, Col3 FROM AnotherTable
    WHERE [ID] NOT IN (SELECT [ID] FROM Table)

    Personally I prefer

    INSERT INTO Table(Col1, Col2, Col3)
    SELECT Col1, Col2, Col3 FROM AnotherTable a
    WHERE NOT EXISTS(SELECT * FROM Table b WHERE a.[ID] = b[ID])

    I think the second will be more effecient, the first I think cause a scan
    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.

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

    Talking Yes Sir

    Mr. Guru Brett, I will do that right away

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

    ehehh

    How are you Brett, hows life going??

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by desireemm
    How are you Brett, hows life going??

    I thought you are going to ask me why I used SELECT * in the subquery...it's one of the few times that's actually better now that M$ actually coded SQLServer to be optimzed for it

    As for me, you should head back over to the Corral one of these days...

    Anyway, all good here

    Good Luck
    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.

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

    Talking Ok Brett

    is this correct

    Code:
    INSERT INTO Revised_MainTable([IR Number], Date, I/RDocument, [Violation Type] )
    SELECT Date, I/RDocument, TypeOfIncident, [IR Number] FROM dbo.Report 
    WHERE NOT EXISTS(SELECT * FROM Table dbo.Report WHERE [IR Number] = [Incident Report No])

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No, I don't think so...you could do with taking a little extra time to format the code so you can "see" the problems immediatley. For example the column orders of the collist and the select list are in different orders.

    Plus you want to know where the rows don't exists in the table that you're insserting into...not reports

    So I changed it, but I don't know the key columns for Revised table, so you may have to change that...that's another good practice I'll point out is to label everything, even if the names are different and not ambiguous..because it's self documenting. If you did that, I would'nt even have to guess, I would just know

    Code:
    INSERT INTO Revised_MainTable(
    	    [I/RDocument], [Date], [Violation Type],  [IR Number])
         SELECT [I/RDocument], [Date], [TypeOfIncident],  [IR Number] 
           FROM dbo.Report a
          WHERE NOT EXISTS(SELECT * FROM Table dbo.Revised_MainTable b
    			WHERE b.[IR Number] = a.[Incident Report No])
    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.

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

    I will be leaving soon

    But I wanted to post the script behind each table

    Code:
    [dbo].[Report](
    	[Incident Report No] [nvarchar](100) NOT NULL,
    	[Date] [datetime] NULL,
    	[Time] [datetime] NULL,
    	[Investigators Name] [nvarchar](100) NULL,
    	[Reported by] [nvarchar](100) NULL,
    	[Law Enforcement Agency] [nvarchar](100) NULL,
    	[Name of Officer] [nvarchar](100) NULL,
    	[Evidence Seized] [nvarchar](100) NULL,
    	[Associated Reports] [nvarchar](50) NULL,
    	[Corrective Action] [nvarchar](50) NULL CONSTRAINT [DF_Report_Corrective Action]  DEFAULT (N'YES'),
    	[Comments] [ntext] NULL,
    	[I/RDocument] [ntext] NULL CONSTRAINT [DF_Report_I/RDocument]  DEFAULT (N'SCANNED REPORT'),
    	[TypeOfIncident] [nvarchar](300) NULL,
    	[Exclusion] [nvarchar](50) NULL CONSTRAINT [DF_Report_Exclusion]  DEFAULT (N'NO'),
    	[86_D] [nvarchar](50) NULL CONSTRAINT [DF_Report_86_D]  DEFAULT (N'NO'),
    	[Loss] [money] NULL CONSTRAINT [DF_Report_Loss]  DEFAULT (0.0000),
    	[LossType] [nvarchar](50) NULL,
    	[Area] [nvarchar](75) NULL,
    	[Action/Incident] [nvarchar](50) NULL,
    	[Security/GC] [nvarchar](50) NULL CONSTRAINT [DF_Report_Security/GC]  DEFAULT (N'GC'),
     CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED












    Revised_MainTable

    Code:
    [dbo].[Revised_MainTable](
    	[I/RDocument] [ntext] NULL CONSTRAINT [DF_Revised_MainTable_I/RDocument]  DEFAULT (N'Scanned Report'),
    	[IR Number] [nvarchar](100) NOT NULL,
    	[Date] [datetime] NULL,
    	[Inspector] [nvarchar](50) NULL,
    	[Area] [nvarchar](50) NULL,
    	[Violation] [nvarchar](50) NULL,
    	[Violation Type] [nvarchar](100) NULL,
    	[Loss] [money] NULL CONSTRAINT [DF_Revised_MainTable_Loss]  DEFAULT (0.0000),
    	[Loss Type] [nvarchar](50) NULL,
    	[Employee] [ntext] NULL,
    	[Guest] [ntext] NULL,
    	[Action] [nvarchar](50) NULL,
    	[Action Type] [nvarchar](50) NULL,
    	[Notes] [ntext] NULL,
    	[Security/GC] [nvarchar](50) NULL CONSTRAINT [DF_Revised_MainTable_Security/GC]  DEFAULT (N'GC'),
     CONSTRAINT [PK_Revised_MainTable] PRIMARY KEY CLUSTERED

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    I thought you are going to ask me why I used SELECT * in the subquery...it's one of the few times that's actually better now that M$ actually coded SQLServer to be optimzed for it
    When did they code it for select * in an exists statement to be more efficient? Do you have a reference?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nigel (nr) at sqlteam told quite awhile ago...I'd have to dig to find a reference...but if nigel said, that's good enough for me

    And it makes sense as well...no more SELECT 1...I believe the optimizer know that no results are required, so it doesn't produce any, like in the old days
    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.

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    When did they code it for select * in an exists statement to be more efficient? Do you have a reference?
    i have read this multiple times. most recently in one of the inside sql 2K5 books. it is definitly in 2k5 and I am pretty sure it is in 2K.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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