T-SQL (SS2K8) :: Deleting From Parent And All Its Child Tables With FK (no Delete On Cascade)
Apr 8, 2015
I have a database with many tables. I would like to Delete all rows with practiceID=55 from all Parents tables and all corresponding rows from its child tables. Tables are linked with foreign key constraints (but there is no ON DELETE CASCADE).
How to write a generalized code for removing rows from both parent and child tables.
Query should pick parent table one by one and delete rows with practiceID=55 and all corresponding rows from its child tables
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
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.
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link:
[URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase] FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID] INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId] WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase] FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID] INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID] WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2.
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date) declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date) insert @parent select 1, '1/1/2015', '12/31/2015' insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
I have two table both say A and B.If i insert a record in A that record should be inserted in B.If i delete a record in A that record should be deleted from B.Is that possible.If yes please tell me.Thankyou in advance,vishnu
Hi i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table. regards vinod.t.v
I am importing data from a paradox table and trying to clean it up. I have this query that finds all the child records that are not in the parent table.
Select MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
Now I'm trying to delete all those child records instead of just selecting them so I tried...
Delete MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
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
Below is my sample data of my table named "Groups"
with Groups as ( select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
[Code] ....
Expected result:
with ExpectedResult as ( select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all select '' as GroupName,'peoplesoft' as SubGroup union all select '' as GroupName,'mysql' as SubGroup union all
[Code] ....
How to achieve this parent-child has the same table.
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?
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
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.
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?
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.
Hi, I have a self-referencing table, something like Emlplyee-Manager. I want to implement the cascading delete in this table. So when I delete a manager than all the employees should be deleted at ANY level below the manager. I do not have DRI for the Foreignkey (manager)!!! My problem is that the trigger fires only one time (for the "sons") and not for the "grandsons" and below. I've used the following flags: - nested triggers (in "SQL Server Properies", "Server settings" tabsheet) - recursive triggers (database "Properties", "Options" tabsheet) I've combined this 2 flags: 1. nested flag reset and recursive flag reset 2. nested flag reset and recursive flag set or 3. nested flag set and recursive flag reset 4. nested flag set and recursive flag set The results are the following: -for case 1, 2 and 3 the same results: the trigger fires just for the first level (the sons are deleted but the grandsons remains there orphan) -for case 4 I have the following error message: "Maximum stored procedure nesting level exceeded (limit 32)" so I can't delete anything at all.
Any idea why the trigger doesn't fire for "grandsons" and below? Could be that this above mentioned flags doesn't works for self-referencing 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
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.
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.
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link: [URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- Query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase] FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID] INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId] WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase] FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID] INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID] WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2
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?
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
Request ID Parent ID Account Name Addresss 1452 1254789 Wendy's Atlanta Georgia 1453 1254789 Wendy's Norcross Georgia 1456 1254789 Waffle House Atlanta Georgia
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate) 2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate) 3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem: There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations. Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be along the lines of: a) Remove the Foreign Key references b) Copy the table structure, and make a new temp table, adding the column c) Copy the data over d) Add the FK relations, that used to be in the first table, to the new table e) Delete the original f) Done?
The questions are: a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'. b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant? c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months: -- Purge records in small portions to avoid locking production tables -- for a long time. The process takes longer, but can co-exist with -- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45 Incorrect syntax near 'Failed:'.
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative. Thanks
I am a C# programmer also acting as the dba for a SQL Server project. I have a table called Folder that has child records in a table called FolderItems. When a Folder is deleted I want the FolderItems to be deleted. I know how to accomplish via code, but I would prefer to have SQL Server perform the delete just in case a folder is deleted via SQL Management Studio or other method.
Is a trigger the way to go about this? I know how to create triggers For Delete, but I just wondering is this the best method to create a Cascade delete or does SQL have any built in "Cascade" delete features.
Helle people. The question is: I have two tables: Directories and SubDirectories. SubDirectories table has foreign key for DirectoryId. Now if I define this key with ON DELETE CASCADE attribute then in case of I delete a Directory record, all SubDirectory records will also be deleted. But if I have only one table Directories that have field named ParentId and in this field I save an Id of parent direcory. Can I define a parentId field as a foreign key to the same table and define it with ON DELETE CASCADE? Will it work too? Thanks
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;
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!
