Must be overthinking this Insert/Update 1

[DugsDMan]

I have two data files that I need to merge into one table in SQL Server 2000. The layouts are below, * denotes key fields.

File 1 (Order):
State*
Type*
Cust #*
FileDate*
Customer Info
Account Type (B or D)*
Order (Y or N)

File 2 (Usage):
State*
Type*
Cust #*
FileDate*
Customer Info
Account Type (B or D)*

Final Table:
State*
Type*
Cust #*
FileDate*
Customer Info
TypeBOrder (Y or N)
TypeDOrder (Y or N)
TypeBUsage (Y or N)
TypeDUsage (Y or N)

The final table will exist perpetually (and hold a rolling 12 months worth of data). The 2 files will come in monthly. I plan to have seperate tables (identical layout to the files) to simply import the 2 data files each month, then run a stored proc to insert/update the main table with this new data, then delete the 2 tables.

Here's what I think I need to do:
[ul]
[li]Import Order table with Order data.[/li]
[li]Import Usage table with Usage data.[/li]
[li]Insert any key fields from the Order table that are not currently in the Main table.[/li]
[li]Update the TypeBOrder field on the main table where there is a record in the Order table with a matching key, Account Type = B and Order = Y[/li]
[li]Update the TypeDOrder field on the main table where there is a record in the Order table with a matching key, Account Type = D and Order = Y[/li]
[li]Update the TypeBUsage field on the main table where there is a record in the Usage table with a matching key and Account Type = B[/li]
[li]Update the TypeDUsage field on the main table where there is a record in the Usage table with a matching key and Account Type = D[/li]
[li]Delete Order table[/li]
[li]Delete Usage table[/li]
[li]Delete main table data > 12 months old[/li]
[/ul]
I can handle all the updating/deleting stuff easily enough. My problem comes in trying to insert all the Order items that are not currently in the table. I've tried Where Not Exists, Minus, etc. but can't seem to get it right. Am I just overlooking an easy solution to this?

I'm trying to avoid using a cursor and selecting the Order table, then checking the Main table to see if it's there...I didn't think that would be the efficient way to go about it.

Any ideas?

Thanks!!

 

[SQLSister]

ASsume a holding table and a main table. To get the records in the holding table but not in the main table

insert into main (idfield, field1, field20
Select h.idifeld, h.field1, h.fiedl2 from holding h left join
main m on h.idfield = m.idfield 
Where m.idfield is null

Questions about posting. See faq183-874

 

[SQLBill]

First, why TWO files? Everything in File 2 (Usage) exists in File 1 (Order). Just import table 1.

-SQLBill

 

[DugsDMan]

Thanks SQLSister I'll try that tomorrow when I get back in.

The reason there are two files is the Order table denotes when Orders are placed on an account, while the Usage table actually denotes Usage on that account. If there is not any usage for an account that month, it simply won't show up on teh report, thus there's not a Y/N field like on the Orders file.

 

[DugsDMan]

However there is not a single ID field. Is there a way for me to use simliar SQL using multiple fields to identify what is in there or not?

Thanks!

 

[SQLSister]

Sure just join on more than one field. Just make sure the join gives you a way to uniquely id the records.

on h.field1 = m.field1 and h.field2 = m.field2

Questions about posting. See faq183-874

 

[DugsDMan]

What I was meaning was how do I make sure that the key field"s" aren't already there. I had just been in here too long yesterday. Once I looked at it this morning, it was obvious...

where KeyField1 is null and KeyField2 is null ...

Thanks for the help! All seems to be working well now.