| Author |
Topic |
|
tommypeters
Starting Member
5 Posts |
 Posted - 2008-12-17 : 07:38:17
|
| I have done some research in different forums to refresh my SQL abilities and found general statements like "Don't use cursors", "Don't loop, use SET" and so on - but I need to ask this question anyway...Situation:A system will be replaced by another, and data has to be migrated from the old system to the new. There's no possibility simultaneously reading from the old database and writing to the new - different countries, safety reasons... So, the old data will be exported to files and imported into an "Intermediate Migration Database" with some "Data Cleaning" measures taken meanwhile. Then data should be "copied" from the IMD to the new system's database. (I write "copied" because it's not a case of copying data from one table to one identical/similar table - the systems are not similar).* We're talking SQL Server 2005* Both the old and new systems are now running, new products are sold by the new system and the rest by the old. But the same customers can exist in both systems.* Speed is essential. Both systems will be taken down for a weekend and all data from the old system should be in the new system before the weekend is over. And there's lots of customers, products, transactions...* Most work should be done in stored procedures. If needed, controlled by a C# program.Question:We have a Customer table in the intermediate database containing data, including address, about the customer. Almost 100,000 customers.In the new system there's a Customer, an Address and a CustomerAddress table that together can hold the same data.The new system already contains a few thousand customers, most of those also exist in the old database.How is this handles most effectively? If the customer already exists it should be updated (with some combination of data from both systems), otherwise the customer should be inserted.As I mentioned, I've read that "Cursors are the worst", "while loops aren't that performance-kosher either"... But what to use?(Since there will be only one concurrent user during migration, we have also thought about the "things around" like removing/rebuilding indexes, SET NOLOCK ON/SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED... - more tricks?) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-17 : 07:50:03
|
| seems like what you need is to design a package in SSIS to get data from old db, perform cleansing and then loading into new db. |
 |
|
|
tommypeters
Starting Member
5 Posts |
Posted - 2008-12-17 : 08:04:58
|
| Haven't used SSIS. Is the Import/Export Wizard easier to use than to write your own stored procedure, faster to execute, or both? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-12-17 : 08:13:59
|
Assuming, the customer in old and new system have the same customer-number.Do your work in two steps please because it is clean and easy.First step: update customer_data where exists (select * from new_table where where old_cust = new_cust)Second step: insert customer_data where not exists (select * from new_table where where old_cust = new_cust)GreetingsWebfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-12-17 : 08:16:09
|
And talking about 100,000 customers - the performance is not a problem for sql server 2005.Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-17 : 08:24:03
|
quote:
Originally posted by tommypeters
Haven't used SSIS. Is the Import/Export Wizard easier to use than to write your own stored procedure, faster to execute, or both?
for customised data cleansing logic,you need to use SSIS package itself. export/import wizard gives you only limited facility to write the intermediate logic |
|
|
|
tommypeters
Starting Member
5 Posts |
Posted - 2008-12-17 : 08:48:48
|
| Thanks for your answers. "Customer" was the easiest case to explain, and the easiest to execute - there's a lot of other things to do during the weekend. I don't have all numbers off-hand, but the previous migration from another system had 50,000 customers - but 8 million transactions and large numbers for other tables. And it's everything that has to be done in two days - export, data transfer, primary data cleaning (by people from the old system), import to intermediate DB with secondary cleaning (by us), move data into new system... And we need some margin for correcting eventual errors and for a final bail-out/DB restore... ;)As it happens, this Customer example will be as easy as Webfred's two-step answer - hardly any secondary cleaning should be necessary. Other things will be much more complicated and may very well call for SSIS, will have a look at it. |
|
|
|
tommypeters
Starting Member
5 Posts |
Posted - 2008-12-18 : 06:23:53
|
quote:
Originally posted by visakh16
quote:
Originally posted by tommypeters
Haven't used SSIS. Is the Import/Export Wizard easier to use than to write your own stored procedure, faster to execute, or both?
for customised data cleansing logic,you need to use SSIS package itself. export/import wizard gives you only limited facility to write the intermediate logic
Just took a quick first look at SSIS and it seems like it can shorten the development time compared to writing everything as stand-alone C#/Stored procedures. And you say it will not make the execution take longer? |
|
|
|
|
Fast Insert - Update for Migration?