CODEWITHSUNDEEP
sqlsql-server
aleczandru
aleczandru

Reputation: 5449

How to migrate data to another table

Hi I am in need of a script that Updates the rows on a table from another table based on an ID and after that it deletes the from the second table the data it copied.

This are the tables I have

Documents
--------
DocumentID(PK)
RealFileName
FileName

ImageDocuments
--------------
ImageDocumentId(PK)
DocumentId(FK)
OriginalFileName
StorageFileName

Mapping beetween the two tables is One to One.

At the moment I have the script that updates the table but I do not know how to proceed to delete the values from documents table.This is what I have so far:

UPDATE [dbo].[ImageDocuments]
SET [dbo].[ImageDocuments].[OriginalFileName] = d.FileName,
    [dbo].[ImageDocuments].[StorageFileName] = d.RealName
FROM [dbo].[ImageDocuments] as fu, 
     [dbo].[Documents] as d
WHERE fu.DocumentID = d.DocumentID

How can I delete from the documents table the rows that were added to the ImageDocuments table?

UPDATE I create a delete query can anyone please validate if this will work corectly:

DELETE [dbo].[Documents]
FROM [dbo].[ImageDocuments] as fu
INNER JOIN [dbo].[Documents] as d ON d.DocumentID = fu.DocumentID

Upvotes: 0

Views: 69

Answers (3)

Ouscux
Ouscux

Reputation: 187

I would use a temporary table to store the matching records in both tables. Afterwards, by using that temporary table, you could delete the data those you already used in other table.

Such as;

DECLARE @Table TABLE (MatchingId INT)
INSERT INTO @Table 
SELECT DocumentID FROM [ImageDocuments] ImgDoc 
INNER JOIN [Documents] doc 
    ON ImgDoc.DocumentID = doc.DocumentID

UPDATE [dbo].[ImageDocuments]
SET [dbo].[ImageDocuments].[OriginalFileName] = d.FileName,
    [dbo].[ImageDocuments].[StorageFileName] = d.RealName
FROM [dbo].[ImageDocuments] as fu, 
     [dbo].[Documents] as d
WHERE fu.DocumentID = d.DocumentID

DELETE FROM DocumentID WHERE DocumentID IN 
(SELECT DocumentID FROM @Table )

Upvotes: 0

Dhaval
Dhaval

Reputation: 2861

I think this should work.

As you already copied all rows in ImageDocuments you can safely delete all the DocumentID in Documents 

    UPDATE [dbo].[ImageDocuments]
    SET [dbo].[ImageDocuments].[OriginalFileName] = d.FileName,
        [dbo].[ImageDocuments].[StorageFileName] = d.RealName
    FROM [dbo].[ImageDocuments] as fu, 
         [dbo].[Documents] as d
    WHERE fu.DocumentID = d.DocumentID;

    Delete From Documents where Documents.DocumentID in 
(Select ImageDocuments.DocumentID from ImageDocuments);

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Use this:

UPDATE [dbo].[ImageDocuments]
SET [dbo].[ImageDocuments].[OriginalFileName] = d.FileName,
    [dbo].[ImageDocuments].[StorageFileName] = d.RealName
OUTPUT INSERTED.ImageDocumentId
INTO @Ids
FROM [dbo].[ImageDocuments] as fu, 
     [dbo].[Documents] as d
WHERE fu.DocumentID = d.DocumentID;

DELETE FROM Documents
WHERE DocumentID IN (SELECT ImageDocumentId FROM @IDs);

Upvotes: 1

Related Questions