Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Struggling with inserting data

    Hello all

    I am trying to insert data from the Report table to the Revised_Maintable
    my problem is that I get an error message every time. I dont know what to do


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


    This is the Report 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 
    (



    and the 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 
    (
    Last edited by desireemm; 10-31-07 at 13:04.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by desireemm
    I dont know what to do
    Start with the error message, that's always a good idea in my book!

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your column orders don't look right.

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

    Oh sorry pat

    Error message

    Code:
    Msg 207, Level 16, State 1, Line 3
    Invalid column name 'IR Number'.

  5. #5
    Join Date
    May 2005
    Posts
    22
    There is [IR Number] columns in the reports table. that is the problem.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There isn't any column named [IR Number] in the Report table, so the WHERE clause in your NOT EXISTS is going to burp. You need to rethink what you want the SELECT to do, and my guess would be that you need to incorporate a second instance of the Revised_MainTable into your NOT EXISTS clause.

    -PatP

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

    ok

    I just want it to insert
    [I/RDocument], [Date], [TypeOfIncident], [Incident Report No] into the Revised_MainTable.

    Because all the data from the Report table is in there except for all the 2004 Incident Reports. I'm just trying to insert te rest of them, there's a total of 1681 but about 494 missing

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It helps to format the code so you can see problems

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

    Seems like the colums don't match up
    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.

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

    Bret!

    HI THERE

    How are you


    Ok you said that some of the colums dont match up, so I fixed that part.


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

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The "NOT EXISTS" doesn't sound right to me...
    But then again ti depends what the poster wants to achieve fom this insert
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CREATE PROCEDURE [InsertRevisedMain_Tbl]
    AS
    INSERT INTO dbo.Revised_MainTable
    ([IR Number], [Date], [I/RDocument], [Violation Type])
    SELECT [Incident Report No], [Date], [I/RDocument], TypeOfIncident
    FROM dbo.Report
    WHERE NOT EXISTS(SELECT * FROM dbo.Revised_MainTable
    WHERE [Incident Report No] = [IR Number])
    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

    Talking Brettttttttt!!!!!!!!!

    Thank You So Much It Works


    You Are So Awesome!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Thank You

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You do know why, right?
    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.

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

    Red face I Am Ashamed

    Quote Originally Posted by Brett Kaiser
    CREATE PROCEDURE [InsertRevisedMain_Tbl]
    AS
    INSERT INTO dbo.Revised_MainTable
    ([IR Number], [Date], [I/RDocument], [Violation Type])
    SELECT [Incident Report No], [Date], [I/RDocument], TypeOfIncident
    FROM dbo.Report
    WHERE NOT EXISTS(SELECT * FROM dbo.Revised_MainTable
    WHERE [Incident Report No] = [IR Number])

    Oh I'm such a GOOOOBBBBEERRR, WHAT AN IDOT.

    In the WHERE CLAUSE Not dbo.Report

    dbo.Revised_Maintable, FILTER ALL WRONG...(hang head in shame)

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

    oh man

    The more I analyze it, the I realize JUST HOW WRONG IT WAS
    Oh my goodness no wonder, My Where made absolutely no sense at all

Posting Permissions

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