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')
;
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!
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