locked
Inserting data into a table with a Combination Primary Key RRS feed

  • Question

  • I have a work flow database.  Each day we receive data from a servicing platform (AS-400) in text-delimited file format.  Currently we use an Access front end to import this data, but I want to create an automated import process that does not require any involvement by an end user.  I am starting with the actual importing process.  

    Here is the flow of the insert process:  Import delimited file into existing import table (ImportTable) that is always empty for the new daily import.  Several queries are run to set up the data correctly, some update queries.  Then the data is split into two different existing tables that hold previously existing data.  The first table, I will call it table_1, has a primary key established based on four fields.  This table is designed to allow only a single occurrence of each customer/report date/store/group record in the table.  The second insert move the details for all records into "table_2".  Inserting all of the data each day into table_2 works every time, but the Table_1 insert doesn't move any data.  Below is my query that parses, but just doesn't move any data.  In my test data, this query should move 39 records and ignore 26 duplicates.

    INSERT INTO dbo.ImportTable (CustID, ReportDate, WorkGrp, DueDate, NeedByDt, StoreName)
    SELECT CustID, ReportDate, WorkGrp, DueDate, NeedByDt, StoreName 
    FROM dbo.ImportTable
    WHERE ((ImportTable.CustID <> (SELECT CustID FROM dbo.Table_1)
    AND ImportTable.ReportDate <> (SELECT ReportDate FROM dbo.Table_1)
    AND ImportTable.WorkGrp <> (SELECT WorkGrp FROM dbo.Table_1)
    AND ImportTable.StoreName <> (SELECT StoreName FROM dbo.Table_1))
    AND (ImportTable.WorkType) Is Null)

    The primary key on Table_1 is a combination of: CustID, ReportDate, WorkGrp, and StoreName.  This prevents any customer from repeating in Table_1 for any one report date.  Like I said, the query parses fine and runs without errors, but does not move the 39 records that should move.  We started with "Not In", but changed that to "<>" later.  

    Any suggestions?
    Wednesday, October 14, 2009 5:29 PM

Answers

  • Your query seems to be wrong. For example your source and destionation is the same: ImportTable. If all you want is to import in Table_1 the records from ImportTable that do not already exist in Table_1 then you can use the following query. Please note that the query works in SQL 2005 & SQL 2008 but not in SQL 2000.

    INSERT INTO dbo.Table_1(CustID, ReportDate, WorkGrp, DueDate, NeedByDt, StoreName)

    SELECT CustID, ReportDate, WorkGrp, DueDate, NeedByDt, StoreName

    FROM dbo.ImportTable

    WHERE ImportTable.WorkType Is Null

    EXCEPT

    SELECT CustID, ReportDate, WorkGrp, StoreName

    FROM Table_1

    Hope it helps!


    Doru Sandor, MCDBA/MCSD
    • Edited by Doru Sandor Wednesday, October 14, 2009 6:33 PM typo
    • Marked as answer by Kalman Toth Monday, October 26, 2009 2:42 PM
    Wednesday, October 14, 2009 6:32 PM

All replies

  • Have you thought about SSIS?
    Abdallah, PMP, MCTS
    Wednesday, October 14, 2009 5:35 PM
  • Your query seems to be wrong. For example your source and destionation is the same: ImportTable. If all you want is to import in Table_1 the records from ImportTable that do not already exist in Table_1 then you can use the following query. Please note that the query works in SQL 2005 & SQL 2008 but not in SQL 2000.

    INSERT INTO dbo.Table_1(CustID, ReportDate, WorkGrp, DueDate, NeedByDt, StoreName)

    SELECT CustID, ReportDate, WorkGrp, DueDate, NeedByDt, StoreName

    FROM dbo.ImportTable

    WHERE ImportTable.WorkType Is Null

    EXCEPT

    SELECT CustID, ReportDate, WorkGrp, StoreName

    FROM Table_1

    Hope it helps!


    Doru Sandor, MCDBA/MCSD
    • Edited by Doru Sandor Wednesday, October 14, 2009 6:33 PM typo
    • Marked as answer by Kalman Toth Monday, October 26, 2009 2:42 PM
    Wednesday, October 14, 2009 6:32 PM