Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

using IF Then, or for loop

Hello, I have a table that has the rows:

AccountID    OtherReInfo      CreatedDate      ModifiedDate


It's created by an insert statement. It has records on it, based on the AccountID.

What i want to know is how do to this:

Insert each new record from an insert into statement, but check:

1. if the record does not exist, and if it doesn't insert it and update the createdDate with getDate
2. if the record does exist, check the otherReInfo column, and see if it's different, and if it is, then update that row, and also the last modified date to getDate()
3. if the record is the same, then do nothing, and move to the next row,

Thanks!
0
Auerelio Vasquez
Asked:
Auerelio Vasquez
  • 6
  • 3
1 Solution
 
JoeNuvoCommented:
your data to be insert/update
it come in form of table which exists on database
or you going to call it one by one from application?
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
no, it exists already in a table/view(i'll probably pull it from a view)
0
 
JoeNuvoCommented:
if you are using SQL 2008,
there is a new feature called MERGE

code should be something like following
MERGE INTO MyTable Target
USING (SELECT  AccountID, OtherReInfo FROM View WHERE condition) SRC
ON Target.AccountID = SRC.AccountID

WHERE MATCHED AND (Target.OtherReInfo  <> SRC.OtherReInfo) THEN
     UPDATE SET OtherReInfo = SRC.OtherReInfo, ModifiedDate = GETDATE()
WHEN NOT MATCHED THEN
     INSERT (AccountID, OtherReInfo, CreateDate)  VALUES (SRC.AccountID, SRC.OtherReInfo, GETDATE())

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Auerelio VasquezETL DeveloperAuthor Commented:
dang. for this one we are not using sql server 2008.... IS there a similar function in 2005 ?
0
 
JoeNuvoCommented:
you may found some example perform UPSERT with EXISTS/NOT EXISTS

anyway, below is way I like to do.

Declare @updatedid TABLE (AccountID int primary key)
--update
UPDATE MyTable
SET ModifiedDate = CASE WHEN MyTable.OtherReInfo = MyView.OtherReInfo THEN ModifiedDate ELSE GETDATE() END,
	OtherReInfo = MyView.OtherReInfo
OUTPUT DELETED.AccountID INTO @updatedid
FROM MyView
WHERE MyTable.AccountID = MyView.AccountID
--insert
INSERT INTO MyTable (AccountID, OtherReInfo, CreatedDate)
SELECT AccountID, OtherReInfo, GETDATE()
FROM MyView
WHERE AccountID NOT IN (SELECT AccountID FROM @updateid)

Open in new window

0
 
Auerelio VasquezETL DeveloperAuthor Commented:
ok, i've used this code below:

it's going into some kind of loop, becuse it never returns any results, it just keeps running.

mm_prod_codes2 = view where data comes from
mm_other_info is where i want to compare, and use the query below.

What i don't understand is the line about:OUTPUT DELETED.AccountID INTO @updateid

what is DELETED? is that rows that have been deleted? is my query wrong? it could take long, maybe because it's going to run thru that view twice. which takes about 1.5 minutes each time it's executed.

Declare @updateid TABLE (AccountID int primary key)
--update
UPDATE MM_Other_Info
SET LastModified = CASE WHEN mm_prod_codes2.OtherReIn = MM_Other_Info.OtherReIn THEN  GETDATE() END,
      MM_Other_Info.OtherReIn = mm_prod_codes2.OtherReIn
OUTPUT DELETED.AccountID INTO @updateid
FROM mm_prod_codes2
WHERE mm_prod_codes2.AccountID = MM_Other_Info.AccountID
--insert
INSERT INTO MM_Other_Info (AccountID, OtherReIn, CreatedDate)
SELECT AccountID, OtherReIn, GETDATE()
FROM mm_prod_codes2
WHERE AccountID NOT IN (SELECT AccountID FROM @updateid)

0
 
Auerelio VasquezETL DeveloperAuthor Commented:
What's confusing to me, is i only want to update the table, if the OtherReIn column is different on incoming from mm_prod_codes2 based on the account id. I don't where it's taking that fact into account.

this part i don't understand

SET LastModified = CASE WHEN mm_prod_codes2.OtherReIn = MM_Other_Info.OtherReIn THEN  GETDATE() END,
      MM_Other_Info.OtherReIn = mm_prod_codes2.OtherReIn
OUTPUT DELETED.AccountID INTO @updateid
FROM mm_prod_codes2
WHERE mm_prod_codes2.AccountID = MM_Other_Info.AccountID

should the where clause be an inequality clause ?

WHERE mm_prod_codes2.AccountID <> MM_Other_Info.AccountID

I think we are almost there. Right now, when it runs, it takes forever, more than 15 minutes, which could be ok, i'm going to play with the indexing. but, when it does run, it never updates where otherReIn on MM_Other_Info is different than mm_Prod_codes2. i set all the values of otherReIn to Null, and when this ran, it never updated that column.....
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
Ok, i was able to dump the view into a temp table for processing, and that took the processing time from 20 minutes, to 4 minutes. I just drop that temp table every time this is run.
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
ok, one thing about this, it updates everytime as such...... I just want where it's changed, that's the last bit of this. just need that one more bit of help, to make it work properly.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now