April 28, 2010 at 9:37 am
My devs removed one table and created three new tables with foreign keys.
I have a job that runs to prune records based on date.
The job syntax is:
@DaysToRetainSMALLINT
AS
BEGIN
DECLARE@CUTOFFDATEDATETIME
SELECT@CUTOFFDATE = DATEADD(dd, -(@DaysToRetain), GETDATE())
DELETE
FROMLPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE
WHERECREATEDDATE < @CUTOFFDATE
END
I don't have this table anymore. It has been replaced by three new tables that all have foreign keys.
Basically it's Parent table A FK to child B
And
Parent table A FK to child C
Can you guys give me the base syntax to start with?
April 28, 2010 at 10:13 am
how are the foreign keys setup? If they are setup as Cascade Deletes, then just deleting the records from Table A will also delete the records in Table B and Table C.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 28, 2010 at 11:37 am
Without more details, it's kind of hard, but basically, assuming TableA is the parent and TableB & TableC have foreign key relationships to the table, and that the column that relates the tables is LtKey, something along these lines should work:
DELETE TableB
FROM LPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE lt
WHERE lt.CREATEDDATE < @CUTOFFDATE
AND lt.LtKey = TableB.LtKey
DELETE TableC
FROM LPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE lt
WHERE lt.CREATEDDATE < @CUTOFFDATE
AND lt.LtKey= TableC.LtKey
DELETE LPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE lt
WHERE lt.CREATEDDATE < @CUTOFFDATE
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2010 at 1:28 pm
Thanks Grant
Is there a way to put all thre into one statement?
April 28, 2010 at 1:31 pm
krypto69 (4/28/2010)
Thanks GrantIs there a way to put all thre into one statement?
Nope. INSERT/DELETE/UPDATE operations are done one table at a time.
You can wrap it all in one transaction, but be careful that it's not locking too much or possibly locking in an order different than the inserts occur because you could get deadlocks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2010 at 5:35 am
Why not to use cascade?
By using Cascade, If you delete from Table A, Record(s) from Table B and Table C will bve automatically deleted.
April 29, 2010 at 6:43 am
I thought about cascade
How do I use cascade in my senario?
April 29, 2010 at 7:01 am
While defining relationships, there is an option of "Delete Rule". You can set from there.
Considering Table A as Parent and Table B and Table C are chile tables with Foreign Key from Table A.
If cascading option is set, when you delete record from Table A, record from Table B w.r.t the foreign key from Table A will be automaticall deleted. Same is done on Table C.
So, You run Only
Delete from [TableA] where ID = 121
And Records from Table B and Table C for FKID = 121 will be deleted automatically.
I hope you got the point.
April 29, 2010 at 7:19 am
Grant
Im trying to use your statement:
But when I run the below code I get the error:
"Must declare the scalar variable "@CUTOFFDATE".
declare @DaysToRetainSMALLINT
DECLARE @cutoffdate DATE
SET @daystoretain = '1'
SET @cutoffdate = '12/01/2010'
SELECT@CUTOFFDATE = DATEADD(dd, -(@DaysToRetain), GETDATE())
DELETE dbo.LPBSecurity_LoginActivity
--FROMLPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE
FROMLPB_ASPNETDB.DBO.LPBSECURITY_USERCLIENTMETADATA UC
WHEREUC.CREATEDDATE < @CUTOFFDATE
AND UC.userclientmetadataid = dbo.LPBSecurity_LoginActivity.UserClientMetaDataId
-----------------------------------------------------
GO
DELETE dbo.LPBSecurity_UserClientSignature
--FROMLPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE
FROMLPB_ASPNETDB.DBO.LPBSECURITY_USERCLIENTMETADATA UC
WHEREUC.CREATEDDATE < @CUTOFFDATE
AND UC.userclientmetadataid = dbo.LPBSecurity_UserClientSignature.UserClientMetaDataId
GO
DELETE LPB_ASPNETDB.DBO.LPBSecurity_UserClientMetaData UC
WHERE createddate < @CUTOFFDATE
April 29, 2010 at 7:21 am
Atif Sheikh
that sounds like the easiest way for me to go...now how to enable cascade deletes?
April 29, 2010 at 7:28 am
In SSMS, expand table B and go to keys. Right-Click FK and select modify. Under Table Designer, expand INSERT And UPDATE Specific, Unde Delete Rule Select Cascade. Then Save. Repeat for Table C
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 29, 2010 at 7:52 am
krypto69 (4/29/2010)
GrantIm trying to use your statement:
But when I run the below code I get the error:
"Must declare the scalar variable "@CUTOFFDATE".
declare @DaysToRetainSMALLINTDECLARE @cutoffdate DATE
SET @daystoretain = '1'
SET @cutoffdate = '12/01/2010'
SELECT@CUTOFFDATE = DATEADD(dd, -(@DaysToRetain), GETDATE())
DELETE dbo.LPBSecurity_LoginActivity
--FROMLPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE
FROMLPB_ASPNETDB.DBO.LPBSECURITY_USERCLIENTMETADATA UC
WHEREUC.CREATEDDATE < @CUTOFFDATE
AND UC.userclientmetadataid = dbo.LPBSecurity_LoginActivity.UserClientMetaDataId
-----------------------------------------------------
GO
DELETE dbo.LPBSecurity_UserClientSignature
--FROMLPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE
FROMLPB_ASPNETDB.DBO.LPBSECURITY_USERCLIENTMETADATA UC
WHEREUC.CREATEDDATE < @CUTOFFDATE
AND UC.userclientmetadataid = dbo.LPBSecurity_UserClientSignature.UserClientMetaDataId
GO
DELETE LPB_ASPNETDB.DBO.LPBSecurity_UserClientMetaData UC
WHERE createddate < @CUTOFFDATE
Those GO statements are seperating the batches. That means that the variables declared are only available in the first batch. You don't need them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2010 at 8:03 am
I've never liked the idea of creating foreign key constraints with the cascade UPDATE/DELETE option. Let's assume that a misguided developer is trying to insert/update data in the INV_STATUS dimension table, and he deploys a script with the following DELETE/INSERT statements. He'll inadvertently cascade delete every record in the POLICY fact table with a INV_STATUS_CODE values of 'E'. Even if he unit tests his script in Dev, he might not catch his mistake, because it won't throw an error.
delete from INV_STATUS where INV_STATUS_CODE = 'E';
insert into invoice_status (inv_status_code,inv_status_desc) values ('E','Enrolled');
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 29, 2010 at 9:04 am
Grant,
I've now been told that I need to not delete any records from table C.
I'm trying to run this:
declare @DaysToRetainSMALLINT
DECLARE @cutoffdate DATE
SET @daystoretain = '200'
SET @cutoffdate = '12/01/2009'
SELECT@CUTOFFDATE = DATEADD(dd, -(@DaysToRetain), GETDATE())
DELETE dbo.LPBSecurity_LoginActivity
--FROMLPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE
FROMLPB_ASPNETDB.DBO.LPBSECURITY_USERCLIENTMETADATA UC
WHEREUC.CREATEDDATE < @CUTOFFDATE
AND UC.userclientmetadataid = dbo.LPBSecurity_LoginActivity.UserClientMetaDataId
DELETE LPB_ASPNETDB.DBO.LPBSecurity_UserClientMetaData
WHERE createddate < @CUTOFFDATE
But I get a foreign key error (for table C) of course..
How do I leave the records in table C?
April 29, 2010 at 9:21 am
krypto69 (4/29/2010)
Grant,I've now been told that I need to not delete any records from table C.
I'm trying to run this:
declare @DaysToRetainSMALLINTDECLARE @cutoffdate DATE
SET @daystoretain = '200'
SET @cutoffdate = '12/01/2009'
SELECT@CUTOFFDATE = DATEADD(dd, -(@DaysToRetain), GETDATE())
DELETE dbo.LPBSecurity_LoginActivity
--FROMLPB_ASPNETDB.DBO.LPBSECURITY_LOGINTRACE
FROMLPB_ASPNETDB.DBO.LPBSECURITY_USERCLIENTMETADATA UC
WHEREUC.CREATEDDATE < @CUTOFFDATE
AND UC.userclientmetadataid = dbo.LPBSecurity_LoginActivity.UserClientMetaDataId
DELETE LPB_ASPNETDB.DBO.LPBSecurity_UserClientMetaData
WHERE createddate < @CUTOFFDATE
But I get a foreign key error (for table C) of course..
How do I leave the records in table C?
You don't. If C has a relation to A... the data in A has to be there. That's the whole idea of enforcing referential integrity. It's enforced.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply