Import data through flat files to database, need suggestion

Posted on 2011-10-12
Medium Priority
Last Modified: 2012-05-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?

Best Regards,
Question by:MohitPandit
  • 3
LVL 13

Accepted Solution

sameer2010 earned 1400 total points
ID: 36960225
Hi Mohit,

You can use GTT as well. They would eat up a lot of temp memory, but it could be a quick and dirty method to do what you need to do. Permanent tables would be a better option since you may not have table changes going on that frequently.

Alternatively, why don;t you setup the linked servers and setup up jobs to sync both the tables?


Author Comment

ID: 36960268
It means physical temp tables are better as compare to global temp tables as they will eat up lot of temp memory.

Regarding linked servers, we need to follow flat files export/import option only using SSIS.

Can you please bifurcate advantage and disadvantages between GTT VS. physical temp tables? it would be great.

Assisted Solution

DrewKjell earned 600 total points
ID: 36962184
Are your database servers on the same network?  If they are a solution that has worked for me in the past to transfer large amounts (Read millions of rows) of data from a Data Warehouse to a local SQL Server was to create a linked server.  This cuts out the need for flat files and copying which I was a big fan of.  You could still have the staging tables, or you could look at modifying your update to use the Linked Server tables instead of the 'staging' tables which should be able to accomplish the same thing.


Author Comment

ID: 36966168
No, it can be on different network. This is the main reason; we followed flat files.

Author Closing Comment

ID: 37087533
Thanks, I used physical staging tables for that.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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