Import data through flat files to database, need suggestion
Posted on 2011-10-12
I need to transfer data from one database to another database using SSIS on daily basis.
So, I am doing below steps:
1. Extract data in different flat files from source end.
2. Now, those flat files need to import into destination end.
While importing, I need to check either record is new/update based on IDENTITY value at destination end and also need to execute complex T-SQL for update one table after join with other tables.
So, I am planning for Import as below:
1. First, I will import data in staging tables (i.e. temp physical tables);
2. After that I can execute a stored procedure to making JOINs with staging tables and actual tables and can perform INSERT/UPDATE operation accordingly.
BUT, using aforesaid approach, I need to create staging tables (temp physical tables) for each tables respectively. For e.g., I need to import 50 tables on daily basis through flat files then I need to create 50 staging tables. SO, it will increase tables at destination end.
Of Course, before daily import, I'll TRUNCATE data from staging tables first then INSERT.
FYI, I need to import average 2 millions records for all tables on daily basis.
1. Can I use Global Temporary tables for those?
--> If I will use ## tables then it need not to create physical temp tables on database.
1. Can you please give advantages and disadvantages for both approach i.e. Physical temp tables VS. Global temp tables (i.e. ##tmp1)?
2. Do you have other alternate?