August 2, 2008 at 7:09 am
Hello folks,
Here's the thing (issue, problem, conundrum - pick one). Warning, long preamble before the question 😉
I am working on a data migration project that involves transforming data from one set of tables into a new set, in the same db. To test my scripts I load the 'new' tables in a test environment so the people testing can access the data though our developing application. As is expected, the first pass is incomplete or in some cases plain wrong. Due to my misunderstanding of the requirements, or more often than not, they forget to tell me about certain key fields 😉
So I have to delete the records I have loaded and then re-populate said tables with my revised script. As this environment is being used at the same time by the end users for acceptance testing, I can't simply clear down the tables and start again, as I will wipe out any records created through the front end and are being tracked through the system to pass acceptance testing.
Another complication is I am loading 4 tables, and when these records are used from the app, other tables are populated with records. DRI is enforced through FK constraints, so If a record I have inserted is used, I need to find all associated records and delete them first, having joined back to the parent table and adding the appropriate where clause. (where parentTable.CreatedBy = 999 and parentTable.CreatedDateTime > ...)
Now this is a candidate for code that generates code, so I went off and looked for it. I couldn't find anything other than wipe out all records in all related tables so I have had to roll up my sleeves and write it myself. :rolleyes:
The output for what I have now is
delete T2_CLAllocationCommodityLine from
T2_CLAllocationCommodityLine
inner join T2_CLAllocation
on T2_CLAllocation.CLAllocationID = T2_CLAllocationCommodityLine.CLAllocationID
inner join T2_ContractLine
on T2_ContractLine.ContractLineID = T2_CLAllocation.ContractLineID
inner join T2_ContractHeader
on T2_ContractHeader.ContractHeaderID = T2_ContractLine.ContractHeaderID
where T2_ContractHeader.CreatedBy = 999 and T2_ContractHeader.CreatedDate > '30 July 2008 18:00'
[snipped for brevity this generates 19 delete statements!]
delete T2_ContractLineGrade from
T2_ContractLineGrade
inner join T2_ContractLine
on T2_ContractLine.ContractLineID = T2_ContractLineGrade.ContractLineID
inner join T2_ContractHeader
on T2_ContractHeader.ContractHeaderID = T2_ContractLine.ContractHeaderID
where T2_ContractHeader.CreatedBy = 999 and T2_ContractHeader.CreatedDate > '30 July 2008 18:00'
delete T2_ContractLine from
T2_ContractLine
inner join T2_ContractHeader
on T2_ContractHeader.ContractHeaderID = T2_ContractLine.ContractHeaderID
where T2_ContractHeader.CreatedBy = 999 and T2_ContractHeader.CreatedDate > '30 July 2008 18:00'
delete T2_ContractHeader from
T2_ContractHeader
where T2_ContractHeader.CreatedBy = 999 and T2_ContractHeader.CreatedDate > '30 July 2008 18:00'
Where the Header, Line and Line Grade tables are the ones I populate.
Its working fine as long as each table has only one FK reference, but breaks when there are more than one. To fix I need to add automatic alias naming and frankly I'm stuck.
If I can get it to go I think this would be a valuable tool for any DBA, but could also be downright dangerous in the hands of some one with the wrong access rights and a grudge.
So my question is this: Do I post the code I have currently got and outline how it could be better? Or do I work it out and keep it to myself? (Which I don't want to do).
Dave J :unsure:
August 2, 2008 at 6:51 pm
There is no security in obscurity.
Post it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 3, 2008 at 5:14 pm
There are some notes which should help you to sort it out.
1. Your migration solution when finished to be applied to Production system where there are no new tables. So, when it's run in Test, it must drop all objects not existing in Production and start from scratch. In our projects we just restore Production database(s) in DEV and then run deployment scripts.
2. Test system must have "parallel" functionality. All changes you make to data must be applied both to old and new tables. Yes, double inserts/updates will affect performance, but it's just test system, right?
3. Having parallel run in place you may safely drop/create/repopulate new tables - all new test data up to the last second before deployment will be copied from old tables. By having this applied and tested you make sure no live data will be lost from Production system when you deploy you solution there.
_____________
Code for TallyGenerator
August 4, 2008 at 3:05 am
rbarryyoung (8/2/2008)
There is no security in obscurity.Post it.
OK
/*
Used to generate Delete scripts. It does NOT run them.
You need to copy the output of this script to a new window and run it manually.
Run in text mode.
Use at own risk!
Set the @table parameter to the parent table you want to delete from.
Change the @type parameter to either S or D to generate a select or delete query.
The @debug flag is not useful for 'production' use, but it helps you see what's going on if set to 1
As it stands, this script will generate 4 Select queries in the pubs db, 'seeded' with the titles table.
Dave Jackson
31 July 2008
*/
if db_name() <> 'pubs'
Begin
Print 'This script needs to be run in pubs'
Return
End
Set Nocount On
Declare @table Varchar(200)
,@lvl Int
,@where Varchar(2000)
,@type Char(1)
,@current Varchar(200)
--Set a debug flag 1 = True, 0 = False
--if set to true, then select from work tables, show where clause etc.
Declare @debug Int
Set @debug = 0
--Set the table we ultimately want to delete from, and the starting level to one
Select @table = 'titles'
,@lvl = 1
--Choose type of Query to generate
--S = Select, D = Delete
Select @type = Upper('S')
--Set the where clause
--leave @where blank ('') if you want to get rid of all records...(be careful people).
--use double single quotes if you need a single one, or use the pipe symbol, '|',
--without the quotes instead of a 2 single quotes.
--The next replace statement replaces them with escaped single quote marks
--making the where clause easier to read.
--Note you must qualify the column names using 2 part naming convention.
Select @where = 'where titles.type in(|psychology|,|trad_cook|)'
Select @where = Replace(@where,'|','''')
If @debug = 1
--show what we've got
Select @where
--copy the initial table setting for use later
Select @current = @table
--drop object if it already exists
If Object_id('tempdb..#hierarchy') Is Not Null
Drop Table #hierarchy
--create work table
Create Table #hierarchy
(parent_table varchar(200) not null,
parent_column varchar(200) not null,
child_table varchar(200),
child_column varchar(200),
lvl int not null--,
CONSTRAINT UIX_parentchild
UNIQUE NONCLUSTERED (parent_table,child_table,parent_column,child_column)
)
Create Clustered Index Cix_parent On #hierarchy (
Parent_table)
--drop object if it already exists
If Object_id('tempdb..#done') Is Not Null
Drop Table #done
--create work done table
Create Table #done (
Tab Varchar(200))
--drop object if it already exists
If Object_id('tempdb..#FkeyDefs') Is Not Null
Drop Table #fkeydefs
--setup working set - we do this once into a working table
--because it is slow doing this once each iteration of the following loop
--Taken from John Liu's very good script, find it at
--http://www.sqlservercentral.com/scripts/Miscellaneous/61481/
Select 'PK_Table_Name' = Ccu.Table_name
,'PK_Column_Name' = Ccu.Column_name
,'FK_Table_Name' = Ccu1.Table_name
,'FK_Column_Name' = Ccu1.Column_name
Into #fkeydefs
From Information_schema.Constraint_column_usage Ccu
Inner Join Information_schema.Referential_constraints Rc
On Ccu.Constraint_name = Rc.Unique_constraint_name
Inner Join Information_schema.Constraint_column_usage Ccu1
On Rc.Constraint_name = Ccu1.Constraint_name
Where Ccu.Constraint_name Not In (Select Constraint_name
From Information_schema.Referential_constraints)
Create Clustered Index Cix_pk_table_name On #fkeydefs (
Pk_table_name)
If @debug = 1
--show what we've got
Select *
From #fkeydefs
--Infinite loop ;0)
While 1 = 1
Begin
Insert #hierarchy
Select Pk_table_name
,Pk_column_name
,Fk_table_name
,Fk_column_name
,@lvl
From #fkeydefs
Where Pk_table_name Like @current
If @@rowcount = 0
--This looks to see if there are any more to do
Begin
If Not Exists (Select H1.Parent_table
From #hierarchy H1
Left Join #hierarchy H2
On H1.Child_table = H2.Parent_table
And H1.Parent_table = H2.Parent_table
Where H2.Child_table Is Null
And H1.Child_table Not In (Select Tab
From #done))
--There isn't, so quit this loop
Break
End
--Keep a record of what we have done, or we'll keep reprocessing when we are at the
--last leaf of the tree.
Insert #done
Select @current
Set @lvl = @lvl + 1
--Reset the name of the table we are after
Select @current = H1.Child_table
From #hierarchy H1
Left Join #hierarchy H2
On H1.Child_table = H2.Parent_table
And H1.Parent_table = H2.Parent_table
Where H2.Child_table Is Null
And H1.Child_table Not In (Select Tab
From #done)
--Bottom of the While loop, jump back to the top here
End
If @debug = 1
--show what we've got
Select *
From #hierarchy
Order By Lvl Desc
Set Nocount On
--work out the hierarchy
--taken from the example in BOL (2000)
Declare @line Varchar(255)
If Object_id('tempdb..#stack') Is Not Null
Drop Table #stack
Create Table #stack (
Item Char(255)
,Lvl Int)
If Object_id('tempdb..#stack2') Is Not Null
Drop Table #stack2
Create Table #stack2 (
Item Char(255)
,Lvl Int)
Insert Into #stack
Values (@table
,1)
Select @lvl = 1
While @lvl > 0
Begin
If Exists (Select *
From #stack
Where Lvl = @lvl)
Begin
Select @table = Item
From #stack
Where Lvl = @lvl
If @debug = 1
Begin
Select @line = '--' + Space((@lvl * 2) - 1) + @table
Print @line
End
Select @line = @table
If Not Exists (Select 1
From #stack2
Where Item = @line)
Insert Into #stack2
Select @line
,@lvl
Delete From #stack
Where Lvl = @lvl
And Item = @table
Insert #stack
Select Child_table
,@lvl + 1
From #hierarchy
Where Parent_table = @table
If @@rowcount > 0
Select @lvl = @lvl + 1
End
Else
Select @lvl = @lvl - 1
End --Of the WHILE Loop
If @debug = 1
--show what we've got
Begin
Select Item
,Lvl
From #stack2
Order By Lvl Desc
,Item Desc
Print '--========================================================='
End
-- =============================================
-- Declare a READ_ONLY cursor,
-- funnily enough, the quickest part of this script...
-- =============================================
Print 'Begin Tran
-- rollback
-- commit
-- Note: If there is no FK defined, this script does NOT pick it up.'
Declare BuildSelectDeleteQuery Cursor Read_only For
Select Item
,Max(Lvl)
From #stack2
Group By Item
Order By Max(Lvl) Desc
Declare @item Varchar(255)
,@lvl2 Int
,@sql Varchar(8000)
Open BuildSelectDeleteQuery
Fetch Next From BuildSelectDeleteQuery
Into @item,
@lvl
While (@@fetch_status <> -1)
Begin
If (@@fetch_status <> -2)
Begin
Select @item = Rtrim(@item)
Select @lvl2 = @lvl
--Don't muck about with the line breaks,
--they are essential for formatting the output
If @type = 'S'
Select @sql = Char(13) + 'Select ' + @item + '.* '
If @type = 'D'
Select @sql = Char(13) + 'Delete ' + @item
Select @sql = @sql + ' from' + Char(13) + ' ' + @item
While @lvl2 <> 0
Begin
Select @sql = @sql + Char(13) + ' inner join ' + Parent_table + Char(13) +
' on ' + Parent_table + '.' + Parent_column + ' = ' + Child_table + '.' + Child_column
From #stack2 S
Inner Join #hierarchy H
On S.Item = H.Child_table
Where H.Child_table = @item
Select @item = Parent_table
From #stack2 S
Inner Join #hierarchy H
On S.Item = H.Child_table
Where S.Item = @item
Select @lvl2 = @lvl2 - 1
End
Print @sql + Char(13) + @where
End
Fetch Next From BuildSelectDeleteQuery
Into @item,
@lvl
End
Close BuildSelectDeleteQuery
Deallocate BuildSelectDeleteQuery
Set Nocount Off
If you run the code on a table which references a parent table on two columns, it fails...
Dave J
August 4, 2008 at 7:39 am
I have the following constraint (along with others that are similar) on a table:
ALTER TABLE [dbo].[Ship_Contract_Line]
WITH NOCHECK ADD CONSTRAINT
[XFKCT_Ship_Contract_Line_Delivery_Point_Code]
FOREIGN KEY([Party_Account_No], [Delivery_Point_Code])
REFERENCES [dbo].[Delivery_Point] ([Party_Account_No], [Delivery_Point_Code])
(Because a (third) party can have several delivery destinations, which must be defined).
Therefore the query
Select 'PK_Table_Name' = Ccu.Table_name
,'PK_Column_Name' = Ccu.Column_name
,'FK_Table_Name' = Ccu1.Table_name
,'FK_Column_Name' = Ccu1.Column_name
From Information_schema.Constraint_column_usage Ccu
Inner Join Information_schema.Referential_constraints Rc
On Ccu.Constraint_name = Rc.Unique_constraint_name
Inner Join Information_schema.Constraint_column_usage Ccu1
On Rc.Constraint_name = Ccu1.Constraint_name
Where Ccu.Constraint_name Not In (Select Constraint_name
From Information_schema.Referential_constraints)
and Ccu.Table_name = 'Delivery_Point'
Returns
PK_Table_NamePK_Column_NameFK_Table_NameFK_Column_Name
--------------------------------------------------------------------------------------------------
Delivery_PointDelivery_Point_CodeShip_Contract_LineDelivery_Point_Code
Delivery_PointDelivery_Point_CodeShip_Contract_LineParty_Account_No
Delivery_PointParty_Account_No Ship_Contract_LineDelivery_Point_Code
Delivery_PointParty_Account_No Ship_Contract_LineParty_Account_No
Now before everyone leaps down my throat, I know that this is bad design. It's not mine! :hehe: Personally I would have joined on the PK of the MS_Delivery_Point, but I have inherited what I have.
So, I need to make the query that populates #fkeydefs better?
Dave J
August 6, 2008 at 4:18 am
David Jackson (8/4/2008)
So, I need to make the query that populates #fkeydefs better?
No, I need to be smarter about building the join. 😉 I think I've got it. A partial table definition I have is
CREATE TABLE [dbo].[Delivery_Point](
[Delivery_Point_Code] [varchar](7) NOT NULL,
[Party_Account_No] [varchar](7) NOT NULL,
[Created_By] [varchar](12) NOT NULL,
[Created_Dt] [datetime] NOT NULL,
CONSTRAINT [XPKDelivery_Point] PRIMARY KEY CLUSTERED
(Party_Account_No] ASC,[Delivery_Point_Code] ASC)
) ON [PRIMARY]
And tables referencing this table were causing the problem. The latest script outputs:
-- Delivery_Point
-- Ship_Contract_Line
-- Shipping_Allocation
-- Barge_Ticket_Commodity
-- Shipping_Margin_Adj
--=========================================================
Begin Tran
-- rollback
-- commit
-- Note: If there is no FK defined, this script does NOT pick it up.
Select Shipping_Margin_Adj.* from
Shipping_Margin_Adj
inner join Barge_Ticket_Commodity
on Barge_Ticket_Commodity.Ticket_Comm_Id = Shipping_Margin_Adj.Ticket_Comm_Id
inner join Shipping_Allocation
on Shipping_Allocation.Cargo_Id = Barge_Ticket_Commodity.Cargo_Id
inner join Ship_Contract_Line
on Ship_Contract_Line.Ship_Contract_Line_Id = Shipping_Allocation.Ship_Contract_Line_Id
inner join Delivery_Point on Delivery_Point.Delivery_Point_Code = Ship_Contract_Line.Delivery_Point_Code
and Delivery_Point.Party_Account_No = Ship_Contract_Line.Party_Account_No
where Delivery_Point.CreatedBy = 'DaveJ' and Delivery_Point.CreatedDate > '31 Jul 2008'
[...]
Select Ship_Contract_Line.* from
Ship_Contract_Line
inner join Delivery_Point on Delivery_Point.Delivery_Point_Code = Ship_Contract_Line.Delivery_Point_Code
and Delivery_Point.Party_Account_No = Ship_Contract_Line.Party_Account_No
where Delivery_Point.CreatedBy = 'DaveJ' and Delivery_Point.CreatedDate > '31 Jul 2008'
Select Delivery_Point.* from
Delivery_Point
where Delivery_Point.CreatedBy = 'DaveJ' and Delivery_Point.CreatedDate > '31 Jul 2008'
And the code that does this is:
/*
Used to generate Delete scripts. It does NOT run them.
You need to copy the output of this script to a new window and run it manually.
Run in text mode.
Use at own risk!
Set the @table parameter to the parent table you want to delete from.
Change the @type parameter to either S or D to generate a select or delete query.
The @debug flag is not useful for 'production' use, but it helps you see what's going on if set to 1
As it stands, this script will generate 4 Select queries in the pubs db, 'seeded' with the titles table.
Dave Jackson
31 July 2008
*/
Set Nocount On
Declare @table Varchar(200)
,@lvl Int
,@where Varchar(2000)
,@type Char(1)
,@current Varchar(200)
--Set a debug flag 1 = True, 0 = False
--if set to true, then select from work tables, show where clause etc.
Declare @debug Int
Set @debug = 0
--Set the table we ultimately want to delete from, and the starting level to one
Select @table = 'titles'
,@lvl = 1
--Choose type of Query to generate
--S = Select, D = Delete
Select @type = Upper('s')
--Set the where clause
--leave @where blank ('') if you want to get rid of all records...(be careful people).
--use double single quotes if you need a single one, or use the pipe symbol, '|',
--without the quotes instead of a 2 single quotes.
--The next replace statement replaces them with escaped single quote marks
--making the where clause easier to read.
--Note you must qualify the column names using 2 part naming convention.
Select @where = 'where titles.type in(|psychology|,|trad_cook|)'
Select @where = Replace(@where,'|','''')
If @debug = 1
--show what we've got
Select @where
--copy the initial table setting for use later
Select @current = @table
--drop object if it already exists
If Object_id('tempdb..#hierarchy') Is Not Null
Drop Table #hierarchy
--create work table
Create Table #hierarchy
(parent_table varchar(200) not null
,parent_column varchar(200) not null
,child_table varchar(200)
,child_column varchar(200)
,lvl int not null
,CONSTRAINT UIX_parentchild2
UNIQUE NONCLUSTERED (parent_table, child_table, parent_column, child_column)
)
Create Clustered Index Cix_parent On #hierarchy (Parent_table)
--drop object if it already exists
If Object_id('tempdb..#done') Is Not Null
Drop Table #done
--create work done table
Create Table #done (
Tab Varchar(200))
--drop object if it already exists
If Object_id('tempdb..#FkeyDefs') Is Not Null
Drop Table #fkeydefs
--setup working set - we do this once into a working table
--because it is slow doing this once each iteration of the following loop
--Taken from John Liu's very good script, find it at
--http://www.sqlservercentral.com/scripts/Miscellaneous/61481/
Select 'PK_Table_Name' = Ccu.Table_name
,'PK_Column_Name' = Ccu.Column_name
,'FK_Table_Name' = Ccu1.Table_name
,'FK_Column_Name' = Ccu1.Column_name
Into #fkeydefs
From Information_schema.Constraint_column_usage Ccu
Inner Join Information_schema.Referential_constraints Rc
On Ccu.Constraint_name = Rc.Unique_constraint_name
Inner Join Information_schema.Constraint_column_usage Ccu1
On Rc.Constraint_name = Ccu1.Constraint_name
Where Ccu.Constraint_name Not In (Select Constraint_name
From Information_schema.Referential_constraints)
Create Clustered Index Cix_pk_table_name2 On #fkeydefs (
Pk_table_name)
If @debug = 1
--show what we've got
Select *
From #fkeydefs
declare @errs int, @rows int
--Infinite loop ;0)
While 1 = 1
Begin
Insert #hierarchy
Select Pk_table_name
,Pk_column_name
,Fk_table_name
,Fk_column_name
,@lvl
From #fkeydefs
Where Pk_table_name Like @current
and not Exists(Select H1.Parent_table
From #hierarchy H1
Left Join #hierarchy H2
On H1.Child_table = H2.Parent_table
And H1.Parent_table = H2.Parent_table
Where H2.Child_table Is Null
And H1.Parent_table = @current)
select @errs = @@Error, @rows = @@rowcount
-- if @errs <> 0
--Continue
if @rows = 0
--This looks to see if there are any more to do
Begin
If Not Exists (Select H1.Parent_table
From #hierarchy H1
Left Join #hierarchy H2
On H1.Child_table = H2.Parent_table
And H1.Parent_table = H2.Parent_table
Where H2.Child_table Is Null
And H1.Child_table Not In (Select Tab
From #done))
--There isn't, so quit this loop
Break
End
--Keep a record of what we have done, or we'll keep reprocessing when we are at the
--last leaf of the tree.
Insert #done
Select @current
Set @lvl = @lvl + 1
--Reset the name of the table we are after
Select @current = H1.Child_table
-- select *
From #hierarchy H1
Left Join #hierarchy H2
On H1.Child_table = H2.Parent_table
And H1.Parent_table = H2.Parent_table
Where H2.Child_table Is Null
And H1.Child_table Not In (Select Tab
From #done)
--Bottom of the While loop, jump back to the top here
End
If @debug = 1
--show what we've got
Select *
From #hierarchy
Order By Lvl Desc
Set Nocount On
--work out the hierarchy
--taken from the example in BOL (2000)
Declare @line Varchar(255)
If Object_id('tempdb..#stack') Is Not Null
Drop Table #stack
Create Table #stack (
Item Char(255)
,Lvl Int)
If Object_id('tempdb..#stack2') Is Not Null
Drop Table #stack2
Create Table #stack2 (
Item Char(255)
,Lvl Int)
Insert Into #stack
Values (@table
,1)
Select @lvl = 1
While @lvl > 0
Begin
If Exists (Select *
From #stack
Where Lvl = @lvl)
Begin
Select @table = Item
From #stack
Where Lvl = @lvl
--uncomment the next line if you don't want to see the hierarchy at all...
--If @debug = 1
Begin
Select @line = '--' + Space((@lvl * 4) - 1) + @table
Print @line
End
Select @line = @table
If Not Exists (Select 1
From #stack2
Where Item = @line)
Insert Into #stack2
Select @line
,@lvl
Delete From #stack
Where Lvl = @lvl
And Item = @table
Insert #stack
Select Child_table
,@lvl + 1
From #hierarchy
Where Parent_table = @table
If @@rowcount > 0
Select @lvl = @lvl + 1
End
Else
Select @lvl = @lvl - 1
End --Of the WHILE Loop
If @debug = 1
--show what we've got
Begin
Select Item
,Lvl
From #stack2
Order By Lvl Desc
,Item Desc
End
Print '--========================================================='
-- =============================================
-- Declare a READ_ONLY cursor,
-- funnily enough, the quickest part of this script...
-- =============================================
Print 'Begin Tran
-- rollback
-- commit
-- Note: If there is no FK defined, this script does NOT pick it up.'
Declare BuildSelectDeleteQuery Cursor Read_only For
Select Item
,Max(Lvl)
From #stack2
Group By Item
Order By Max(Lvl) Desc
Declare @item Varchar(255)
,@lvl2 Int
,@sql Varchar(8000)
,@copysql Varchar(8000)
,@joinsql Varchar(8000)
Open BuildSelectDeleteQuery
Fetch Next From BuildSelectDeleteQuery
Into @item,
@lvl
While (@@fetch_status <> -1)
Begin
If (@@fetch_status <> -2)
Begin
Select @item = Rtrim(@item)
Select @lvl2 = @lvl
--Don't muck about with the line breaks,
--they are essential for formatting the output
If @type = 'S'
Select @sql = Char(13) + 'Select ' + @item + '.* '
If @type = 'D'
Select @sql = Char(13) + 'Delete ' + @item
Select @sql = @sql + ' from' + Char(13) + ' ' + @item
Select @joinsql = ''
While @lvl2 <> 0
Begin
Select @copysql = @sql
Select @sql = @sql + Char(13) + ' inner join ' + Parent_table + Char(13) +
' on ' + Parent_table + '.' + Parent_column + ' = ' + Child_table + '.' + Child_column
From #stack2 S
Inner Join #hierarchy H
On S.Item = H.Child_table
Where H.Child_table = @item
if @@rowcount > 1
--found more than one, so we need to re-construct the join condition
--This assumes the compound column keys used in the join are named the same...
--If they are not, manual intervention will be required
Begin
Select top 1 @joinsql = @joinsql + Char(13) + ' inner join ' + Parent_table + ' on '
+ Parent_table + '.' + Parent_column + ' = ' + Child_table + '.' + Child_column
From #stack2 S
Inner Join #hierarchy H
On S.Item = H.Child_table
Where H.Child_table = @item
Select @joinsql = @joinsql + Char(13) + ' and '
+ Parent_table + '.' + Parent_column + ' = ' + Child_table + '.' + Child_column
From #stack2 S
Inner Join #hierarchy H
On S.Item = H.Child_table
Where H.Child_table = @item and H.child_column = H.parent_column
and patindex('%' + H.child_column + '%', @joinsql) = 0
Select @sql = @copysql + ' ' + @joinSQL
select @joinsql = ''
End
Select @item = Parent_table
From #stack2 S
Inner Join #hierarchy H
On S.Item = H.Child_table
Where S.Item = @item
Select @lvl2 = @lvl2 - 1
End
Print @sql + Char(13) + @where
End --End of (@@fetch_status <> -2) While
Fetch Next From BuildSelectDeleteQuery
Into @item, @lvl
End --End of (@@fetch_status <> -1) While
Close BuildSelectDeleteQuery
Deallocate BuildSelectDeleteQuery
Set Nocount Off
I hope this code helps anyone that needs it, but make sure only people you trusts have the correct rights to do so. :w00t:
Dave J
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply