Solved

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

Posted on 2013-06-03
4
299 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Both answers were helpful and I will be able to use each in difference situations.

Thank you for your support.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

12 Experts available now in Live!

Get 1:1 Help Now