Import data through flat files to database, need suggestion

Posted on 2011-10-12
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
    LVL 13

    Accepted Solution

    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?

    LVL 5

    Author Comment

    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.
    LVL 9

    Assisted Solution

    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.

    LVL 5

    Author Comment

    No, it can be on different network. This is the main reason; we followed flat files.
    LVL 5

    Author Closing Comment

    Thanks, I used physical staging tables for that.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now