Insert Dynamic & Static Data into Temp Table

Question

I'm new to using "Insert" functions, and know enough SQL to be dangerous.

I am trying to generate my own duplicated row data by using a combination of dynamic and static data. I'm using SQL Server 2012.

Example of need below.

Create table #Temp
(Item nchar(32),
Company nchar(32))
;

Insert Into #Temp (Item, Company)
VALUES
(X, 'Company1'),
(X, 'Company2')
;

• "Company1" and "Company2" is the "Static" part that I am manually adding.
• "X" above in this case is Item number from a select statement [Select Item From Table and is the "dynamic" part as the number of items returned increases every day.

We'll pretend that today the "Table" has only two item numbers, 100 and 200. So my end result would be adding each instance of Company to each item number as follows:

Item |Company
----------
100 | Company1
100 | Company2
200 | Company1
200 | Company2

Thank you in advance!

Answer 1

You can use CROSS JOIN like:

;WITH items AS (
SELECT 100 Item
UNION ALL
SELECT 200
), companies AS (
SELECT 'Company1' as Company
UNION ALL
SELECT 'Company2'
)

--Insert Into #Temp (Item, Company)
SELECT  i.Item,
        c.Company
FROM items i
CROSS JOIN companies c

Output:

Item    Company
100     Company1
100     Company2
200     Company1
200     Company2