February 15, 2011 at 1:47 pm
For a bit of background, I have been programming for 30 years. Scientific analysis in Fortran, games in Pascal, AI in Forth, data driven web sites in PHP to name a few projects.
I have recently become a DBA with responsibility for several medium size (5-10 million records) databases on SQL Server 2005 and 2008.
The other day I had written a utility to examine a file against the database for changed or missing records. One of the programmers looked at what I was doing and said I was going the long way around the barn.
His advice was to pick up the 50,000 records in the nightly extract with Access, turn off warnings, perform an update of every record and then perform an insert of everything and turn warnings back on. This will just try updating then inserting every record and the primary key constraints will keep duplicates from being created.
It struck me as throwing a bucket of dung at the wall (twice) and seeing what sticks. He said this is how they "always do it."
I ask your opinion. Is this a common and regularly accepted technique for updating an LDAP database table?
February 15, 2011 at 2:03 pm
Sounds like a good way to end up with a junk database to me.
Is this some sort of ETL method for getting data from files into tables? That's what I think I'm reading. If so, have you looked into SSIS for that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2011 at 2:51 pm
If you never have record edits, this is... one approach.
I've seen this method disseminated before as a quick and dirty way to perform that task.
My alternatives will depend on the data, but if you need a full compare between two usually equivalent tables, I'd use SSIS, load the primary key (only) into a lookup component, and then split the results depending on if they were found or not.
If you're in 2k8 I'd avoid that, and load a staging table, then use the MERGE statement. That'll do your updates and inserts in a single pass.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 4:11 pm
dlatch (2/15/2011)
It struck me as throwing a bucket of dung at the wall (twice) and seeing what sticks. He said this is how they "always do it."
'
BWAA-HAAA!!!! And now we know one of the reasons I do 100% code reviews. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2011 at 6:51 pm
GSquared and Craig -
It was actually an SSIS package that I had written. Doing a lookup against the key and column splits on the critical tested fields. I stored the streams into temporary tables and then either UPDATED or INSERTED the selected results into the master table.
It was after reviewing the package that the programmer said, that is too much work...
February 15, 2011 at 6:57 pm
dlatch (2/15/2011)
GSquared and Craig -It was actually an SSIS package that I had written. Doing a lookup against the key and column splits on the critical tested fields. I stored the streams into temporary tables and then either UPDATED or INSERTED the selected results into the master table.
It was after reviewing the package that the programmer said, that is too much work...
Lazy programming for the win? If it's already built correctly (as you've mentioned you've already done!), it can't be too much work. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2011 at 7:15 am
dlatch (2/15/2011)
GSquared and Craig -It was actually an SSIS package that I had written. Doing a lookup against the key and column splits on the critical tested fields. I stored the streams into temporary tables and then either UPDATED or INSERTED the selected results into the master table.
It was after reviewing the package that the programmer said, that is too much work...
Sounds like good, standard, documentable, extensible, work.
Tell the dev that "coding is always 'pay now or pay later with interest'", and you'd rather do a little more work now in order to avoid a lot of work later.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply