title: Trying to accomplish without dynamic SQL (sql server)
body: <p>All,</p>
  
  <p>I'm trying to pull off an insert from one table to another without using dynamic sql. However, the only solutions I'm coming up with at the moment use dynamic sql. It's been tricky to search for any similar scenarios. </p>
  
  <p>Here are the details:</p>
  
  <p>My starting point is the following legacy table:</p>
  
  <pre><code>CREATE TABLE [dbo].[_Combinations](
  [AttributeID] [int] NULL,
  [Value] [varchar](50) NULL
  ) ON [PRIMARY]
  GO
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (16, N'1')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (16, N'2')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Red')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Orange')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Yellow')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Green')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Blue')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Indigo')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Violet')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'A')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'B')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'C')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'D')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'E')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'F')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'G')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'H')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'I')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'J')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'K')
  
  SELECT * FROM _Combinations
  </code></pre>
  
  <p>The _Combinations table contains a key for different types of attributes (AttributeID) and the possible values for each attribute (Value).</p>
  
  <p>In this case, there are 3 different attributes with multiple possible values, however there can be many more (up to 10).</p>
  
  <p>The requirement is then to create every possible combination of each value and store it normalized, as there will be other data stored with each possible combination. I need to store both the attribute keys and values that make up each combination, so it's not just a simple cross join to display each combination. The target table for storing each combination of attributes is this:</p>
  
  <pre><code>CREATE TABLE [dbo].[_CombinedAttributes](
  [GroupKey] [int] NULL,
  [AttributeID] [int] NULL,
  [Value] [varchar](50) NULL
  ) ON [PRIMARY]
  </code></pre>
  
  <p>So attribute combination records using the above data would look like this in the target table:</p>
  
  <pre><code>GroupKey    AttributeID Value
  1               8         A
  1               16        1
  1               28        Red
  2               8         B
  2               16        1
  2               28        Red
  </code></pre>
  
  <p>This gives me what I need. Each group has an identifier and I can track the attributeIDs and values that make up each group. I'm using two scripts to get from the _Combinations table to the format of the _CombinedAttributes table:</p>
  
  <pre><code>-- SCRIPT #1
  SELECT Identity(int) AS RowNumber, * INTO #Test
  FROM (
  SELECT AttributeID AS Attribute1, Value AS Value1 FROM _Combinations WHERE AttributeID = 8) C1
  CROSS JOIN 
  (
  SELECT AttributeID AS Attribute2, Value AS Value2 FROM _Combinations WHERE AttributeID = 16) C2
  CROSS JOIN
  (
  SELECT AttributeID AS Attribute3, Value AS Value3 FROM _Combinations WHERE AttributeID = 28) C3
  
  -- SCRIPT #2
  
  INSERT INTO _CombinedAttributes
  SELECT RowNumber AS GroupKey, Attribute1, Value1 
  FROM #Test
  UNION ALL
  SELECT RowNumber, Attribute2, Value2 
  FROM #Test
  UNION ALL
  SELECT RowNumber, Attribute3, Value3
  FROM #Test
  ORDER BY RowNumber, Attribute1
  </code></pre>
  
  <p>The above two scripts work, but obviously there's some drawbacks. Namely I need to know how many attributes I'm dealing with and there's hard coding of IDs, so I can't generate this on the fly. The solution I came up with is I build the strings for Script 1 and Script 2 by looping through the attributes in the the _Combinations table and generate execution strings which is long and messy but I can post if needed. Can anyone see a way to pull off the format for the final insert without dynamic sql? </p>
  
  <p>This routine wouldn't be run very much, but it's going to be run enough that I'd like to not be doing any execute string building and use straight SQL.</p>
  
  <p>Thanks in advance.</p>
  
  <p>UPDATE:</p>
  
  <p>When I use a second dataset, Gordon's code is no longer returning correct results, it's creating groups with only 1 attribute near the end, however on this second dataset I get the correct rowcount with Nathan's routine (row count on final result should be 396). But as I stated on the comments, if I use the first dataset, I get the opposite result, Gordon's returns correctly, but Nathan's code has dups. I'm at a loss. Here is the second data set:</p>
  
  <p>DROP TABLE [dbo].[_Combinations]
  GO</p>
  
  <p>CREATE TABLE [dbo].[_Combinations](
      [AttributeID] [int] NULL,
      [Value] varchar NULL
  ) ON [PRIMARY]
  GO</p>
  
  <pre><code>INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (16, N'1')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (16, N'2')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'&lt;=39')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'40-44')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'45-49')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'50-54')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'55-64')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'65+')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'AA')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'JJ')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'CC')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'DD')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'EE')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'KK')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'BB')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'FF')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'GG')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'HH')
  INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'II')
  </code></pre>