Insert/Update Query 1

[arodri]

I have a query that's pulling data from a linked server into a temp table (#SWTable). Here is a sample of the data in that table:
It's being ordered by Code, Date

Code Cust Date Plan
123 111 4/27/2011 C25
456 222 4/19/2011 LMN
789 333 4/19/2011 LMN
789 333 4/20/2011 LMN
011 444 4/25/2011 C25
011 444 4/26/2011 LMN

I need to insert that data into a table I have created: PACust

The part I'm having trouble with is my insert statement, because the PACust table cannot have duplicate Cust. The logic that I need to create the insert statement is:
Insert Code, Cust, Date, Plan INTO PACust
From the #SWTable only if that Cust is not already in the PACust Table.
So for example (from my sample data) It would insert:

123 111 4/27/2011 C25
456 222 4/19/2011 LMN
789 333 4/19/2011 LMN
011 444 4/25/2011 C25

Then what I need to do after only ONE of each Cust record is inserted, is run an update statement so that it updated the Code, Date, and Plan ( I need the most recent Plan, Code, and Date in the final PACust table ).

Here is the insert statement that I have now, but it's inserting ALL of the rows (including duplicates), so I need it to do what I described above. Any help would really be appreciated!

Insert INTO PACust (Code, Cust, Date, Plan)
SELECT A.Code, A.Cust, A.Date, A.Plan
FROM #SWTable A
WHERE NOT EXISTS
(SELECT CustOBJID
FROM PACust
WHERE PACust.Cust = A.Cust)

 

[lameid]

Insert INTO PACust  (Code, Cust, Date, Plan)    
SELECT A.Code, A.Cust, A.Date, A.Plan      
FROM  #SWTable A     
WHERE A.Cust NOT IN 
     (Select Cust
     From PACust)

OR

Insert INTO PACust  (Code, Cust, Date, Plan)    
SELECT A.Code, A.Cust, A.Date, A.Plan      
FROM  #SWTable A
    Left Join PACust
       ON PACust.Cust = A.Cust
WHERE PACust.Cust Is null

 

[arodri]

Hello,

I tried these two queries but both still do the same thing - they insert every record instead of just one CustOBJID.

 

[gmmastros]

The data from your temp table has duplicates, so the code for checking the PACust table allows the insert, but it's inserting twice. For example, your temp table has:

[tt][blue]
011 444 4/25/2011 C25
011 444 4/26/2011 LMN
[/blue][/tt]

By the time you are done with this process, what should be in your PACust table? The one with the most recent date?

If so you could try:

Insert INTO PACust(Code, Cust, [Date], [Plan]) 
Select #SWTable.Code, #SWTable.Cust, #SWTable.[Date], #SWTable.[Plan]
From   #SWTable
       Inner Join (
         Select Cust, Max(Date) As MaxDate
         From   #SWTable
         Group BY Cust
         ) As MostRecent
         On  #SWTable.Cust = MostRecent.Cust
         And #SWTable.Date = MostRecent.MaxDate
       Left Join PACust
         On PACust.Cust = #SWTable.Cust
Where  PACust.Cust Is NULL

Update PACust
Set    PACust.Code = #SWTable.Code,
       PACust.[Date] = #SWTable.[Date],
       PACust.[Plan] = #SWTable.[Plan]
From   PACust
       Inner Join #SWTable
         On PACust.Cust = #SWTable.Cust
       Inner Join (
         Select Cust, Max(Date) As MaxDate
         From   #SWTable
         Group BY Cust
         ) As MostRecent
         On  #SWTable.Cust = MostRecent.Cust
         And #SWTable.Date = MostRecent.MaxDate

*** Not tested, but should be ok.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh. I should mention... this code may still try to insert duplicates if there are multiple rows with the same cust and date AND that date is the max date for the cust. If this is permissible in your data (coming from the linked server), then you will need to investigate another method.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[arodri]

I'm still in the middle of testing, but your query definitely didn't insert any duplicates.
There shouldn't be multiple rows with the same cust and max date for the cust, so this shouldn't be a problem. Thanks for warning though!

Also, would you be able to tell me why the previous insert statement didn't work:

Insert INTO PACust (Code, Cust, Date, Plan)
SELECT A.Code, A.Cust, A.Date, A.Plan
FROM #SWTable A
WHERE A.Cust NOT IN
(Select Cust
From PACust)

Is it not inserting row by row?
And is there a way to insert the data the way I was originally planning (oldest CustOBJID record first, then use an UPDATE statement to update the data to the most recent one).

Thanks!

 

[gmmastros]

Also, would you be able to tell me why the previous insert statement didn't work:

Look at it this way... suppose your temp table had 2 (or more rows) with cust # 123, but that customer was not in your PACust table. The NOT IN check would return true for that customer, and then the inserts begin. Basically, it's checking all of the customers in your temp table against all the customers in your PACust table and deciding which ones to insert. Once that decision is made, all the data gets inserted.

Is it not inserting row by row?

No. It's not inserting row by row, and that's a good thing. Row by row things in SQL Server are considerably slower than set based operations.

And is there a way to insert the data the way I was originally planning (oldest CustOBJID record first, then use an UPDATE statement to update the data to the most recent one).

Of course there is a way to do this. In fact, as long as it's just data, there's a way to do just about anything you want. You could take a row by row approach (think slow) or you could start throwing more set based code at it. Either way, it can be done.

I gotta ask, though... Why bother inserting data just so you can update it with newer data immediately afterwards?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[arodri]

That makes a lot of sense! Thanks so much for explaining that.
That’s a good point – I’m not planning to change it so I insert it row by row or by inserting the old data first then updating it. When we were first talking about how to re-design this job (this query is part of the job) the first thought was inserting all of the old data then updating it – that’s why I asked if there was a way to do that. But the query you gave me makes much more sense than doing it the other way, so I think I’ll stick to that.
Thanks again for all of your help!

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom