Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Copy 1 large sql 2008 table to another database on the same machine

Posted on 2013-06-03
4
303 Views
Last Modified: 2016-02-11
I have a table with about 30 million records that I need to move to another database on the same machine.  The table does have an Identity column.  I'm using TSQL and it is very slow  any suggestions?

The Code:

BEGIN DISTRIBUTED TRANSACTION

BEGIN TRY

      SET IDENTITY_INSERT [MasterWare_Archive].[dbo].[InputMaster_Archive] ON
      
    INSERT  INTO [MasterWare_Archive].[dbo].[InputMasterWare_Archive] ( ID
                         , Line
                         , FileDate
                         , ... )
            SELECT ID
                         , Line
                         , FileDate
                         , ...
            FROM
                [MasterWare].[dbo].[InputMasterWare_Archive]
            WHERE
                [DateInstalled] > 90;
               
      SET IDENTITY_INSERT [MasterWare_Archive].[dbo].[InputMaster_Archive] OFF

END TRY
BEGIN CATCH
    ROLLBACK ;
    PRINT 'Failed to copy' ;
    PRINT ERROR_MESSAGE() ;
    PRINT ERROR_LINE() ;
END CATCH


COMMIT ;
0
Comment
Question by:wdbates
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 50 total points
ID: 39217203
Your transaction log might be building up a lot...
you can insert into the table in batches...
The result of such inserts is a batch might fail but the previous batches will be commited to the database..

DECLARE @count BIGINT,@count_max BIGINT,@batch BIGINT
SET @Batch = 1000
select @count =  min(id) from [MasterWare].[dbo].[InputMasterWare_Archive]
select @count_max =  max(id) from [MasterWare].[dbo].[InputMasterWare_Archive]
while (@count < @count_max)
BEGIN
SET IDENTITY_INSERT [MasterWare_Archive].[dbo].[InputMaster_Archive] ON
INSERT  INTO [MasterWare_Archive].[dbo].[InputMasterWare_Archive] ( ID
                         , Line
                         , FileDate
                         , ... )
            SELECT ID
                         , Line
                         , FileDate
                         , ...
            FROM
                [MasterWare].[dbo].[InputMasterWare_Archive]
            WHERE [DateInstalled] > 90
            and ID >= @count AND ID<@count+@Batch
SET @count = @count+@Batch
      SET IDENTITY_INSERT [MasterWare_Archive].[dbo].[InputMaster_Archive] OFF
END

END TRY
BEGIN CATCH
    ROLLBACK ;
    PRINT 'Failed to copy' ;
    PRINT ERROR_MESSAGE() ;
    PRINT ERROR_LINE() ;
END CATCH

Open in new window

0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 50 total points
ID: 39219475
The best option in my opinion is SSIS or directly SQL Export/Import Wizzard if this is a one timer:

http://www.mssqltips.com/sqlservertutorial/202/simple-way-to-export-data-from-sql-server/
http://beyondrelational.com/modules/12/tutorials/24/tutorials/9011/getting-started-with-ssis-part-6-import-export-wizard-part-1.aspx



Or....use BCP OUT then copy the file to the other SQL box if needed otherwise just use BCP IN:

http://msdn.microsoft.com/en-us/library/aa337544(v=sql.90).aspx
0
 

Author Comment

by:wdbates
ID: 39220896
Hello All;

Sorry for the delay, but work been a bitch.  It is a weekly job, but the first time this runs will be a very large pull of data.  I like the SSIS approach, but how can I commit or rollback in SSIS of if the package fails.  This is critical data and I need to be sure that the records are loaded or if package fails the job is roll backed.

WB
0
 

Author Closing Comment

by:wdbates
ID: 39226690
Both answers were helpful and I will be able to use each in difference situations.

Thank you for your support.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

837 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