Search code examples
sqlssmsdata-migration

Only transfer rows Order Lines with matching Order Header

I need to migrate rows from my OrderLines table to a new database, but I only want the ones that have already had the matching headers migrated already.

I am using SQL Server Mgmt Studio and have tried the following code without any luck:

INSERT INTO dbNew.dbo.OrderLines
(columns)

SELECT columns 
FROM dbOld.dbo.OrderLines

WHERE EXISTS
 (SELECT * FROM dbNew.dbo.OrderHeader
  WHERE dbOld.dbo.OrderLines.OrderID = dbNew.dbo.OrderHeader.OrderID)
Solution

  • Try this:

    insert into dbNew.dbo.OrderLines (columns)
select columns
from dbOld.dbo.OrderLines old_lines
where exists (
    select null
    from dbNew.dbo.OrderHeader h
    where old_lines.OrderID = h.OrderID
)

    EDIT: Oops, fixed typo.