Solved

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

Posted on 2013-06-03
4
304 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
[X]
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
  • 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 40

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
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…

730 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