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 |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2008-02-14 : 09:55:59
|
| Guys,I have a table where I store all the lookup tables in the database - upto 160 of them. In the same table I have a sequence column where I want to generate hierarchy fo the tables Consider the below exampleindextypelov table is child of depttypelov and doctypelov is child of indextypelovemptype is child of empcategory For the above example Tab seq_____________________depttypelov 1indextypelov 2doctypelov 3empcategory 1emptype 2Is there any way to update this column in multiple passes Step1: would be to update seq column to 1 Step2: would be to update seq column to 2 for all child tablesStep3: would be to update seq column to 3 for all child tables for only those subset of tables where seq = 2so on Is there any way to accomplish?Any suggestions and inputs would helpThanks |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-02-14 : 09:59:06
|
| There is no indication in table structure as to which record is parent/child of another record. How are we supposed to write UPDATE then?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 2008-02-14 : 10:48:01
|
| Sorry if my post was not clear enoughThe whole idea was not refer the create ddl script to find out the relationship. Is there any way to build the seq column from system objects such as information_schema.CONSTRAINT_COLUMN_USAGE, sysforeignkeys.Thanks |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2008-02-14 : 11:06:13
|
| Firstly, I'd be interested to hear about what you are doing with the "table of tables"?Secondly, there are several way to model graphs in sql (relational).-Adjacency model (transitive closure): best for Acyclic Directed Reconvergent graphs-Adjacency model (without transitive closure): best for Acyclic Directed Non-reconvergent graphs with shallow depth-Nested Set model: best for Acyclic Directed Non-reconvergent graphs with unknown depth that are non-temporal in nature-Materialized Path model: best for Acyclic Directed Non-reconvergent graphs with unknown depth that are temporal in nature(YMMV - these are some very general guidelines I use as a starting point)It is unclear to me which model you are going after. It certainly seems that you are dealing with reconvergent data (there could be multiple paths between at least one pair of nodes) and possibly undirected data (permits travel in both directions).What do you intend your sequence number to mean? What does seq=2 for indextypelov mean exactly? Couldn't the indegree of indextypelov be greater than 1? In which case, which depth are you trying to represent with that sequence number?I think you need to better define your use case if you want to get some solid help. Aside from that, yes, you can interrogate system objects to find out about foreign keys and such.Jayto here knows when |
|
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 2008-02-14 : 16:57:30
|
| We currently have master database in which all lookup tables have identity property and 4 production databases where we have the same lookup tables where the columns do not have identity property.The idea is to push all the look up data from Master database to 4 production databases and have id values consistent across 4 production databases. Inorder to accomplish this we generate insert statements for inserts and updates on the Master database look up tables and run it against the production database every night.For this process to be successful we need insert statements to run in right order and hence need for seq column in the table to store the parent child heirarcy. I also realise the updates can also be accomplished by replication, but the idea is to a consistent solution which database platform independent.Any suggestions and inputs to generate values for sequence column would helpThanks |
|
|
|
|
|
|
Parent Child tables hierarchy