Insert Data with Foreign Keys

Question

I have two tables connected with a foreign key constraint. Each table has a primary Key (ProductID ,BaseProductID ) that is an autoincrementing identity column. Every BaseProduct has only 1 Product during the import.

BaseProduct
- BaseProductID
- BaseProductName

Product
- ProductID
- BaseProductID
- ProductName

I try to insert rows into both tables from a different Database that has the same Tables, but different seed Values. I dont want to keep the ids from the source database.

Is there a way to do this in one statement?

EDIT

The Select would be

Select ProductName FROM @SourceProduct 
WHERE Not ProductName  in (
select ProductName   FROM @TargetBaseProduct
)

I tried the solution by Giorgi which is close, but the matching by

tgt.BaseProductID = src.BaseProductID

leads to result where source rows with BaseProductID= 1 are not inserted if the target already has an entry with BaseProductID = 1.

DECLARE @SourceBaseProduct TABLE
    (
      BaseProductID INT ,
      BaseProductName NVARCHAR(MAX)
    )
DECLARE @SourceProduct TABLE
    (
      ProductID INT ,
      BaseProductID INT ,
      ProductName NVARCHAR(MAX)
    )

DECLARE @TargetBaseProduct TABLE
    (
      BaseProductID INT IDENTITY(1, 1) ,
      BaseProductName NVARCHAR(MAX)
    )
DECLARE @TargetProduct TABLE
    (
      ProductID INT IDENTITY(1, 1) ,
      BaseProductID INT ,
      ProductName NVARCHAR(MAX)
    )


INSERT  INTO @SourceBaseProduct
VALUES  ( 1, 'BaseProduct1' ),
        ( 2, 'BaseProduct2' ),
        ( 4, 'BaseProduct3' )
INSERT  INTO @SourceProduct
VALUES  ( 3, 1, 'Product1' ),
        ( 9, 2, 'Product2' ),
        ( 27, 4, 'Product3' )

INSERT  INTO @TargetBaseProduct
(

      BaseProductName 
      )
VALUES  ( 'Existing Product Base' )

INSERT  INTO @TargetProduct(
 BaseProductID  ,
      ProductName)
VALUES  (  @@IDENTITY, 'Existing Product' )

MERGE INTO @TargetBaseProduct tgt
USING
    ( SELECT    sbp.BaseProductID ,
                sbp.BaseProductName ,
                sp.ProductName
      FROM      @SourceBaseProduct sbp
                JOIN @SourceProduct sp ON sp.BaseProductID = sbp.BaseProductID
    ) AS src
ON tgt.BaseProductID = src.BaseProductID
WHEN NOT MATCHED BY TARGET THEN
    INSERT VALUES ( src.BaseProductName )
OUTPUT
    Inserted.BaseProductID ,
    src.ProductName
    INTO @TargetProduct(BaseProductID, ProductName);


SELECT  * FROM    @TargetBaseProduct
SELECT  * FROM    @TargetProduct

Output

BaseProductID   BaseProductName
1               Existing Product Base
2               BaseProduct2
3               BaseProduct3


ProductID   BaseProductID   ProductName
1           1               Existing Product
2           2               Product2
3           3               Product3

Expected result

BaseProductID   BaseProductName
1               Existing Product Base
2               BaseProduct1
3               BaseProduct2
4               BaseProduct3


ProductID   BaseProductID   ProductName
1           1               Existing Product
2           2               Product1
3           3               Product2
4           4               Product3

EDIT

using

ON 1 = 0

like Giorgi suggested gave the right result

Answer 1

If I get you right, there is way with MERGE:

DECLARE @SourceBaseProduct TABLE
    (
      BaseProductID INT ,
      BaseProductName NVARCHAR(MAX)
    )
DECLARE @SourceProduct TABLE
    (
      ProductID INT ,
      BaseProductID INT ,
      ProductName NVARCHAR(MAX)
    )

DECLARE @TargetBaseProduct TABLE
    (
      BaseProductID INT IDENTITY(1, 1) ,
      BaseProductName NVARCHAR(MAX)
    )
DECLARE @TargetProduct TABLE
    (
      ProductID INT IDENTITY(1, 1) ,
      BaseProductID INT ,
      ProductName NVARCHAR(MAX)
    )


INSERT  INTO @SourceBaseProduct
VALUES  ( 1, 'BaseProduct1' ),
        ( 2, 'BaseProduct2' ),
        ( 4, 'BaseProduct3' )
INSERT  INTO @SourceProduct
VALUES  ( 3, 1, 'Product1' ),
        ( 9, 2, 'Product2' ),
        ( 27, 4, 'Product3' )



MERGE INTO @TargetBaseProduct tgt
USING
    ( SELECT    sbp.BaseProductID ,
                sbp.BaseProductName ,
                sp.ProductName
      FROM      @SourceBaseProduct sbp
                JOIN @SourceProduct sp ON sp.BaseProductID = sbp.BaseProductID
    ) AS src
ON tgt.BaseProductID = src.BaseProductID
WHEN NOT MATCHED BY TARGET THEN
    INSERT VALUES ( src.BaseProductName )
OUTPUT
    Inserted.BaseProductID ,
    src.ProductName
    INTO @TargetProduct(BaseProductID, ProductName);


SELECT  * FROM    @TargetBaseProduct
SELECT  * FROM    @TargetProduct

Output:

BaseProductID   BaseProductName
1               BaseProduct1
2               BaseProduct2
3               BaseProduct3


ProductID   BaseProductID   ProductName
1           1               Product1
2           2               Product2
3           3               Product3

EDIT: if you want to insert existing rows too, then change

ON tgt.BaseProductID = src.BaseProductID

to

ON 1 = 0