March 3, 2011 at 7:54 pm
I have a central database with million images and I need to copy these images into a table in another db on the same server, so can someone help me in determining the efficient way of doing it. FYI there is no logic in this migration it is straight copy from db to another.
thanks
Shashi
March 3, 2011 at 11:04 pm
Have you scripted out the table yet with the data? I would start there to see what the DDL looks like.
March 4, 2011 at 12:20 pm
Hi , here is the script for those tables and I need an efficient way of copying 10 million image records from frDocument to frDocument_Copy table
Like should I use bulk copy, Insert into ...or can you suggest me any other faster way of doing it.
CREATE TABLE [dbo].[frDocument](
[DocumentID] [int] NOT NULL,
[DocumentVersion] [int] NOT NULL,
[DocumentName] [varchar](150) NOT NULL,
[Description] [varchar](250) NULL,
[Image] [image] NULL,
[PageCount] [int] NULL,
[DocumentVersionDate] [datetime] NOT NULL,
[ScannedDateTime] [datetime] NULL,
[RecordingUserName] [varchar](25) NOT NULL,
[RecordingTime] [datetime] NOT NULL,
[RowVersion] [int] NOT NULL,
CONSTRAINT [PK_frDocument] PRIMARY KEY CLUSTERED
(
[DocumentID] ASC,
[DocumentVersion] ASC
)
)
GO
CREATE TABLE [dbo].[frDocument_Copy](
[DocumentID] [int] NOT NULL,
[DocumentVersion] [int] NOT NULL,
[DocumentName] [varchar](150) NOT NULL,
[Description] [varchar](250) NULL,
[Image] [image] NULL,
[PageCount] [int] NULL,
[DocumentVersionDate] [datetime] NOT NULL,
[ScannedDateTime] [datetime] NULL,
[RecordingUserName] [varchar](25) NOT NULL,
[RecordingTime] [datetime] NOT NULL,
[RowVersion] [int] NOT NULL,
CONSTRAINT [PK_frDocument_Copy] PRIMARY KEY CLUSTERED
(
[DocumentID] ASC,
[DocumentVersion] ASC
)
)
GO
March 4, 2011 at 12:34 pm
With SSIS, you generally do not want to do updates inside of the dataflow, but one well accepted technique is to create an additional table in the same database of the destination table to stage the rows to be updated/inserted into. This table is loaded inside of the data flow (destination component) so you would need to add some logic to determine which rows have to be updated/inserted. Once the 'update' table is loaded you can use an execute sql task in the control flow to do a 1 time update.
March 4, 2011 at 1:11 pm
i might consider doing it this way...
migrate all the data except the blob/images themselves, and then migrate the images 1000 at a time so i can see progress and have re-startability if i want to stop the process;
something like this :
--insert all million + rows, except for the blobs.
INSERT INTO [dbo].[frDocument_Copy](DocumentID,DocumentVersion,DocumentName,Description,PageCount,DocumentVersionDate,ScannedDateTime,RecordingUserName,RecordingTime)
SELECT DocumentID,DocumentVersion,DocumentName,Description,PageCount,DocumentVersionDate,ScannedDateTime,RecordingUserName,RecordingTime
FROM [dbo].[frDocument]
----due to bandwith / time considerations, update in batches based on ROWCOUNT
SET ROWCOUNT 1000
DECLARE @i INT,
@j-2 INT
SELECT @j-2=1,@i = COUNT(1) FROM [dbo].[frDocument_Copy]
WHILE 1 = 1 --yeah, i know
BEGIN
IF NOT EXISTS(SELECT 1
FROM [dbo].[frDocument_Copy] Dest
INNER JOIN [dbo].[frDocument] Sourc
ON Dest.[DocumentID] = Sourc.[DocumentID]
AND Dest.[DocumentVersion] = Sourc.[DocumentVersion]
WHERE Dest.[Image] IS NULL
AND Sourc.[Image] IS NOT NULL
)
BREAK; --bail out of the endless loop!
PRINT 'Iteration ' + CONVERT(VARCHAR(30),@j) + ' : ' + CONVERT(VARCHAR(30),@j * 1000) + ' Out Of ' + CONVERT(VARCHAR(30),@i )
--still here? do another update.
UPDATE Dest
SET Dest.[Image] = Sourc.[Image]
FROM [dbo].[frDocument_Copy] Dest
INNER JOIN [dbo].[frDocument] Sourc
ON Dest.[DocumentID] = Sourc.[DocumentID]
AND Dest.[DocumentVersion] = Sourc.[DocumentVersion]
WHERE Dest.[Image] IS NULL
AND Sourc.[Image] IS NOT NULL;
--another bailout featue: no rows processed.
If @@ROWCOUNT = 0 BREAK;
END
SET ROWCOUNT 0
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply