Tidier SQL code

[ConBran]

Hi there,

I have a DTS package that I am running to import data to a database, the flow it follows is as such:

1. Import the data to a staging area
2. Remove any invalid records (ones with invalid data) and move to another table for analysis
3. Remove any records that may already be in the table and move into another table for analysis
4. Import everything that is left in teh staging area to the destination table

I seem to be having a problem with the SQL code that I am using in stage 3 - I have had to use a basic sql string for this becuase the system is handled by a few people, and not everyone is good at sql (im an average user myself), this is then used to change the DTS Pump. Basically, the code I am using is:

SELECT * FROM Import where Brochure_Code IN(Select Brochure_Code from IDF) AND Market_ID in (Select Market_ID from IDF) AND Warehouse_ID in (Select Warehouse_ID from IDF) AND Year in (Select Year from IDF) AND Month in (Select Month from IDF) Order by Market_ID

(messy, I know). It 'seems' to work, and I use the term loosely, for some reason, it will remove some records that are already there, but it also seems to be removing records that aren't actually there (in the destination table [IDF]) and moves them too!!! I am perplexed as to why it is doing this.

Can anyone offer me a bit of SQL code that can be used to compare two records in two different tables to see if certain fields are the same - in this case, the fields are:

Brochure_code
Warehouse_ID
Year
Month
Market_ID

(these 5 fields make up the primary key and CANNOT be changed - too many other things rest upon them)

Feel free to rip apart the code, or ask more questions. Any and all help will be appreciated.

ConBran

 

[TheJon]

Where you should be using unique occurrences of the composite key, you are actually looking for permutations of the key fields which match your IMPORT table, even though they do not appear in the IDF table.

Imagine 2 tables, each with 2 fields which make up composite keys.

TableA tableB
key1 key2 key1 key2
1 a 1 a
1 b 1 c
2 c 2 a

I believe that your desired result is to see where records match on all keys between the tables, so in the above example you would expect to only flag the record with key1 = 1 and key2 = a.

Your code currently looks for records where key1 is in (select key1 from tableB) and key2 is in (select key2 from tableB).
"Select key1 from tableB" gives a record set of (1,2) and "Select key2 from tableB" gives a record set of (a,c). The permutations of these values are 1a, 1c, 2a, 2c, so your code will also flag the key1 = 2 and key2 = c entry as being in tableB when it isn't.

If you change the code to match the tables using the composite key (I always use inner join notation as it is more efficient):

select
  *
from
  tableA a
    inner join
  tableB b
      on a.key1 = b.key1
      and a.key2 = b.key2

you will only match the key1 = 1 and key2 = 1 record.

so, for your DTS package try:

SELECT 
  * 
FROM 
  Import 
    inner join
  idf
      on import.Brochure_Code = idf.Brochure_Code
      and import.Market_ID = idf.Market_ID
      and import.Warehouse_ID = idf.Warehouse_ID
      and import.Year = idf.Year
      amd import.month = idf.month
Order by 
  Market_ID

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]