3

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!

gofr1
  • 15,741
  • 11
  • 42
  • 52
A. Reilly
  • 33
  • 4

1 Answers1

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
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • Hi @gofr1. Thank you kindly for the feedback. I was not familiar with cross join, so that is some new insight. However, in my example I used two numbers, 100 and 200 as an example of output. But the select statement I will be using will bring back 200K+ item numbers; a count that increases each day. How do I account for the dynamic nature of the item numbers in the union? – A. Reilly Oct 09 '16 at 14:23
  • So you need at first add all, then add only new? I understood you right? – gofr1 Oct 09 '16 at 14:38
  • Because the item number list can have new numbers added and old ones deleted, I will simply be pulling the total number of item numbers each day. So I'll need to refresh the relationships to Company every day, essentially rebuilding the output daily. Does that help? – A. Reilly Oct 09 '16 at 15:40
  • In that case you need MERGE: put cross join into CTE or insert into temp table, than merge it with destination table. Or one more way is to put this cross join into view and then just use this view in further query's. This view will always contain up to date data. – gofr1 Oct 09 '16 at 15:46
  • Okay, thanks. But I'm still a little stuck on creating the cross join. In your "Union All" example above you are individually selecting item 100 and 200 to union with Company 1 & 2. Can I select all items at once (200k+) to union with the static Company 1 & 2? – A. Reilly Oct 09 '16 at 16:03
  • That UNION it just an example to generate two tables to join. You need to generate a table with company's and cross join it with actual items table directly. Another words - remove items part from my sample, and in select query use actual items table instead of `items`. – gofr1 Oct 09 '16 at 16:08
  • Ah, okay. I think I got it. I'll give it a shot tomorrow and see what I come up with. Thank you for all the help! – A. Reilly Oct 09 '16 at 16:13
  • @jofr1 you are the man! It worked like a charm. Thanks again. – A. Reilly Oct 10 '16 at 16:53
  • Great! Once again, my pleasure to help! – gofr1 Oct 10 '16 at 16:55