Databases
StackOverflow2013
Postsdbo
POTrying to accomplish without dynamic SQL (sql server)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrying to accomplish without dynamic SQL (sql server)
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAS2012
    1. USAS2012
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTrying to accomplish without dynamic SQL (sql server)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTrying to accomplish without dynamic SQL (sql server)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POTrying to accomplish without dynamic SQL (sql server)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre you stuck with this table design? Entity-Attribute-Value is nearly always an anti-pattern. A column in a table *is* an "Attribute". If the column has a name of "attribute", something's usually wrong...
      singulars
      1. POTrying to accomplish without dynamic SQL (sql server)
      1. USN West
    2. COThis is one of those lagacy databases where some ignoramus thought it would be clever to create a database model inside thier database. Thereby, immediately duplicating the vendors efforts (in a degraded fashion) and reimplementing one of software's biggest anti-patterns.
      singulars
      1. POTrying to accomplish without dynamic SQL (sql server)
      1. USJodrell
    3. CO[EAV is only a despicable cancer to those who don't understand it](http://sqlblog.com/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx).
      singulars
      1. POTrying to accomplish without dynamic SQL (sql server)
      1. USAaron Bertrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload