March 7, 2008 at 4:32 am
Hello everyone,
I have a table called dbo.ECAF
In it are the following
PERSON_IDvarchar
START_DATEdatetime
END_DATEdatetime
TYPEvarchar
UPNvarchar
And I have another table dbo.ECAF_IDEAR with the same fields. This table already has PERSON_IDs added. Im wanting to
write an SP that looks at the new file every week and UPDATE's
dbo.ECAF_IDEAR.start_date and dbo.ECAF_IDEAR.end_date to dbo.ECAF_IDEAR start and end dates
WHERE PersonID = PersonID
AND dbo.ECAF_IDEAR.StartDate is NULL AND dbo.ECAF_IDEAR.EndDate IS NULL
I was trying to run with a WHILE loop but got fairly stuck on the syntax, has anyone got any advice on this?
Or am I completely wrong :doze:
Im wanting to get it set up so ever night it checks one file and updates the other one.
Thanks in advance
Debbie
March 7, 2008 at 4:57 am
Hi Debbie
Your first step for this UPDATE should be a SELECT which returns the rows containing the data which you want to use as your reference, i.e. which will overwrite the contents of the other table, and should include the column(s) which uniquely reference each row in each table.
Also, are you sure that you're dealing with UPDATEs alone? Will there be INSERTs too?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 7, 2008 at 5:03 am
No inserts are required. The data should only be updated where a PER_ID exists in the table
March 7, 2008 at 5:08 am
Good, that makes things quite a bit simpler. Now, can you write a SELECT query from your source table which contains the rows and columns which you are using to update the other table?
Incidentally, there are as always several ways of doing this, we'll get to them later.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 7, 2008 at 5:14 am
OK so
SELECT PERSON_ID, START_DATE , END_DATE, Type
FROM dbo.ECAF
I think I have a type to send that field too. I can add some criteria later about only grabbing data thats been modified after the last import
March 7, 2008 at 5:15 am
I'm guessing it will look something like this...
SELECT a.PersonID, a.start_date, a.end_date
FROM dbo.ECAF a
INNER JOIN dbo.ECAF_IDEAR b
ON b.PersonID = a.PersonID AND a.start_date IS NULL AND b.end_date IS NULL
WHERE a.start_date IS NOT NULL AND a.end_date IS NOT NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 7, 2008 at 5:17 am
I see so before you do the update you join both tables using the PER_ID which is what is going to be used to do the update on!
March 7, 2008 at 5:22 am
Absolutely Debbie! So we build the UPDATE in two steps; the first step, which is a simple SELECT, is easy to test - you have output rows to look at and count.
How's it looking so far? Once you've finished tweaking the SELECT, we can do the second step and change it to an UPDATE and Bob's your uncle.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 7, 2008 at 5:29 am
I see so I dont really need to use WHILE loops.
I will just go into the tables properly and get it working and Ill let you know when Im sorted!
Thanks 🙂
March 7, 2008 at 5:36 am
Yep. It's called an UPDATE ... FROM and will look something like this when you've finished...
UPDATE b SET b.start_date = a.start_date, b.end_date = a.end_date
FROM dbo.ECAF a
INNER JOIN dbo.ECAF_IDEAR b
ON b.PersonID = a.PersonID AND b.start_date IS NULL AND b.end_date IS NULL
WHERE a.start_date IS NOT NULL AND a.end_date IS NOT NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 7, 2008 at 5:54 am
Its never as easy as you think it is when you really start working on it 🙂 Ive realised I have 2 situations
/* This will find completely new information for pupils so we are
going to INSERT INTO tblPUPIL_CAF based on the information below*/
SELECT ecaf.PERSON_ID, ecaf.START_DATE, ecaf.END_DATE, idear.CAF_ID, idear.CAF_STARTDATE, idear.CAF_ENDDATE
FROM dbo.ECAF ecaf --The table I want to import details from
INNER JOIN [D-DB01].EDUC_IDR_IDEAR.dbo.tblPUPIL pup -- The pupil table that contains every pupil
ON pup.PER_ID = ecaf.PERSON_ID
LEFT OUTER JOIN [D-DB01].EDUC_IDR_IDEAR.dbo.tblPUPIL_CAF idear --The CAF table that links to the pupil table
ON pup.PER_ID = idear.tblPUPIL
WHERE ecaf.TYPE = 'ECAFSTD' -- The Standard initial ECAF details
AND idear.tblPUPIL IS NULL
/* We have already got information in the ECAF table but we want to
UPDATE anything that is not different to what we already have*/
SELECT ecaf.PERSON_ID, ecaf.START_DATE, ecaf.END_DATE, idear.CAF_ID, idear.CAF_STARTDATE, idear.CAF_ENDDATE
FROM dbo.ECAF ecaf --The table I want to import details from
INNER JOIN [D-DB01].EDUC_IDR_IDEAR.dbo.tblPUPIL pup -- The pupil table that contains every pupil
ON pup.PER_ID = ecaf.PERSON_ID
INNER JOIN [D-DB01].EDUC_IDR_IDEAR.dbo.tblPUPIL_CAF idear --The CAF table that links to the pupil table
ON pup.PER_ID = idear.tblPUPIL
WHERE ecaf.TYPE = 'ECAFSTD'
AND ecaf.START_DATE <> idear.CAF_STARTDATE
OR ecaf.TYPE = 'ECAFSTD'
AND ecaf.END_DATE <> idear.CAF_ENDDATE
But I think using this as a starting point I can create SP's that first do an insert on the main table and then update anything else. Thats for your help on this!
March 7, 2008 at 7:46 am
It's looking good! And thanks for the feedback 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 7, 2008 at 7:50 am
No problem.
Ive got to wait till after 5 to start testing it properly but when it works Ill create it as a stored procedure and pass it across to the server team to get set up as a job.
Ill let you know how I get on.
Thanks again
Debbie
March 7, 2008 at 9:38 am
I'd really appreciate that, thanks Debbie.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 14, 2008 at 5:07 am
Hi Debbie
If you haven't already seen today's article by Jeff Moden about tuning UPDATES, then it's worth a read. There's a 'gotcha' in the code I posted earlier, which should instead be written like this:
UPDATE i SET i.start_date = a.start_date, i.end_date = a.end_date
FROM dbo.ECAF_IDEAR i
INNER JOIN dbo.ECAF a
ON a.PersonID = i.PersonID
AND a.start_date IS NOT NULL
AND a.end_date IS NOT NULL
WHERE i.start_date IS NULL
AND i.end_date IS NULL
The difference between this and the previous version is that the target table for the update is referenced in the FROM rather than in a JOIN. Jeff's article points out the potential problem with amusing anecdotal evidence.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply