March 12, 2010 at 6:56 pm
How can I copy and entity that is represented by records in more than 1 table (due to the fact that the tables are normalized or that entity has other entities as children). Is there is a generic way that I can write a copy routine to copy the collection of records and maintain the relationship between them.
For example I could have an entity of type house, that is represented by a record in a House table. That house could have rooms which a represented in the Room table. Those rooms could have furniture which is represented in the Furniture table.
Create tables (I am just creating fields for the IDs to keep this simple):
CREATE TABLE House
(
HouseID INT IDENTITY (1,1)
,Description nvarchar(32)
)
CREATE TABLE Room
(
RoomID INT IDENTITY (1,1)
,HouseID INT NOT NULL
,Description nvarchar(32)
)
CREATE TABLE Furniture
(
FurnitureID INT IDENTITY (1,1)
,RoomID INT NOT NULL
,Description nvarchar(32)
)
Fill Data:
SET Identity_Insert House ON
INSERT INTO House (HouseID,Description)
SELECT 1,'House1'
UNION
SELECT 2, 'House2'
SET Identity_Insert House OFF
SET Identity_Insert Room ON
INSERT INTO Room (RoomID,HouseID,Description)
SELECT 1,1,'Kitchen'
UNION
SELECT 2,1,'Bedroom1'
UNION
SELECT 3,1,'Bedroom2'
UNION
SELECT 4,2,'Kitchen'
UNION
SELECT 5,2,'Bathroom'
SET Identity_Insert Room OFF
SET Identity_Insert Furniture ON
INSERT INTO Furniture (FurnitureID,RoomID,Description)
SELECT 1,1,'Table'
UNION
SELECT 2,1,'Bookshelf'
UNION
SELECT 3,2,'Dresser'
UNION
SELECT 4,2,'Lamp'
UNION
SELECT 5,4,'Bed'
SET Identity_Insert Furniture OFF
Now I want to copy House with HouseID 1 and all the records from Room and Furniture as well. The copied records need to point to the ids of the copied parent records. My scenario is a lot more complex than this. I am hoping there is some standard solution/approach to this as I would imagine that this is a common problem.
What if I want to do the same with a set of houses instead of just 1?
Thanks in advance.
March 13, 2010 at 6:47 am
This sounds like yet another attempt to make a relational database into some sort of object-oriented framework. (Good luck with that, but be advised that you're not the first person to have that notion. It's been going on for years, without conspicuous success.) There is no way around the fact that in SQL that you are going to have to INSERT (copy) rows into their new homes one table at a time.
Some approaches that are purely within SQL:
1) Write a stored procedure that has INSERT INTO...SELECT FROM queries for each table containing some component of the house. Pass it a parameter containing the identifier for the house you want to copy and use that parameter in the WHERE clause of all your queries. If you make the input parameter a table variable, you could pass multiple "houses" to be copied all at once.
IF you define foreign-key constraints over all tables holding the various, you can read that structure and create dynamic SQL to copy rows from each table to another in the proper sequence. (If you know all the tables, you don't actually need this, but you will probably be adding tables in the future.) Foreign key constraints are SQL's way of keeping track of the relationships between tables.
2) Write a query joining all the tables and output your results as XML, then write a routine to read the XML and write them back into the new tables.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 13, 2010 at 10:23 am
Thanks for the feedback.
I am already doing #1. The main problem with this is that it becomes a pain to track the cross referencing between the parent in the source record and that of the destination records. I guess I can build temp x-ref tables for all the keys as I copy the structure, but I was hoping there was a better way. My example was simple but the number of tables involved in an operation like this can be significant.
I like the idea of XML and it is something that I will explore.
The Dixie Flatline (3/13/2010)
1) Write a stored procedure that has INSERT INTO...SELECT FROM queries for each table containing some component of the house. Pass it a parameter containing the identifier for the house you want to copy and use that parameter in the WHERE clause of all your queries. If you make the input parameter a table variable, you could pass multiple "houses" to be copied all at once.IF you define foreign-key constraints over all tables holding the various, you can read that structure and create dynamic SQL to copy rows from each table to another in the proper sequence. (If you know all the tables, you don't actually need this, but you will probably be adding tables in the future.) Foreign key constraints are SQL's way of keeping track of the relationships between tables.
2) Write a query joining all the tables and output your results as XML, then write a routine to read the XML and write them back into the new tables.
March 15, 2010 at 7:27 am
I hadn't realized you were going to tables with a different structure. I don't think you are going to get away from having cross reference tables, but they might only have to live on the server that generates the XML. Then your XML could include elements or attributes that denote the source table you are pulling from and the target table you are copying to. You could then code generically to generate an INSERT to the target table(s) specified within the XML.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply