Design Question - Parent Child Tables && Identity Columns
I don't know if this is the right forum but...
In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Design Prob:parent/child W Different Headers: Sorting, Non Scrolling Header Etc
Report needs to display as follows Parent col1,col2,col3... val1,val2,val3 expandable section child1 colA,colB,colC valA,valB,valC child2 colA... .... repeat Am currently implementing this as nested list controls with textboxes due to the fact that the headers for the parent and child differ. (By header I mean the column labels) 1. is there a better way of doing this as I much prefer working with tables/matrix controls? 2. how can I stop the header from scrolling - this is possible with tab/mtx controls. The alternative is to repeat the headers all down the page which wastes space and looks messy 3. How can I apply 'interactive sorting' to the parent columns? Thanks
View Replies !
View Related
Parent Child Tables
In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?
View Replies !
View Related
VB 2005 Express: Unable To Reference Parent And Child Columns Of A DataRelation
From material I have read I should be able to reference columns in a datarelation by Parent.ColumnName Child.ColumnName The code below is Ok until I use the Child reference Dim tbl0102reltn As New DataRelation("tbl012tbl02", tbl01Cols, tbl02Cols) dataset01.Relations.Add(tbl012tbl02reltn) '=== Add the new Forecast Column to table01 (The display table) Dim FcstTotVol As DataColumn = New DataColumn("FcstTotVol") 'declaring a column named Name FcstTotVol.DataType = System.Type.GetType("System.Int16") FcstTotVol.Expression = "Child(tbl012tbl02reltn).TotVol" The last line throws an error "cannot interpret token Child() at position 1" If I replace the last Line as FcstTotVol.Expression = "tbl0102reltn.Childtable.Columns(10)" i receive the error "The expression contains undefined function call tbl0102reltn.Childtable.Columns()." I am unable to find away to feed the expression required for the new column. Can anyone assit. Thanks, Jim
View Replies !
View Related
Tables In Parent-Child Order
Greetings,I just wanna know if anyone can tell me how to get all user definedtables in parent-then-child manner. I mean all the parents should belisted first and then childs.I dont think there is any direct way to do this, but i am not able toform any sort of query to achieve this.Any help will be greatly appreciated.TIA
View Replies !
View Related
Check Constraint Across Parent-child Tables
Hi,DDLs and DMLs:create table #job (jobID int identity(1,1) primary key, jobNamevarchar(25) unique not null, jobEndDate dateTime, jobComplete bitdefault(0), check (([JobEndDate] is null and [JobComplete] = 0) OR([JobEndDate] is not null and [JobComplete] = 1)));Q1 with check constraint:sample dagtainsert into #job (jobName)values('first job');transaction Aupdate #jobset jobEndDate = '12/19/2003', JOBCOMPLETE=1where jobID = 3;RESULTSET/STATUS = Successupdate #jobset jobEndDate = NULL, JOBCOMPLETE=0where jobID = 3;RESULTSET/STATUS = Successtransaction Cupdate #jobset jobEndDate = '12/19/2003'where jobID = 3;RESULTSET/STATUS = Failurehow come check constraint can't set a value which is preset in thecheck constraint? If it's the way how it works with MS SQL Server2000, well, IMHO, it's limiting because the above transaction C is avalid one. Or maybe check constraint is not fit for this purpose?Maybe, it doesn't make much sense for me to go into Q2 but I'll try-- create job's child table, taskcreate table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique not null, taskEndDate dateTime, taskComplete bitdefault(0), jobID int not null references #job (jobID));-- skip check constraint for taskEndDate and taskComplete for nowNow, the Business Rule says,1) if all tasks are complete then automatically set jobComplete forthe #job table to yes;2) the jobEndDate in the #job table must be >= the last/MaxtaskEndDateI tend to think trigger would slow down data update quite a bit, so,try to stay away for this purpose if possible.Always appreciate your idea.
View Replies !
View Related
Insert Data Into Parent And Then Child Tables
hello - i am trying to figure out how i can create an SSIS package to insert into multiple tables. After the first insert, I want to take the ID created (an Identity column) and then use that to insert into other associated (foreign key) tables. For example, I have a table Users. The primary key is an Identity column. Once the SSIS insert is complete, the bulk load of new users has an identity ID value for each row. What I want to do, during the same SSIS package, is to take each row as it is inserted and add rows to other tables. Like, UserDepartment - it has a foreign key for the user id and a foreign key to the department being added. And, as part of this I will need to get the latest ID value and possibly some other values and store them in variables. I looked at multi-cast, but I don't know/think that this will work for me. does anyone know of a good example or article like this? thanks - will
View Replies !
View Related
SSIS And Parent-Child Relationship Tables
Supose I have two records in a parent-child relationsuip (actually I have many more such records). Does SSIS offer any support ask for inserting one record into the parent table, the other into the child table, and updating the foreign key of the child to point to the parent? TIA, Barkingdog P.S. I have to do just this as part of the datawarehouse test I'm running. Seems like a common task but I don't recall anything in SSIS addressing the issue.
View Replies !
View Related
Inserting Data Into Two Tables With Parent-child Relationship
I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first? If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database. The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.
View Replies !
View Related
Copy Parent/child Rows To Same Respective Tables
My Question: Does anyone know of a decent way (i.e. I do not want to loop to insert each row and check the SCOPE_IDENTITY() field or anything like that) to copy parent/child rows to their same respective table besides using the method I have listed below (my manager does not really like the idea of the "PreviousID" field)? More details are listed below. My Table Situation: I have a parent table and a child table. Both tables have an identity column as the primary key. The relationship between the tables is established using the parent table's primary key to the column in the child table that stores the relationship. The identity column in both tables is the only column that is unique in the tables. Sample Data (made up and simplified for visualization purposes): ParentTable - "Items" ID ItemCategory Price 1,T-Shirt,$20 2,Blue Jeans,$50 3,T-Shirt,$40 ChildTable - "Components ID ItemID Component 1,1,Fabric 2,1,ScreenPrinting 3,2,Fabric 4,2,Zipper 5,3,ThickFabric 6,3,ScreenPrinting 7,3,Elastic My Need: I need to make a copy of the records (keeping the parent/child relationship intact) to the same table as the source records. For example, in my data example above, I may need to make a copy of all the "T-Shirt" items and their child records. As the parent records are copied, they will be assigned new keys since the primary key is an identity. Obviously, this new key needs to be used when creating the child records, but I need to somehow associate this new key to the new child records. Possible Solution: I know this can be achieved by adding another column to the parent table to store the "PreviousID" (INT NULL). Using this new field, when I want to copy the "T-Shirt" items, I would insert the new records and store the ID of source records (i.e. the identity value of the row that was copioed would be stored in this "PreviousID" field). Once the parent record has been copied, I could then insert the child records, and I could join on the "PreviousID" field to get to the new ID to use for inserting the copies of the child records. Thanks for reading this and for any help offered.
View Replies !
View Related
Database Design Selecting Multiple Different Child Tables
I want to have a linking table say for example we call this a claim. Based on the claim number you need to relate to one of say 6 different types of claims. The types of claims related to their own individual parent table. (individual because each type of claim tracks completely different information) does anyone have an idea on how to set this up? Sample Structure table = Claim Field 1 = ClaimTypeA_ID Field 2 = ClaimTypeB_ID Field 3 = ClaimTypeC_ID Field 4 = ClaimTypeD_ID Field 5 = ClaimTypeE_ID Field 6 = ClaimTypeF_ID The six field relate to the 6 different tables ID. If I do this how do I store the data? put 0's in each of the claim types that are not used??? Any suggestions would be appreciated.
View Replies !
View Related
Question On Design With Identity Columns
Normally when I have a "Many-toMany" or linkage table where the primary key consists of a foreign key from two different tables, I do not bother to make a separate identiy column instead.Does anyone see a reason why an identity column would be more or less desireable ? For exampleTable Person PK - PersonIDTable Car PK - CarIDTable PersonCar PK (PersonID, CarID)Or would it be better to make an Identity Column such as PersonCarID so then the table would look like the following:Table PersonCarPK - (PersonCarID)FK - PersonIDFK - CarIDCreate Unique Constraint on Person and CarIDAny feedback is appreciated
View Replies !
View Related
Bcp In Child Before Parent
Huh? I've got good RI data...BUT..a developer loaded the tables in alpha table order.... Such that the child loaded BEFORE the parent.... Huh? Got a test being set up now to mess with the child file to add a key that doesn't exist in the parent... But Why is this allowed? In DB2 you can specify LOAD DATA REPLACE NO CHECK.... On the load card...you then need to run a check after to verify the data... Is that what's going on? Is there such a utility in SQL Server to run a check post load? I'm confused.... Any comments appreciated. Thanks Brett 8-)
View Replies !
View Related
Multiple Tables, Inserts, Identity Columns And Database Integrity
Hi all, I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not. What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'. What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row.... The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order.... How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring? Thanks, appreciate it.
View Replies !
View Related
Insert Into Parent/child
hi, i have two tables i want the identity value of the parent table to be inserted into the chile table here is my code,but i don't know why it isn't working ! protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id; thanks in advance :) connection.Open(); commandd.ExecuteNonQuery(); } }
View Replies !
View Related
Parent Child Transversing
I have a parent/child relationship in a relational database broken out like this: Table Name: categories[category_id] int (primary_key NOT NULL),[category_name] varchar(50),[parent_fk] int The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this: select category_id, category, parent_fk from categories where category_id not in ( select parent_fk from categories) Each bottom-level category has a count attached to it. The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts). My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything). Thanks!
View Replies !
View Related
Parent / Child Query
Hi there I have come accross this problem. I want to get all the childs which are not parents (last node on the model tree, so that I will end up with the actual models and not model lines) I use this SQL statement but the result in null. select * from CC_MODEL where ccactiveflag = 1 and ccModelId NOT IN ( select distinct ccParentId from CC_MODEL where ccactiveflag = 1 ) Am I missing something or it will just never work. I have tried this query and it worked. select * from CC_MODEL where ccactiveflag = 1 and ccModelId NOT IN (10,11) where numbers in brackets are some of the results from the query select distinct ccParentId from CC_MODEL where ccactiveflag = 1 Any ideas??? Thanks
View Replies !
View Related
Parent Child Dimension
I am trying to create a Local Cube with a Parent Child Dimension.I have used the following Create Cube Clause.This works fine.But I do not have a clue as to how to include the Level Naming Template.Any help will be greatly appreciated. CreateCubeStr1 ="CREATECUBE=CREATE CUBE LocalCube(" CreateCubeStr1 =CreateCubeStr1 & "DIMENSION [Employees] DIMENSION_STRUCTURE PARENT_CHILD,LEVEL [All Employees] TYPE ALL " CreateCubeStr1 =CreateCubeStr1 & ",MEASURE [Org Salary] Function Sum Format '#.#' )"
View Replies !
View Related
Parent-Child Dimension
Is there any way to combine a star schema dimension with a parent-child dimension? I want to start at the top of our organization and drill down to the level of an individual order. By using the Position dimension, I can only see units by the person responsible for selling them, I cannot drill down into the territories, customer and finally the order. I've tried to put the PositionID in the Division dimension, this works good except it begins at the sales rep level. Any thoughts or ideas are greatly appreciated. Thanks!
View Replies !
View Related
Hierarchy – Parent And Child
I am designing a table to represent data in hierarchy structure, I use id and parent id to represent the data in hierarchy form: Id | parent_id ---+----------- 1 | 0 2 | 0 3 | 0 4 | 1 5 | 1 6 | 4 7 | 4 8 | 7 9 | 7 This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id. Is this a good relational database design ? kindly suggest. Id | parent_id | root_id ---+-----------+--------- 1 | 0 | 2 | 0 | 3 | 0 | 4 | 1 | 1 5 | 1 | 1 6 | 4 | 1 7 | 4 | 1 8 | 7 | 1 9 | 7 | 1 10 | 2 | 2 11 | 2 | 2 12 | 10 | 2 13 | 10 | 2 Rgds Vijay
View Replies !
View Related
Parent Child Problem In Sql
I want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data: ParentID ChildID ------------------------- 10 8 8 6 8 7 7 2 7 5 5 1 5 2 6 2 child of parent if input 10 8 7 6 2 2 5 1 (2) can be neglected input 7 2 5 1 (2) can be neglected input 8 7 2 5 (2) can be neglected Plz help we to write the sql statements Thanks
View Replies !
View Related
Insert Trigger For Parent/Child
I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger. Thanks, James
View Replies !
View Related
Selecting Parent / Child Relationships
Hello all, I'm having a real hard time trying to figure this one out. I'm trying to create a sql query that selects both the parent name and it's children, but it's got to loop through all the record sets to populate a drop down as an end result. I think I thought this out correctly: I have 2 tables category relationship tbl category cat_id //auto int cat_name // varchar relationship r_id // auto int parent_id // int child_id // int both the parent_id and child_id are associated with the cat_id in my category table I could have 1cars // this is parent 2 audi 3 bmw 4 chevy Table data example r_id parent_id child_id **************************** 1 1 15 2 1 16 3 1 17 4 2 55 5 2 56 etc... I want to select both the parent cat_name from category and also select the child cat_name where the parent_id = # I can do it manaully like this select cat_name, cat_id, parent_id , child_id from category, relationships where child_id = cat_id and parent_id = 1 what is the best way to loop through all the parent ids to find child category? Could this be done in a stored procedure? thanks in advance.
View Replies !
View Related
Parent / Child / Grandchild Report
Hi guys, I hope you can help me creating a report. I'm trying to create a Parent / Child / Grandchild report. Looking something like this: Region1 SalesmanA Invoice1 Invoice4 Invoice7 SalesmanB Invoice2 Invoice8 SalesmanD Invoice12 Region1 SalesmanC Invoice3 Invoice5 SalesmanE Invoice6 Invoice9 SalesmanF Invoice10 Invoice11 Invoice13 I have searched for an solution for some time now but I can't find a good solution. Hope you can help me. Thanks.
View Replies !
View Related
Flattening Parent Child, An Issue, Please Help
Hello Experts,Here is the code to flatten a PC hierarchy into a level based table. Itworks fine.SELECTt1.TASK_ID AS TASK_LV1,t2.TASK_ID AS TASK_LV2,t3.TASK_ID AS TASK_LV3,t4.TASK_ID AS TASK_LV4,t5.TASK_ID AS TASK_LV5FROM dbo.Project t1 LEFT OUTER JOINdbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_IDAND t2.WBS_LEVEL = 2 LEFT OUTER JOINdbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_IDAND t3.WBS_LEVEL = 3 LEFT OUTER JOINdbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_IDAND t4.WBS_LEVEL = 4 LEFT OUTER JOINdbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_IDAND t5.WBS_LEVEL = 5How do modify the code to work for any level rather than hard codingthe level up to "5"?Please help.Thanks.Soumya
View Replies !
View Related
Parent-Child View Without Using Cursors
I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against. For example: Parent { ID_PK, Name, PhoneNum, Address } Child { ID_PK, ParentID_FK, Name } The view would return a dataset like this: Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward with an unknown number of children for each parent. Then I would like to be able to query against this view with something like this: SELECT * FROM FamilyView Where Child2 = 'Peter' I have no idea how to write the SQL for this View. Is it possible? Is this possible without using a cursor? Thanks for any advice you all can give me. Brian
View Replies !
View Related
Cascade Delete From Child To Parent
Hi All, I have the following db design: a Primary table say CityTable CityId Pk CityName SectorTable SectorID CityID FK to CityTable SectorName MuncipalTable MunicipallD CityID FK to CityTable MuncipalName CantonmentTable CantonmentlD CityID FK to CityTable CantonmentName The CityId can spread across multiple tables. I would like to delete the record from CityTable if all references of CityId have been removed. For e.g CityTable CityId: 1, 2 CityName: Pune, Mumbai SectorTable SectorID: 1a, 2a, 3a CityID: 1, 2, 1 SectorName: Sec1, Sec2, Sec3 MuncipalTable MunicipallD: 1c, 2c, 3c CityID: 2, 1, 1 MuncipalName: abc, def, hij So If I delete SectorID 1a, the CityTable should not delete its record since SectorID 2a and MuncipalID 2c and 3c are still referencing it. But If delete all the records from SectorTable and MuncipalTable referencing CityID 1 then the record from CityTable should get deleted. How can I achieve this using a SP? Please help. Thanks & regards Sunil
View Replies !
View Related
Nested Parent-Child Packages
I have a fairly simple SSIS project that has nested parent-child packages. I am trying to find the best way to manage the connections strings so as to make the package portable across machines and environments. Currently there is one "master" package which calls 6 child packages. 1 of these child package calls 3 child packages of its own. For the database connections, I've settled on creating a standardized .dtsConfig file for each server/login. This is a relatively small number (intially 8) that I don't expect to grow much. I've taken a different approach for the file-system connections used by Execute Package components that call the child packages. For each package that has child packages, I store all the connection strings (paths) to the child packages in a single .dtsConfig file. This works well for the top-level "master" package where I can pass in the .dtsConfig file (that has the paths to the child packages) as a run-time option in the Execute Package Utility. However, this approach seems to fall apart for the 2nd generation package that in turn call 3rd generation packages because I don't know how to get the .dtsConfig file (with the 3rd generation .dtsx package paths) path to this downstream dtsx package. Though I'm sure there are others, the only two solutionsI can think of now are (a)don't nest packages beyond 1 parent/child relationship -- not really an option or (b)Store the path of .dtsConfig files for each .dtsx package as an environment variable on each machine. This option is unappealing because it would require adding an environment variable for every .dtsx package that has child packages. I don't think it would take long for this to grow into a large number, that would make managing environment variables cumbersome. So far my experience with SSIS has been that there was a simple solution for each scenario I had. So this hoop jumping I'm going through seems to indicate I am just missing something. Is there a better way I am just not getting?
View Replies !
View Related
Parent Child Package With Expressions
Hi there, sorry if the message seems a bit garbled i cannot see the textbox properly with ie7/ff anyways. the situation. childpackage contains a loop-container. in there i use an ole-db-source with a variable based on an expression. ie. "select * from foo where bar=" + len(@[bar]) == 0 ? "initial" : @[bar] + " and etc=1" where bar is the variable that is set by the loop. this works great. however i need to call this package several times, only the expression is a tad different. so i need a scripttask that sets the expression correctly, then i call the childpackage and map the current-expression to the expression in childpackage. how do i do that? or am i doing it wrong? my script-task looks something like: dts.Variable("theVar").Expression = " ""select * from foo where bar="" + len(@[bar]) == 0 ? ""initial"" : @[bar] + "" and etc=1"" " in the childpackage i have a package-conf that maps thevar to thequery with target-object expression.
View Replies !
View Related
Debugging Parent / Child Packages
Hi there, I have a Business Intelligence project containing 4 packages. One of the packages is a parent package that just runs the other 3 packages sequentially (passing in variables via Package Configurations). When I set the properties of the Execute Package tasks in the parent package, it seems that I can only select SQL Server or File System locations for the child packages. What I want to do is for the parent to run the packages in the Visual Studio Project so that I can debug the whole process. I don't want to be bothered deploying the child packages so that I can run them from the parent, at least not while I am debugging the whole process. How do I get the children to run within Visual Studio as well as the parent? I can't for the life of me find anything on the MSDN about this. Many thanks in advance. Richard F
View Replies !
View Related
Parent-Child Package Configurations
Hi All, May be I am doing something wrong over here, but I have been trying in vain to test out a simple scenario where I can use my Parent Package configurations in my Child package. I have two packages ready and can someone please walk me through this process. Appreciate all help Thanks
View Replies !
View Related
Parent-&&>Child Package Editing Bug?
Before I submit this to connect, I'd like to see if someone can verify: Create a package that executes another package. When you start debuging (build) the parent package it opens up the child package and executes. Once you're done and stop debugging, the child package remains open. When you make edits to the child package and save and close, none of the edits are saved. You must close the package and re-open it before you make your edits...
View Replies !
View Related
Insert Parent Child Records...
Hello, We have a complex functionality of migrating data from a single record into multiple parent child tables. To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table. Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails. As of now, we are using the following approach for achieving this functionality. 1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset. 2. Use the recordset as the source to a foreachloop , using foreachADO enumerator 3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :) Thanks, Satya
View Replies !
View Related
Parent-Child Variable Issue
Hello, I'm having problem to pass variable from a package to a another one. I having 2 packages: the parent (parent.dtsx) use a Execute Package Task to execute the child package (child.dtsx). In the parent package, I have a variable named var1 (data type String) with value=test In my child package, I having a script task using the variable var1 to make a MsgBox (var1 is in readonly in the script): MsgBox(CStr(Dts.Variables("var1").Value)) In this case, I having an error (cause the variable may no exist) but i can see the value of the variable in the MessageBox and the execute package task fail because of this error. After I tried this solution: (Found in this Forum) In the child package, I create the variable var1 (same type, no value). Then in the control flow, I right click on the background and select "package configurations". I enable package configurations. Then I add a new one. I change the configuration type to "parent package variable." Then, in the specify configuration settings entry, I enter the name of the variable in the parent package. On the following screen, I select the "value" property of the variable created in this package (Child package). At last, I give the configuration a name and hit finish. If I execute my parent package, the result is no value in the MessageBox. I also tried the same thing (with the package configuration) to pass a a variable with the connection string from a package to the connection of an another one (I set the connection string of the connection) and it doesn't work. What did I miss ? Thanks in advance for your help. Regards
View Replies !
View Related
Parent / Child Variable Frustration
Can't figure out why either of the two setups I've tried on this aren't working. Any help would be appreciated. Business need: Clients upload six MS Access (mdb) data files to server, data files loaded into SS2005 database. Because of legacy concerns, even though each client's six files contain the same data, the individual file names are different. Setup: Package 1 searches the client's ftp folder for new file. If found, contents are unzipped and moved to client's staging folder while uploaded files are archived. Package 1 then calls package 2, passing to it the end date for this particular file, along with client-specific data (e.g. the variables used in the connection strings for the six mdb files and package 2's log file, which appends the client name to a constant file name). Package 1 uses a different config file for each client, and the package is run with that applicable config file. Problem: For some reason, the parent variables are not being passed properly to Package 2. If I run it with the first client, everything works fine. If I run it with the second, Package 1 works fine (finds the file, does the naming, etc.), but Package 2 is using the default variable values that are coded in the Value column in the Variables pane. The first time I tried it, the first step of Package 2 was a script step whose only function was to set Package 2's variables to the values in Package 1: Dts.Variables("package2variablename").Value = Dts.Variables("package1variablename").Value This didn't work at all. The log file's name still used the default column value instead of what Package 1 had, and the package was trying to find the wrong file name in the staging directory. The second time, I set Package 2 up to use configurations and have one parent package entry for each variable I want to set. This worked slightly better, as while there was still a log file with the Value-column value in it, there was also one for the client-specific name. However, the package still failed as Package 2 is still looking for the wrong file name. Obviously there's something in there I haven't set correctly, but I'm at a loss as to what it is.
View Replies !
View Related
Problem Of Parent-Child Package With ASP.Net
I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.) I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start. Could somebody explain why this is happening? any suggestions/ similar experiences? Regards Saurabh
View Replies !
View Related
Parent-Child Hierarchy But Reversed(!)
Hi! I have a report that uses a parent-child hierarchy in a table. It is hidden with the toggle set to itself, and that works perfectly. However, instead of having it look like this(simple example): + Profit After the + has been clicked: -Profit +Sales +Cost I would like it to look like this: +Sales +Cost -Profit Is this possible with when using a parent-child hierarchy? http://ssasfreak.spaces.live.com/
View Replies !
View Related
Parent Child Subtotals / Reverse
Hi I'm working with parent-child hierarchies. My source is AS2005. What I have now is: + parent 1 child 1.1 child 1.2 child 1.3 + parent 2 child 2.1 child 2.2 What I would like is: child 1.1 child 1.2 child 1.3 + parent 1 child 2.1 child 2.2 + parent 2 This would allow me to simulate subtotals for the hierarchy in a more financially intuitive way. I call it a simulutation because the optimal solution would be to use a normal group for the parent-child hierarchy and then apply group-headers and -footers. However, this does not seem possible and the documentation I've found also describes parent-child hierarchies wrt. the details-group only. I have thought sorting the data - perhaps on levels, but I haven't got anything to work yet. One of the things that annoys me the most when developing in RS2005 is that it's very hard to debug the layout. For instance, when I apply sorting - first ascending then descending - and the result is the same I get no messages telling me what's wrong (which there obviously must be...). Best regards Mikkel
View Replies !
View Related
Parent-Child Report Problem
Hi, I have a report which I have created 3 or 4 child reports from. If I make a change to the format of an object within the parent report (e.g. the colour of a text box) then the change appears on the child reports as I would expect. The problem is that if I make a change to the properties of the report (e.g. the margins or the page size) then the change is NOT picked up in the child reports. Does anybody have any solution to this annoying problem. Regards, James
View Replies !
View Related
Storedproc For Parent Child Gridview
Hi I have a search page which contains 4 fields.Giving input to anyone of the field should display the result in Parent Gridview.Parent Gridview has button in it .when i click on the button child Gridview should display related refund details of customer in parent Gridview. let us think i have two tables like Customer and refunddetails. Parent Gridview should display Customer details,Child should display corresponding customers refund details. I need two storedprocs for binding to both Gridviews. i have first stored proc for Gridview1 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[MyProc] (@val1 varchar(255), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) --@out smallint OUTPUT AS select * from customer where ((@val1 IS NULL) or (name = @val1)) AND ((@val2 IS NULL) or(ssn = @val2)) AND ((@val3 IS NULL) or(accountnumber = @val3)) AND ((@val4 IS NULL) or(phonenumber = @val4)) now i need to capture the @val1 from storedproc1 and using that value retrieve the remaining values in refund table. name is common in both the tables. i need this because user can search the value using ssn or accountnumber or phonenumber or name.it is not required that user serches using name.Name textbox can be null. so please someone help me.
View Replies !
View Related
Assign Single ID To Parent And Child Doc.
Hi, i have 2 Tables. i am saying emails in table1 and also if there is any attachments there i saved them also in the same table but with differet ID, and in another table i am saving the relationship of both documents. Now i have defined another table in which i want to store a single New ID generated by the new Table to email document and it's attachment document.. what i am now doing i just create a SP, and it's take the first record from the main table and check if there is attachments , if no then it's just assign a new ID and save the document id into newly created table, but if there is attachement then it's go to relationship table and returen me the child id on behalf of parent ID and then in the newly created table it save the child docment id with the same id as the parent id has already been saved. i m doing like this SELECT all the emails those has attachments.. Insert Into newlyCreated Table (ID,doc)values(1,1) then SELECT doc from Relationship table where Maintable doc=@doc Insert into newlycreated tAble(ID,doc)values(1,2) and so on .. and if i have more then one attachement then i have to use the loop.. what i m looking , is there any short way to do this all, bcoz it's taks ages to do.. is there any single line query that i can use to get the child doc id rather then going into loops .. Thanks ....
View Replies !
View Related
Returning Parent / Child Rows
Hi, I have the following table JobID JobType JobName ParentJobID etc... 12345 Disconnection MyJob1 98475 New Pilot MyJob2 12345 85746 Streetlight MyJob3 98475 etc I need a query that returns all the jobs that relate to a specific job e.g if I pick 12345 I get the following: 12345 Disconnection MyJob1 98475 New Pilot MyJob2 85746 Streetlight MyJob3 As they are all related records. Can anyone help Thanks
View Replies !
View Related
Delete Child If Parent Deleted
dear friend,, i have two table.in my first table id is primarykey and in my second table id if foreign key. so my need i have to use one query to delete the primary key table values. so if i am deleting one id in primary key table the child in the second table has to be deleted automatically.if parent get deleted the child should get deleted automatically. so plese help me to do this please give me sample query please
View Replies !
View Related
Deletion Of Parent And Child Table
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys. Rahul Arora 07 Batch NCCE Israna, ###################### IMPOSSIBLE = I+M+POSSIBLE
View Replies !
View Related
Making Sure Every Parent Has A Child - Constraints
hello there, I think I might know the answer to this, but I wanted to see if any one has come up with a slick idea for enforcing this relationship. If I have two tables and one is dependent on the other (parent-child relationship), how can I enforce that every parent record has a corresponding child record? Here is a code example USE tempdb GO CREATE TABLE dbo.Parent ( ParentId int NOT NULL ) ALTER TABLE dbo.Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentId) CREATE TABLE dbo.Child ( ParentId int NOT NULL ,ChildId int NOT NULL ) ALTER TABLE dbo.Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ParentId, ChildId) ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentId) REFERENCES dbo.Parent(ParentId) INSERT INTO dbo.Parent VALUES (1) INSERT INTO dbo.Child VALUES (1,1) INSERT INTO dbo.Parent VALUES (2) INSERT INTO dbo.Child VALUES (2,2) INSERT INTO dbo.Child VALUES (2,1) INSERT INTO dbo.Parent VALUES (3) SELECT p.ParentId, 'I SHOULD HAVE A DEPENDENT RECORD' FROM dbo.Parent p LEFT JOIN dbo.Child c ON p.ParentId = c.ParentId WHERE c.ParentId IS NULL ParentId 3 should have a child record associated with it. I am assuming that these are my choices: 1) code all inserts to the parent table along with a insert to the child table and wrap those in a transaction 2) place a trigger for insert on the parent table that ensures that the child table is populated after data for the parent. Here is the gotcha, we will be using a middle-tier data access layer (nhibernate or dlink) so .NET application developers will be creating the data modifications at the transactional level. Also there might be several ongoing ETLs that populate this schema as well, so multiple points of entry and seperate code blocks. I don't want to hide business logic within triggers. I assume that all our coders are competent and could enforce this properly via code, but I know that mistakes happen. Has any one come across this situation and have a solution for enforcing the integrity of the schema with constraints?
View Replies !
View Related
Parent And Child Records From Same Table
Hi i have a table named categorymaster categoryid catname parentid 1 Boxing 0 2 Tennis 0 3 Basketball 0 4 MayWeather 1 5 Tyson 1 6 Clinton woods 1 7 RogerFederer 2 8 Micheal 3 9 Hingis 2 so if i give input say categoryid=1[This falls under main category-boxing] i need to get result as 1 boxing [main category] 4 mayweather [sub category] 5 tyson [sub category] 6 clinton woods [sub category] if i give categoryid=5[Note:Tyson] result should be as 1 boxing [main category] 5 tyson [sub category] hope u can get my question Thanks in advance
View Replies !
View Related
|
TRACKER
