INSERT INTO table from 2 unrelated tables-CodePudding

Not sure how to achieve the result, need your help

Source A:

SELECT SourceAID
FROM [dbo].[SourceA]

Source B:

SELECT SourceBID
FROM [dbo].[SourceB]

Result table (select example):

SELECT SourceAID
      ,SourceBID
      ,Value
  FROM [dbo].[Result]

Idea of insert: For each SourceAID, i need to insert records with all SourceBID. There is no any reference between these 2 tables.

Idea by hand looks like this:

 INSERT INTO [dbo].[Result] ([SourceAID], [SourceBID], [Value])
  VALUES ('AID_1', 'BID_1', NULL),
('AID_1', 'BID_2', NULL),
('AID_1', 'BID_3', NULL),
('AID_2', 'BID_1', NULL),
('AID_2', 'BID_2', NULL),
('AID_2', 'BID_3', NULL)

and so on

CodePudding user response：

As @Larnu said.

Use some following code:

INSERT INTO [dbo].[Result] ([SourceAID], [SourceBID], [Value])
SELECT 
    SA.SourceAID,
    SB.SourceBID,
    NULL
FROM
    [dbo].[SourceA] AS SA
    CROSS JOIN [dbo].[SourceB] AS SB

CodePudding user response：

The other way is using subquery

INSERT INTO [dbo].[Result] ([SourceAID], [SourceBID], [Value])
SELECT SA.SourceAID,SB.SourceBID,NULL
(SELECT 1 AS ID ,SA.SourceAID FROM [dbo].[SourceA]) SA
join 
(SELECT 1 AS ID ,SA.SourceBID FROM [dbo].[SourceB]) SB
on SA.ID=SB.ID