I want to implement a "cloning" feature in my application and this
involves cloning related data two.
Having this structure (simplified but representative of my problem)
CREATE TABLE Enterprise (enteid INTEGER IDENTITY PRIMARY KEY, entename
VARCHAR(30) NOT NULL)
CREATE TABLE Department (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL, enteid INTEGER NOT NULL REFERENCES Enterprise
(enteid))
CREATE TABLE Employee (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL, employeename VARCHAR(30) NOT NULL, deptid INTEGER
NOT NULL REFERENCES Department (deptid))
I want to:
Clone a complete Enterprise, having @.enteid as a parameter (the id of
the enterprise to clone ) :
1 - Copy one enterrpise row. Obtaining a @.new_enteid
2 - Copy all Departaments rows associated to @.enteid (@.deptid's).
Obtaining @.new_deptid's and associating this new rows to @.new_enteid
3 - Copy all Employee rows related to all @.deptid's and associating
this new rows to the correspondig @.new_deptid's
Is this possible to make in one SQL sentence
Best Regards
Fabio Cavassini
http://fabioon.blogspot.com/Fabio Cavassini wrote:
> I want to implement a "cloning" feature in my application and this
> involves cloning related data two.
> Having this structure (simplified but representative of my problem)
> CREATE TABLE Enterprise (enteid INTEGER IDENTITY PRIMARY KEY, entename
> VARCHAR(30) NOT NULL)
> CREATE TABLE Department (deptid INTEGER IDENTITY PRIMARY KEY, deptname
> VARCHAR(30) NOT NULL, enteid INTEGER NOT NULL REFERENCES Enterprise
> (enteid))
> CREATE TABLE Employee (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL, employeename VARCHAR(30) NOT NULL, deptid INTEGER
> NOT NULL REFERENCES Department (deptid))
> I want to:
> Clone a complete Enterprise, having @.enteid as a parameter (the id of
> the enterprise to clone ) :
> 1 - Copy one enterrpise row. Obtaining a @.new_enteid
> 2 - Copy all Departaments rows associated to @.enteid (@.deptid's).
> Obtaining @.new_deptid's and associating this new rows to @.new_enteid
> 3 - Copy all Employee rows related to all @.deptid's and associating
> this new rows to the correspondig @.new_deptid's
> Is this possible to make in one SQL sentence
> Best Regards
> Fabio Cavassini
> http://fabioon.blogspot.com/
Why would you ever want to duplicate data in a table? Don't you have
any other keys? Take two enterprises of the same name for example - how
will you tell them apart? Duplicating the data in the Employee table
would violate Second Normal Form.
The right answer is to design a better normalized data model. The wrong
answer is to wreck data integrity even further by using the
SCOPE_IDENTITY() function, which will allow you to fill your tables
with redundant data in exactly the way you have proposed.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The example is only representative of the problem, in fact I extracted
the data model from an old post that you posted ;)
(http://groups.google.com/group/micr...618a7b00b?hl=en)
The real data model is the following:
My enterprise works with Service_Orders, these service orders are
populated with:
Sell_Articles: This is actually what my enterprise sells to it's
customers
and
Buy_Articles: This is what my enterprise buys to it's providers to get
the Sell Articles
This is the real data model:
CREATE TABLE OrderService (osid INTEGER IDENTITY PRIMARY KEY)
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname
VARCHAR(30) NOT NULL)
CREATE TABLE Buy_Article_Type (batid INTEGER IDENTITY PRIMARY KEY,
batname
VARCHAR(30) NOT NULL)
CREATE TABLE Sell_Article (said INTEGER IDENTITY PRIMARY KEY, satid
INTEGER NOT NULL REFERENCES Sell_Article_Type (satid), osid
INTEGER NOT NULL REFERENCES OrderService (osid), amount DECIMAL NOT
NULL)
CREATE TABLE Buy_Article (baid INTEGER IDENTITY PRIMARY KEY, batid
INTEGER NOT NULL REFERENCES Buy_Article_Type (batid),said
INTEGER NOT NULL REFERENCES Sell_Article (said), amount DECIMAL NOT
NULL)
Now, it's very common that most clients always requires same (or very
near) ServiceOrders, so that's why I need to implement a feature to
"clone" ServiceOrders, This clone will be used as a template for the
user to modify it if needed.
What I need is the SQL Statement that allows me to "clone" a service
order including all it's relationships: Sell_Articles and Buy_Articles.
Hope you can help me
Regards
Fabio|||Fabio Cavassini wrote:
> The example is only representative of the problem, in fact I extracted
> the data model from an old post that you posted ;)
> (http://groups.google.com/group/micr...618a7b00b?hl=en)
>
You did not. You changed it by taking out the alternate keys which
makes the model useless and your requirement highly suspect.
> The real data model is the following:
> My enterprise works with Service_Orders, these service orders are
> populated with:
> Sell_Articles: This is actually what my enterprise sells to it's
> customers
> and
> Buy_Articles: This is what my enterprise buys to it's providers to get
> the Sell Articles
> This is the real data model:
>
Again all the keys are missing. IDENTITY should not be the only key of
any table, that's what UNIQUE constraints are for. You need to fix this
first.
> Now, it's very common that most clients always requires same (or very
> near) ServiceOrders, so that's why I need to implement a feature to
> "clone" ServiceOrders, This clone will be used as a template for the
> user to modify it if needed.
>
A database designer is supposed to eliminate redundancy - not design it
in. With proper design the idea of cloning data in a table would be A)
impossible because it would violate integrity constraints, B)
unecessary. If you don't understand database design principles such as
normalization then I recommend you study before you try to implement
this. Alternatively, add the missing candidate keys to your DDL and
post again so we can help you better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>Again all the keys are missing. IDENTITY should not be the only key of
>any table, that's what UNIQUE constraints are for. You need to fix this
>first.
Can you tell me how would you change the model to insert these keys?
---
CREATE TABLE OrderService (osid INTEGER IDENTITY PRIMARY KEY)
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname
VARCHAR(30) NOT NULL)
CREATE TABLE Buy_Article_Type (batid INTEGER IDENTITY PRIMARY KEY,
batname
VARCHAR(30) NOT NULL)
CREATE TABLE Sell_Article (said INTEGER IDENTITY PRIMARY KEY, satid
INTEGER NOT NULL REFERENCES Sell_Article_Type (satid), osid
INTEGER NOT NULL REFERENCES OrderService (osid), amount DECIMAL NOT
NULL)
CREATE TABLE Buy_Article (baid INTEGER IDENTITY PRIMARY KEY, batid
INTEGER NOT NULL REFERENCES Buy_Article_Type (batid),said
INTEGER NOT NULL REFERENCES Sell_Article (said), amount DECIMAL NOT
NULL)
---
>A database designer is supposed to eliminate redundancy - not design it
>in. With proper design the idea of cloning data in a table would be A)
>impossible because it would violate integrity constraints, B)
>unecessary. If you don't understand database design principles such as
>normalization then I recommend you study before you try to implement
>this. Alternatively, add the missing candidate keys to your DDL and
>post again so we can help you better.
Well, I know normalization principles, but I think you missed some of
my description of the problem or maybe I should never used the world
"clon".
The idea it's not to make exact clones of set of Order Services, the
idea it's to use existing Order Services as "templates" for new ones,
but this will be different, not the same.
The idea is so simple as Word Templates are, when you use a Word
Template, the New Document you create includes the Template Code
(redundancy) and you can modify it at your desire. IMHO sometimes
redundancy it's necessary to keep a system simple
Regards
Fabio|||Fabio Cavassini wrote:
> Can you tell me how would you change the model to insert these keys?
> ---
>
For example:
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname VARCHAR(30) NOT NULL UNIQUE)
I don't know what the purpose of the OrderService table is. Apparently
it contains no real data. I could understand if it was just used to
generate a value used as a unique key in another table but that appears
not to be the case here.
I don't know about Sell_Article or Buy_Article either. Apparently they
represent different amounts for each type. At a guess the logical
candidate key in Sell_Article may be (satid, osid)? Possibly you have
some entities missing. It's very hard to guess the meaning just from a
list of column names.
The IDENTITY is called a *surrogate* key. That means it stands in for
some other candidate key in a foreign key reference. In your logical
model you should always be able to substitute the business key (aka
natural key or logical key) for the surrogate in the same table. To
enforce entity integrity you need to declare the business key as well
for the obvious reason that IDENTITY does not prevent duplicates in a
table - it just enumerates them.
Now do a little exercise with your logical model. Remove the IDENTITY
columns and try to normalize the schema to BCNF. The results may be
enlightening. :-)
> The idea it's not to make exact clones of set of Order Services, the
> idea it's to use existing Order Services as "templates" for new ones,
> but this will be different, not the same.
Even if this was desirable from a logical point of view (it clearly
isn't) it would be very impractical since it would prevent you from
ever declaring any unique keys. The problem is you are trying to
implement GUI functionality in the database. I suggest you persist the
real data when the user has something real to store - not before.
Creating a redundant row and then updating it isn't likely scale well.
> IMHO sometimes
> redundancy it's necessary to keep a system simple
If you seriously think that redundancy simplifies databases then just
trawl the archives of this group for the thousands of posts from those
desperately trying to eliminate redundancy from bad data models. Better
read some books on relational design too. I recommend Date's "Database
in Depth" BTW.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||To answer the question in your original post, if you look at my replies
in the link you posted you'll see two solutions for inserting the rows
to the referencing table. The solution with the alternate keys uses
single set-based INSERT statements. The solution without the keys
requires a WHILE loop, several statements and row-at-a-time INSERTs.
I'll leave you to decide which is "simpler". I know which I prefer...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Steven Pasetti wrote:
