Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Disco Patrick
Starting Member
18 Posts |
 Posted - 2009-08-04 : 05:42:12
|
| Imagine the scenario where users of a database can create complex objects that are made up of many entites, e.g.:BUILDING-----IDAddressBUILDING FLOOR-----IDBuildingIDCarpetColourROOM-----IDBuildingFloorIDFurnitureColourNow imagine you wanted to build an identical building next door to an existing building. It would be almost exactly the same as the first, but then once it had been built you were going to change the carpet colour on just one floor, and the furniture colour in just one room.Rather than recreating every floor and every room one by one, it would be great to be able to make a copy of the exisiting building, and then tweak it to your needs. This is what I am trying to achieve in my database.So far I have looked at doing this using temporary tables, using the SELECT * INTO #temp syntax. For example, I select all floors from the exisiting building, then insert them back into the table with the new BuildingID.I was wondering whether there was a best practice for creating copies of complex objects in this way, or if using temp tables is a good enough option. Does anyone have any experience of this?edited to add: I am currently trying to find out how to INSERT INTO the permanent table from the temp table. Also, I am planning on doing this whole operation in a stored procedure. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-04 : 08:23:23
|
| >>I am currently trying to find out how to INSERT INTO the permanent table from the temp tableYou would do this in the same way as you would from a permanent table. insert into <TargetTable> (colList) select (colList) from <SourceTable>You don't really need to use temp table(s) for this. sounds like you will need to first insert the new building with it's new address and, at the same time, get the ID of the new building. Then peform one insert into each supporting table based on selects from the same table where the builing ID equals the ID to copy. In the SELECT you would substitute the new ID for the old one. I would suggest that you not change the other attributes at that so that you can simply have a CopyBuiling SP. Then you can change the attributes as you would for any existing building.If the building ID is an identity column then use scope_identity() to get the new ID when you do the first insert.Be One with the OptimizerTG |
 |
|
|
|
|
|
|
|
Duplicating an object made of many entites