Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best approach for Data transfer of large number of rows Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 12:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 166, Visits: 1,301
Hello,
we have our database in sql 2005 version Enterprise Edition .
Because of performance issue we have normalized database and now required data transfer from denormalized database to normalized database.all tables has been created.

Denormalized database have 429 tables, some of them have greater than 5 millions rows.
In this condition one table data may needs to be transferred in 3 different tables of normalized database.

which would be the best approach to do data transfer from one database to another
i.e script or ssis package..?

Thanks..
Post #1557833
Posted Thursday, April 3, 2014 1:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
If the databases are on the same server, you can use SQL scripts.
Make sure logging is minimized to reduce overhead.

What kind of performance issues were you having with the denormalized system?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1557847
Posted Thursday, April 3, 2014 2:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 166, Visits: 1,301
Actually, we have tables with more than 150 columns and it's harder to manage it (insert) .
so tables are denormalized now.

source and destination databases are on different server.

Thanks,
megha
Post #1557861
Posted Thursday, April 3, 2014 2:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
Megha P (4/3/2014)
Actually, we have tables with more than 150 columns and it's harder to manage it (insert) .
so tables are denormalized now.

source and destination databases are on different server.

Thanks,
megha


If they databases are on different servers, I would go for SSIS.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1557866
Posted Thursday, April 3, 2014 4:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 166, Visits: 1,301
ok...thanks..SSIS will be fast over script in case of different server data transfer?
Post #1557889
Posted Thursday, April 3, 2014 4:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061

In addition to Koen; you can also drop all constrains on the destination table if no. of rows are more which will expedite the process. Once SSIS is done you can creat them again.


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1557890
Posted Thursday, April 3, 2014 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
Megha P (4/3/2014)
ok...thanks..SSIS will be fast over script in case of different server data transfer?


Yes, because with scripts you would have to use linked servers. SSIS will be faster.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558069
Posted Friday, April 4, 2014 11:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 166, Visits: 1,301
Hi,
Is there any other advantages of using SSIS over using script , except faster loading for data transfer?

Thanks,
Megha
Post #1558697
Posted Saturday, April 5, 2014 2:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
Megha P (4/4/2014)
Hi,
Is there any other advantages of using SSIS over using script , except faster loading for data transfer?

Thanks,
Megha


You don't have to use linked servers.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558705
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse