Hi,

I have these tables as shown below. Say I want to duplicate a condition
group with ID = 10. Notice that there are identity columns in the Conditions
and Values tables. I can use a INSERT INTO ... SELECT FROM to insert new
rows into the ConditionGroups table. But when I get to Conditions and Values
subsequently I will need to get the generated identity value first before I
insert values.

What's the best way to do this? I want to do this in the database itself.
Are cursors avoidable?

Regards,
Nitin

---------------

CREATE TABLE [dbo].[ConditionGroups] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conditions] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ConditionGroupID] [int] NULL ,
[Lhs] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Operator] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Values] (
[ID] [int] NOT NULL ,
[ConditionID] [int] NOT NULL ,
[RhsValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

For multi-row inserts with identity columns used in referenced, your options
are limited to using a trigger that can correlate the rows from the inserted
table using an identifier that is not system generated or using a cursor
that can loop through each row & insert the values into the referencing
table.

Depending on how you have constructed your inserts, in SQL 2005, you might
be able to use the OUTPUT clause with insert statements too.

Thanks Anith,

I am just wondering whether I have modeled the table correctly. Is there any
known model to store expressions (condition groups, conditions, values etc)

Regards,
Nitin

Show quoteHide quote

Nitin M wrote:
You didn't post any keys but it appears that your Conditions table
doesn't have an alternate key at all (the GroupID is nullable). If you
fix that first and then add the necessary constraints you won't need a
cursor - three inserts should do it.

Hi David,

Sorry for not posting the complete code. Should have done that earlier.

My problem is with the identity columns being foreign keys. So when I try to
duplicate a condition I can duplicate the conditions with a simple insert
clause, but with values the problem will come as the newly generated values
for the Conditions.ID column will have to be retrieved some how before
values can be duplicated. Hope I am clear now. I have pasted the complete
code now.

Thanks a lot,
Nitin

----------------

CREATE TABLE [dbo].[ConditionGroups] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conditions] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ConditionGroupID] [int] NOT NULL ,
[Lhs] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Operator] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Values] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ConditionID] [int] NOT NULL ,
[RhsValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ConditionGroups] ADD
CONSTRAINT [PK_ConditionGroup] PRIMARY KEY  CLUSTERED
(
  [ID]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Conditions] ADD
CONSTRAINT [PK_Conditions] PRIMARY KEY  CLUSTERED
(
  [ID]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Values] ADD
CONSTRAINT [PK_RhsValues] PRIMARY KEY  CLUSTERED
(
  [ID]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Conditions] ADD
CONSTRAINT [FK_Conditions_ConditionGroups] FOREIGN KEY
(
  [ConditionGroupID]
) REFERENCES [dbo].[ConditionGroups] (
  [ID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Values] ADD
CONSTRAINT [FK_Values_Conditions] FOREIGN KEY
(
  [ConditionID]
) REFERENCES [dbo].[Conditions] (
  [ID]
) ON DELETE CASCADE
GO

------------




Show quoteHide quote

Are there any natural keys in those tables? Candidate keys, even, something
that uniquely identifies each row? Or is the identity column the only key?


ML

---
http://milambda.blogspot.com/

Unfortunately no. There can be two conditions like Name CONTAINS 'ABC' and
Name CONTAINS 'EFG'.
All conditions in a group are assumed to be separated by OR. When the
operator is IN then more than one values can be possible.

Can it be better modeled?

Show quoteHide quote

Nitin M wrote:
Like this:


ALTER TABLE ConditionGroups ADD CONSTRAINT ak1_ConditionGroups
UNIQUE (Name);

ALTER TABLE Conditions ADD CONSTRAINT ak1_Conditions
UNIQUE (ConditionGroupID,Lhs,Operator);

ALTER TABLE [Values] ADD CONSTRAINT ak1_Values
UNIQUE (ConditionID,RhsValue);


Now you can copy your condition group like this:

INSERT INTO dbo.ConditionGroups (name)
VALUES ('New Condition Group');

DECLARE @ConditionGroupID INT;
SET @ConditionGroupID = SCOPE_IDENTITY();

INSERT INTO dbo.Conditions (ConditionGroupID,Lhs,Operator)
SELECT @ConditionGroupID, Lhs, Operator
  FROM dbo.Conditions
  WHERE ConditionGroupID = 10;

INSERT INTO [Values] (ConditionID,RhsValue)
SELECT C2.ID, V.RhsValue
FROM dbo.[Values] AS V
JOIN dbo.Conditions AS C1
  ON V.ConditionID = C1.ID
   AND C1.ConditionGroupID = 10
JOIN dbo.Conditions AS C2
  ON C2.ConditionGroupID = @ConditionGroupID
   AND C2.Lhs = C1.Lhs
   AND C2.Operator = C1.Operator ;

Hi David,

A condition group can look like this
(
    Name contains 'ABC'
    OR
    Name contains 'DEF'
)

You cannot have a unique constraint on ConditionGroupID, Lhs, Operator in
the Conditions table. In future I will also need to associate some data with
each condition (Lhs, Operator, Rhs) so I need to store the Lhs, Operator
twice for the case mentioned above.

Hope I am clear now.

Regards,
Nitin


Show quoteHide quote

Nitin M wrote:
Show quoteHide quoteIn that case your schema isn't normalized and you should fix that. We
can't really design it for you because we don't know your data and
business requirements.

Let me rephrase:

1) is dbo.ConditionGroups.[Name] unique?
2) is each tuple of dbo.Conditions.Lhs, dbo.Conditions.ConditionGroupID,
dbo.Conditions.Operator unique?

Forget about the model for a moment - look at the requirements.

If the above is true, you have a natural key for each table.

Now, please, post some sample data and describe expected results.


ML

---
http://milambda.blogspot.com/

You could try remodeling it this way:
- add "RhsValue" field to "Conditions" table,
- and add UNIQUE constraint on (ConditionGroupID, Lhs, Operator, RhsValue)
  in "Conditions" table,
- for not "IN" expressions store values in "RhsValue" field in "Conditions"
  table,
- for "IN" expressions store NULL as "RhsValue" in "Conditions" table
  and the real values in "Values" table (you could rename it to "InValues"
  or "InList").

This model will prevent expressions like this:

(
  name IN ( 'a', 'b', 'c' )
  OR
  name IN ( 'd', 'e' )
)

but it will not limit your mechanism because it's equivalent to:
(
  name IN ( 'a', 'b', 'c', 'd', 'e' )
)


In that model when you want to copy whole expression (ConditionGroup),
then you make an INSERT with SELECT into "ConditionGroups" table
and get the SCOPE_IDENTITY() value. Next you make an INSERT with
SELECT into "Conditions" (using the value instead of oryginal
ID). Last thing is to INSERT data into "Values" using SELECT
based on ConditionGroupID, Lhs, Operator and RhsValue (remember
that RhsValue can be NULL and you want to treat NULL as equal to NULL,
so you should use expression like this:
(
(c1.RhsValue IS NULL) AND (c2.RhsValue IS NULL)
OR
(c1.RhsValue = c2.RhsValue)
)

It's not perfect but it should work and will allow you to copy
whole group with just 3 queries.


Kamil 'Hilarion' Nowicki

SQL is for data and **not** for rules.  You ought to be using Prolog or
LISP.  You are trying to drive nails with a pumpkin.  Having said all
that, people will call me mean if I do not give you this kludge:

I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL:

skill = Java AND (skill = Perl OR skill = PHP)

becomes the disjunctive canonical form:

(Java AND Perl) OR (Java AND PHP)

which we load into this table:

CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));

INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');

Assume we have a table of job candidates:

CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));

INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL');  -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');

The query is simple now:

SELECT DISTINCT C1.candidate_name
  FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
       = (SELECT COUNT(*)
            FROM Query AS Q2
           WHERE Q1.and_grp = Q2.and_grp);

You can retain the COUNT() information to rank candidates.  For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relational division.

<DIV>&quot;Nitin M&quot; &lt;ni***@nowhere.com&gt; wrote in message
Whether this is modeled correctly, it's hard to tell, but you might consider
an XML column to store the whole expression in a single row.




You can avoid cursors, but I had to add an additional column to the
Condition table to support the copy operation.

So,

Clone the ConditionGroup
Clone all the Conditions, marking them with the source condition
clone all the ConditionValues for the condition, using the source
information to corelate

EG

drop table [ConditionValues]
drop table [Conditions]
drop table [ConditionGroups]

go

CREATE TABLE [dbo].[ConditionGroups] (
[ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conditions] (
[ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[ConditionGroupID] [int] NULL REFERENCES ConditionGroups,
[Lhs] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Operator] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CopiedFromCondition] int null references Conditions
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ConditionValues] (
[ID] [int] IDENTITY NOT NULL primary key,
[ConditionID] [int] NOT NULL REFERENCES Conditions,
[RhsValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]
GO

insert into ConditionGroups(name) values ('a')
insert into Conditions (ConditionGroupID,Lhs,Operator)
          select 1,'j','joe'
union all select 1,'k','joe'

insert into ConditionValues(ConditionID,RhsValue)
          select 1,'value11'
union all select 1,'value12'
union all select 2,'value21'
union all select 2,'value22'
go



begin try
begin transaction
save transaction local

  declare @SourceGroup int
  set @SourceGroup = 1

  declare @NewConditionGroup int

  insert into ConditionGroups(name)
  select name
  from ConditionGroups
  where id = @SourceGroup

  set @NewConditionGroup = scope_identity()

  insert into Conditions(ConditionGroupID,Lhs,Operator,CopiedFromCondition)
  select @NewConditionGroup, Lhs, Operator, ID
  from Conditions
  where ConditionGroupID = @SourceGroup

  insert into ConditionValues(ConditionID, RHSValue)
  select c.ID,v.RHSValue
  from Conditions c
  join ConditionValues v
    on c.CopiedFromCondition = v.ConditionID

commit transaction
end try
begin catch

  rollback transaction local
  commit transaction
  exec utils.RethrowError

end catch

go
select *
from ConditionGroups
join Conditions
  on ConditionGroups.ID = Conditions.ConditionGroupID
join ConditionValues
  on ConditionValues.ConditionID = Conditions.ID
for xml auto

On Tue, 05 Sep 2006 16:15:14 +0300, Nitin M <ni***@nowhere.com> wrote:

You can use @@IDENTITY system function to get the generated identity. Look 
at it: http://msdn2.microsoft.com/en-us/library/ms187342.aspx

Show quoteHide quote

In most cases SCOPE_IDENTITY() is much better idea than @@IDENTITY (eg.
when there's a AFTER trigger on the table you are inserting into and that
trigger inserts into another table that uses identity column). The only
case that I've fount @@IDENTITY better that SCOPE_IDENTITY() was
when I had an INSTEAD OF INSERT trigger on that table (which inserted
data into this table and no other table) - SCOPE_IDENTITY() returned
nothing (because IDENTITY was not used in current scope, but in triggers
scope).

Kamil 'Hilarion' Nowicki

On Thu, 07 Sep 2006 12:42:47 +0300, Hilarion <hilari0n@noemail.nospam> 
wrote:

Show quoteHide quoteYes, you are right.

Show quoteHide quote

Primary Key
Postcode problem
Query Problem
About Cursor on Dynamically created Tables.
Creating 2nd transaction form inside db ?