wdbates
asked on
Copy 1 large sql 2008 table to another database on the same machine
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] .[InputMas ter_Archiv e] ON
INSERT INTO [MasterWare_Archive].[dbo] .[InputMas terWare_Ar chive] ( ID
, Line
, FileDate
, ... )
SELECT ID
, Line
, FileDate
, ...
FROM
[MasterWare].[dbo].[InputM asterWare_ Archive]
WHERE
[DateInstalled] > 90;
SET IDENTITY_INSERT [MasterWare_Archive].[dbo] .[InputMas ter_Archiv e] OFF
END TRY
BEGIN CATCH
ROLLBACK ;
PRINT 'Failed to copy' ;
PRINT ERROR_MESSAGE() ;
PRINT ERROR_LINE() ;
END CATCH
COMMIT ;
The Code:
BEGIN DISTRIBUTED TRANSACTION
BEGIN TRY
SET IDENTITY_INSERT [MasterWare_Archive].[dbo]
INSERT INTO [MasterWare_Archive].[dbo]
, Line
, FileDate
, ... )
SELECT ID
, Line
, FileDate
, ...
FROM
[MasterWare].[dbo].[InputM
WHERE
[DateInstalled] > 90;
SET IDENTITY_INSERT [MasterWare_Archive].[dbo]
END TRY
BEGIN CATCH
ROLLBACK ;
PRINT 'Failed to copy' ;
PRINT ERROR_MESSAGE() ;
PRINT ERROR_LINE() ;
END CATCH
COMMIT ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both answers were helpful and I will be able to use each in difference situations.
Thank you for your support.
Thank you for your support.
ASKER
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