CODEWITHSUNDEEP
mysqlsql
user3505203
user3505203

Reputation: 11

SQL transfer data to another table using join

I have old tables Items(vendorId-FK, ManufacturerId (IS NOT FK)), Vendors(PK- VendorId int), Manufacturer(PK-ManufacturerId int) Need to transfer data to new DB and it works on transferring data from vendor, but from Manufacturer it transfer only 30000 (where oldManufacturerId is not null), and other 5000 is not transferred. Any ideas what I'm doing wrong?

New Table Manufacturer ( OldManufacturerID int, newManufacturerId Uniqueidentifier default newid(), ManufacturerName varchar (100),

    )

New Table Items ( ... ItemDescription, ManufacturerId uniqueidentifier, VendorId uniqueidentifier )

INSERT INTO dbo.Item
        ( ...
          ItemDescription,
          ManufacturerId ,
          VendorId 
        )
SELECT
...
itemDescription,
m.ManufacturerId ,
v.VendorId
FROM OldSqlDatabase.dbo.tbl_Items i 
    JOIN NewSqlDatabase.dbo.Vendor v ON ISNULL(i.vendor_id, '') = ISNULL(v.SourceVendorID, '')
    JOIN NewSqlDatabase.dbo.Manufacturer m ON ISNULL(i.manufacturer_id, '') = ISNULL(m.SourceManufacturerID, '')

I just transfered a data since this table didn't have any relationships, new manufactureId has default newid(), and this new id i want to use in the new item table INSERT INTO dbo.Manufacturer ( OldManufacturerID , ManufacturerName , ) SELECT manufacturer_id , manufacturer_name , FROM oldManufacture

Upvotes: 0

Views: 617

Answers (1)

Mike Brant
Mike Brant

Reputation: 71394

You likely need to use a LEFT JOIN the get records where there a no values (null values) on which to join. The LEFT JOIN will allow you to select all records from the first table being specified in the join, even if there are no matching records available in the second table in the join.

This would also allow you to get rid of that ISNULL() stuff in the join definitions as well. Those would perform very poorly, as they would not be able to use indexes.

I would suggest something like:

INSERT INTO dbo.Item
        ( ...
          ItemDescription,
          ManufacturerId ,
          VendorId 
        )
SELECT
...
itemDescription,
m.ManufacturerId ,
v.VendorId
FROM OldSqlDatabase.dbo.tbl_Items i 
LEFT JOIN NewSqlDatabase.dbo.Vendor v
  ON i.vendor_id = v.SourceVendorID
LEFT JOIN NewSqlDatabase.dbo.Manufacturer m
  ON i.manufacturer_id = m.SourceManufacturerID

Upvotes: 1

Related Questions