How do I write a set based query? I have a groupSets table with fields
setId, idField, datasource, nameField, prefix, active
Data:
1,someIDfield, someTable, someField, pre1, 1
2,someotherIDfield, someTable, someotherField, pre2, 1
3,somethirdIDfield, someTable, somethirdField, pre3, 1
4,somefourthIDfield, someotherTable, somefourthField, pre4, 1

I need to generate records in another table by constructing queries from the
data in groups sets. I need to insert a record for each distinct result of
the query.
Example:
SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL

then I need to do an insert for each result of the above query

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, resultN)

--next reord from groupSets
SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS
NOT NULL


INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, resultN)


I basically want to do the same operation on each record in the groupSets
table. How can I accomplish this without a cursor? Any ideas?
Thanks for your help,
~ck

CK (c_kettenbach@hotmail.com) writes:
> I basically want to do the same operation on each record in the groupSets
> table. How can I accomplish this without a cursor? Any ideas?
> Thanks for your help,

You get yourself a table of numbers. That is, a table that holds numbers
from 1 to which limit you want. Here is a table-valued function written
by SQL Server MVP Itzik Ben-Gan which very fast, as long as you don't
need millions of numbers:

CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

"A Well Stated Problem is a Half Solved Problem". -- unknown IBM
expert

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

You have fundamental design problems in your vague narrative. You
still have not learned that a field is not anything whatsoever like a
column, that we don't use bit flags in SQL -- they are not scalars.
We don't mix data and metadata in a table. Your names reek of an EAV
design.

>> I need to generate records [sic] in another table by constructing queries from the data in group sets. I need to insert a record [sic] for each distinct result of the query. <<

NO! This is not magnetic tapes or punch cards. We write a VIEW -- a
virtual table that is always correct when invoked. The VIEW also
needs a good table name -- not "Groups", which don't tell us what kind
of things is in that set.

>> I basically want to do the same operation [what is the operation??] on each record [sic] in the GroupSets table. How can I accomplish this without a cursor? <<

I played with trying to guess at this operation, but decided that you
should be the one describing it. Without keys, this is not usable non-
code.

CK wrote:

> How do I write a set based query? I have a groupSets table with fields
> setId, idField, datasource, nameField, prefix, active
> Data:
> 1,someIDfield, someTable, someField, pre1, 1
> 2,someotherIDfield, someTable, someotherField, pre2, 1
> 3,somethirdIDfield, someTable, somethirdField, pre3, 1
> 4,somefourthIDfield, someotherTable, somefourthField, pre4, 1

This smells fishy. groupSets should probably be replaced with a view,
but the proper way to build that view depends on the specific contents
desired (four dummy entries is not much to go on).

> I need to generate records in another table by constructing queries from the
> data in groups sets. I need to insert a record for each distinct result of
> the query.
> Example:
> SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL
>
> then I need to do an insert for each result of the above query
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (1, prefix + nameField, 1, result1)
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (1, prefix + nameField, 1, result2)
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (1, prefix + nameField, 1, result3)
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (1, prefix + nameField, 1, result4)
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (1, prefix + nameField, 1, resultN)
>
> --next reord from groupSets
> SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS
> NOT NULL
>
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (2, prefix + nameField, 1, result1)
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (2, prefix + nameField, 1, result2)
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (2, prefix + nameField, 1, result3)
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (2, prefix + nameField, 1, result4)
>
> INSERT INTO groups(setId, groupName, active, groupingEntityID)
> VALUES (2, prefix + nameField, 1, resultN)
>
>
> I basically want to do the same operation on each record in the groupSets
> table. How can I accomplish this without a cursor? Any ideas?
> Thanks for your help,

Your generic names are way too generic. Anonymize if you must, but
make it possible to distinguish between different levels.

Post the following:
* SQL statements to define input and output tables
* SQL statements to populate input tables with sample input data
* What specific output data you would want to end up with, given
this input data

Ur an idiot man. Get lost. Your ego far exceeds your intelligence.

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:b91a91cd-0e23-4ad1-b142-22bead23c75e@d45g2000hsc.googlegroups.com...
> "A Well Stated Problem is a Half Solved Problem". -- unknown IBM
> expert
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. If you know how, follow ISO-11179 data element naming
> conventions and formatting rules. Temporal data should use ISO-8601
> formats. Code should be in Standard SQL as much as possible and not
> local dialect.
>
> Sample data is also a good idea, along with clear specifications. It
> is very hard to debug code when you do not let us see it. If you want
> to learn how to ask a question on a Newsgroup, look at:
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> You have fundamental design problems in your vague narrative. You
> still have not learned that a field is not anything whatsoever like a
> column, that we don't use bit flags in SQL -- they are not scalars.
> We don't mix data and metadata in a table. Your names reek of an EAV
> design.
>
>>> I need to generate records [sic] in another table by constructing
>>> queries from the data in group sets. I need to insert a record [sic] for
>>> each distinct result of the query. <<
>
> NO! This is not magnetic tapes or punch cards. We write a VIEW -- a
> virtual table that is always correct when invoked. The VIEW also
> needs a good table name -- not "Groups", which don't tell us what kind
> of things is in that set.
>
>>> I basically want to do the same operation [what is the operation??] on
>>> each record [sic] in the GroupSets table. How can I accomplish this
>>> without a cursor? <<
>
> I played with trying to guess at this operation, but decided that you
> should be the one describing it. Without keys, this is not usable non-
> code.

Never mind. I figured it out. It does what I need to do using Cursors. this
is a small amount of data and the cursor works quite well. Sounds like the
Set based queries are overkill in this situation. Just adds an unnecessary
degree of complexity. Thanks for your help.

"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:gbf0cs$fjr$1@registered.motzarella.org...
> CK wrote:
>
>> How do I write a set based query? I have a groupSets table with fields
>> setId, idField, datasource, nameField, prefix, active
>> Data:
>> 1,someIDfield, someTable, someField, pre1, 1
>> 2,someotherIDfield, someTable, someotherField, pre2, 1
>> 3,somethirdIDfield, someTable, somethirdField, pre3, 1
>> 4,somefourthIDfield, someotherTable, somefourthField, pre4, 1
>
> This smells fishy. groupSets should probably be replaced with a view,
> but the proper way to build that view depends on the specific contents
> desired (four dummy entries is not much to go on).
>
>> I need to generate records in another table by constructing queries from
>> the
>> data in groups sets. I need to insert a record for each distinct result
>> of
>> the query.
>> Example:
>> SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL
>>
>> then I need to do an insert for each result of the above query
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (1, prefix + nameField, 1, result1)
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (1, prefix + nameField, 1, result2)
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (1, prefix + nameField, 1, result3)
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (1, prefix + nameField, 1, result4)
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (1, prefix + nameField, 1, resultN)
>>
>> --next reord from groupSets
>> SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS
>> NOT NULL
>>
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (2, prefix + nameField, 1, result1)
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (2, prefix + nameField, 1, result2)
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (2, prefix + nameField, 1, result3)
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (2, prefix + nameField, 1, result4)
>>
>> INSERT INTO groups(setId, groupName, active, groupingEntityID)
>> VALUES (2, prefix + nameField, 1, resultN)
>>
>>
>> I basically want to do the same operation on each record in the groupSets
>> table. How can I accomplish this without a cursor? Any ideas?
>> Thanks for your help,
>
> Your generic names are way too generic. Anonymize if you must, but
> make it possible to distinguish between different levels.
>
> Post the following:
> * SQL statements to define input and output tables
> * SQL statements to populate input tables with sample input data
> * What specific output data you would want to end up with, given
> this input data

>> Never mind. I figured it out. It does what I need to do using Cursors. this is a small amount of data and the cursor works quite well. Sounds like the Set based queries are overkill in this situation. Just adds an unnecessary degree of complexity. <<

"Against stupidity the gods themselves struggle in vain." - Die
Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

Gee, if you are so proud of it, then post it so that we poor unwashed
can see the concise, portable, scalable, robust code in your cursor.
Just based on 25+ years experience, I would bet that someone here
will find a set-based solution that is concise, portable, scalable and
robust.

Well I could use your help and you just rip on me like I'm a dumbass. I am
here to learn. That's all.
I am not proud of my solution but it is a solution nonetheless. Here is what
I came up with.I really would like to know how to accomplish the same thing
using Set based queries. Thanks for any help you might provide. I apologize
and I really would like your help.

Cheers,
~ck
BEGIN
DECLARE @tgSetId varchar(5), @tgSetName varchar(50), @ds varchar(50),
@grEntIdField varchar(50), @grEntNameField varchar(50), @nmgPrfx varchar(50)
DECLARE @sql nvarchar(4000)
DECLARE @tgSetMatrix TABLE
(
EntityID int
,[Name] varchar(255)
,TrainingGroupName varchar(255)
,GroupingEntityID int
,TrainingGroupID int
,TrainingGroupSetID int
,Active bit
)

DECLARE trainingGroupSets_cursor CURSOR FOR
SELECT TrainingGroupSetID, TrainingGroupSetName, Datasource,
GroupingEntityIDField, GroupingEntityNameField, NamingPrefix
FROM TrainingGroupSet WHERE Active = 1

OPEN trainingGroupSets_cursor
FETCH NEXT FROM trainingGroupSets_cursor
INTO @tgSetId, @tgSetName, @ds, @grEntIdField, @grEntNameField, @nmgPrfx

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT e.' + @grEntIdField + ', e.Name, b.TrainingGroupName,
b.GroupingEntityID, b.TrainingGroupID, b.TrainingGroupSetID, b.Active FROM
( '
SET @sql = @sql + 'SELECT DISTINCT ' + @grEntIdField + ',''' + @nmgPrfx +
'''+'+ @grEntNameField + ' AS [Name] FROM ' + @ds + ' WHERE ' +
@grEntIDField + ' IS NOT NULL) e '
SET @sql = @sql + 'RIGHT OUTER JOIN (SELECT GroupingEntityID,
TrainingGroupName, TrainingGroupID, TrainingGRoupSetID, Active FROM
db1.dbo.TrainingGroup WHERE TrainingGroupSetID = ' + @tgSetId + ' '
SET @sql = @sql + ') b ON e.' + @grEntIdField + '= b.GroupingEntityID '
SET @sql = @sql + 'UNION SELECT e.' + @grEntIdField + ', e.Name,
b.TrainingGroupName, b.GroupingEntityID, b.TrainingGroupID,
b.TrainingGroupSetID, b.Active FROM ( '
SET @sql = @sql + 'SELECT DISTINCT ' + @grEntIdField + ',''' + @nmgPrfx +
'''+'+ @grEntNameField + ' AS [Name] FROM ' + @ds + ' WHERE ' +
@grEntIDField + ' IS NOT NULL) e '
SET @sql = @sql + 'LEFT OUTER JOIN (SELECT GroupingEntityID,
TrainingGroupName, TrainingGroupID, TrainingGRoupSetID, Active FROM
db1.dbo.TrainingGroup WHERE TrainingGroupSetID = ' + @tgSetId + ' '
SET @sql = @sql + ') b ON e.' + @grEntIdField + '= b.GroupingEntityID '


INSERT INTO @tgSetMatrix EXEC sp_executesql @sql

/* If any training group id is null, we need to create the training group */
IF EXISTS(SELECT 1 FROM @tgSetMatrix WHERE TrainingGroupID IS NULL)
BEGIN
INSERT INTO db1.dbo.TrainingGroup(TrainingGroupSetID, TrainingGroupName,
Active, GroupingEntityID)
SELECT @tgSetId, [Name], 1, EntityID FROM @tgSetMatrix WHERE TrainingGroupID
IS NULL
END

/* If there is a training group but the entityId is null, we need to
deactivate the training group */
IF EXISTS(SELECT 1 FROM @tgSetMatrix WHERE EntityID IS NULL)
BEGIN
UPDATE tg
SET Active = 0
FROM db1.dbo.TrainingGroup tg INNER JOIN @tgSetMatrix m ON
tg.TrainingGroupID = m.TrainingGroupID
WHERE m.EntityID IS NULL
END

/* If there is a training group and entityId is not null and trainingGroupID
is not null and active is false, we need to reactivate the training group */
IF EXISTS(SELECT 1 FROM @tgSetMatrix WHERE EntityID IS NOT NULL AND
TrainingGroupID IS NOT NULL AND Active = 0)
BEGIN
UPDATE tg
SET Active = 1
FROM db1.dbo.TrainingGroup tg INNER JOIN @tgSetMatrix m ON
tg.TrainingGroupID = m.TrainingGroupID
WHERE (m.EntityID IS NOT NULL) AND (m.TrainingGroupID IS NOT NULL) AND
(m.Active = 0)
END

DELETE FROM @tgSetMatrix

FETCH NEXT FROM trainingGroupSets_cursor
INTO @tgSetId, @tgSetName, @ds, @grEntIdField, @grEntNameField, @nmgPrfx
END

CLOSE trainingGroupSets_cursor
DEALLOCATE trainingGroupSets_cursor
END


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1f6e7a6d-738d-4b0e-be80-0ac7fb84385a@f63g2000hsf.googlegroups.com...
>>> Never mind. I figured it out. It does what I need to do using Cursors.
>>> this is a small amount of data and the cursor works quite well. Sounds
>>> like the Set based queries are overkill in this situation. Just adds an
>>> unnecessary degree of complexity. <<
>
> "Against stupidity the gods themselves struggle in vain." - Die
> Jungfrau von Orleans; Friedrich von Schiller (1759-1805)
>
> Gee, if you are so proud of it, then post it so that we poor unwashed
> can see the concise, portable, scalable, robust code in your cursor.
> Just based on 25+ years experience, I would bet that someone here
> will find a set-based solution that is concise, portable, scalable and
> robust.

As far as I can see there is a database design problem (attribute
splitting on the TrainingGroups, as each has its own table, whose name
can be found in the "TrainingGroupSetName" column) and this procedure
is the cleanup task to put them into one table properly. So good set
based queries will be possible after that cleanup, but most likely not
before.

Brgds

Philipp Post

1. The names violate ISO-11179 rules. You actually have a column
named "name" -- could you be vaguer?? Well, "datasource" pretty
generic. Actually, you were vaguer with "entity_id" which is absurd.
The essence of an identifier is that it is specific to one and only
one set of entities. To believe in a universal, generic "entity_id"
is Kabala mystic tradition which all things in creation have a number
assigned by the Lord God of Israel. We name a data element for what
it is by its nature; not for how it is stored, or used in a particular
situation.
2. Even MS gave up on camelCasing; it is a bitch to maintain because
your eyes jump to uppercase letters then back again. Start using the
underscore to separate the attribute name from its roles and
properties. People read it in a linear fashion, thanks to a century
of ruled notebook paper.
3. Standard SQL and now parts of T-SQL require a semi-colon statement
terminator, so start using it. Another question is why did you use
extra parentheses, single-statement BEGIN-END block, commas at the
start of a line, open and close parentheses on their own line, etc.?
We did that stuff with punch cards so we could slip in more cards
later in the 1960's. We have video terminals and editors now, so stop
formatting your code as if you were is a punch card world.
4. Why did you use "_field" as part of the data element names? Fields
and columns are totally different concepts. I chopped off that file
system legacy. And why are so many things VARCHAR(50), a magic number
from ACCESS? The length of a character column is one of the most
important properties it has, unlike field who length is determined by
the application program reading the file.
5. Table variables (proprietary in T-SQL) and temporary tables (not
ANSI Standard in T-SQL) are attempts to mimic scratch tapes and are to
be avoided. Instead we have CTEs, VIEWs and derived tables. The
optimizer can use these logical virtual tables to best advantage
instead of being forced to materialize them in physical storage.
6. We do not write with flags -- that is more punch card stuff. SQL
is a predicate language. We had to set flags with magnetic tapes and
punch cards after we determined a status in one procedural step to
pass the results to the next procedural step. Hey, back then, big
computers were less powerful than your wristwatch is today. Another
problem with flags is that you have to check them before you use them,
so why not just use the predicate instead of the flag? They force you
to have tightly coupled code modules Remember that term from basic
Software Engineering? It still applies in declarative languages.
Another problem is that you quickly have to write code that plays "20
Questions" and requires insanely complex CHECK() constraints. And the
name "active" is an adjective without a noun, so I changed it to
active_flg. But we need to get rid of it completely. We need a
(start_date, end_date) pair that tells us the duration of the active
status. This is, again, basic data modeling.
7. The construct ((A RIGHT OUTER B) UNION (A LEFT OUTER JOIN B)) in
your generated code is weird for several reasons. First of all, there
is a lot of redundancy (We hate redundancy in RDBMS, like the Pope
hates Evil). Next, we dislike RIGHT OUTER JOINs in our Latin alphabet
culture. We read left to right, so having the preserved table come
later is conceptually hard. Then you did not need the SELECT DISTINCT
because the UNION will remove redundant duplicates for you; let the
optimizers decide how to do that. But the kicker is that the whole
damn mess can probably be reduced to (A FULL OUTER JOIN B), preserving
both tables. Since you did not post DDL, as per netiquette, this is
only a guess.
SELECT ..
FROM (SELECT..
FROM Datasource
WHERE gr_ent_id IS NOT NULL) -- impossible by def
AS E
FULL OUTER JOIN
(SELECT ..
FROM db1.dbo.TrainingGroups
WHERE traininggroup_set_id = tg_set_id)
AS B
ON E.gr_ent_id = B.grouping_entity_id;

8. The comment /* If any training group id is NULL, we need to create
the training group */ is insane. BY DEFINITION, an identifier is
always NOT NULL, unique and exists. That is why it is ALWAYS a key
somewhere in the schema. But we have no DDL to see how screwed up the
schema is. That creation process is another function. I invented the
catch phrase "Automobiles, Squids and Britney Spears" to describe
tables and modules that have no cohesion -- they do more than one task
or store several unrelated things which are of totally different
natures.
9. What kind of things are "TrainingGroup" and "TrainingGroupSet" in a
set-oriented language? Think about it. Is this a hierarchy or
something? You are also telling us you have one of each by the
singular names; did you mean "TrainingGroups" or what?
10. Why did you put an "IF EXIST(..)" in front of an "UPDATE.."
statement? The WHERE clause of the UPDATE will find if the set of
rows to be updated is empty; just write a good predicate. Your
mindset is still in file systems. An empty set (i.e. table) is still
a set, but a missing file or an empty file (i.e. physical directory
entry NIL pointer) is a real problem in old file systems. Also, never
use that proprietary UPDATE.. FROM.. crap; it is proprietary and has
changed semantics at least twice since it was invent decades ago by
Sybase. Learn to write good Standard SQL.
11. The "EXISTS (SELECT 1.." tells me that you learned SQL from an old
Oracle person. The modern, preferred form is "EXISTS (SELECT * ..."
instead. Before Oracle had an optimizer, this made a difference in
performance decades ago. Now, it is just a confusing antique -- the
watch pocket in blue jean of SQL. All the SQLs I know will do it the
same way, but it makes you look like a hick.
12. Please set up basic naming conventions. I can guess that "tg_" is
"Testgroup_", that "ds" is "Datasource" and "gr_" is "group_", but why
not use a text editor and be consistent? Data dictionary needs this.
A real professional is always asking the question "How can I make this
code easier for the poor dumb bastards that have to maintain it after
I get my dot-com stock options and retire in the South of France?"
13. Dynamic SQL is usually an admission that any future random
untrained user will be able to write better code than you can. It is
right up there with cursors as a kludge, but it can allow SQL
Injection and destroy the company (read about T. J. Maxx and the
credit card attack in the trade press).
14. Why did you generate "(nmg_prfx + gr_ent_name) AS "vague_name"?
The data element names change from the rest of the schema, which is
awful. But this implied that you have split an attribute across two
columns when it needs to be in one.
15. You declared a table variable named "Testgroup_Set_Matrix" which
is as bad as or worst than the "_field" in those column names. First,
it violates the rule that a data element is named for what it is and
not how it is stored. But you cannot store a matrix in SQL! We have
one and only one data structure in RDBMS; the table. A matrix is an
algebraic structure with math rules, transposes, inverses, eigenvalues
and lots of other properties. Words have meaning, so don't snorf your
dinglehoppers!
16. This table ought to be a CTE and that still stinks. It would look
like this:
WITH Active_Testgroup_Set (..)
AS
(SELECT traininggroup_set_id, traininggroup_set_name,
datasource, -- why is this both a table & column name?
grouping_entity_id, grouping_entity_name, naming_prefix
FROM Traininggroup_Set
WHERE active_flg = 1) --flags suck!

17. Combine UPDATEs so you touch the tables only once, something like
this:

UPDATE db1.dbo.TrainingGroups
SET active_flg
= CASE
WHEN EXISTS
(SELECT *
FROM Active_Testgroup_Set AS A
WHERE A.entity_id IS NULL -- impossible by def!
AND TrainingGroups.traininggroup_id = A.traininggroup_id)
THEN 0
WHEN EXISTS
(SELECT *
FROM Active_Testgroup_Set AS A
WHERE TrainingGroups.traininggroup_id = A.traininggroup_id
AND A.entity_id IS NOT NULL -- true by def
AND A.traininggroup_id IS NOT NULL -- true by def
AND A.active_flg = 0)
THEN 1
ELSE active_flg -- otherwise, do nothing
END;

18. I think the DDL is screwed up and that if you can fix it, the
system will perform 1-2 or more orders of magnitude better, be
smaller, and not need convoluted DML to answer queries or change the
structure of the schema.