Solved

using IF Then, or for loop

Posted on 2011-02-20
9
290 Views
Last Modified: 2012-06-27
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
Comment
Question by:basile
  • 6
  • 3
9 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34940332
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
 
LVL 1

Author Comment

by:basile
ID: 34940399
no, it exists already in a table/view(i'll probably pull it from a view)
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34940598
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
 
LVL 1

Author Comment

by:basile
ID: 34940701
dang. for this one we are not using sql server 2008.... IS there a similar function in 2005 ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 34940794
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
 
LVL 1

Author Comment

by:basile
ID: 34945818
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
 
LVL 1

Author Comment

by:basile
ID: 34946093
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
 
LVL 1

Author Comment

by:basile
ID: 34946410
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
 
LVL 1

Author Comment

by:basile
ID: 34946439
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now