quote:

---

Originally posted by Wael

Also the reason I need this is simply because I have a big .NET project at work with huge database and I wanted to transfer some of the database (not all of it) to my computer at home so I can work from there too..

---

-- Get most recent 10 orders
INSERT INTO TargetDatabase.dbo.ORDERS
SELECT TOP 10 *
FROM   SourceDatabase.dbo.ORDERS
WHERE  NOT EXISTS (SELECT * FROM TargetDatabase.dbo.ORDERS)
ORDER BY OrderDate DESC

-- Get Order Items for sample Order set
INSERT INTO TargetDatabase.dbo.ORDER_ITEMS
SELECT *
FROM   SourceDatabase.dbo.ORDER_ITEMS S
JOIN   TargetDatabase.dbo.ORDERS T
       ON S.OrderID = T.OrderID
WHERE  NOT EXISTS (SELECT * FROM TargetDatabase.dbo.ORDER_ITEMS)

-- Get Products for sample Order Item set
INSERT INTO TargetDatabase.dbo.PRODUCTS
SELECT *
FROM   SourceDatabase.dbo.PRODUCTS S
JOIN   TargetDatabase.dbo.ORDER_ITEMS T
       ON S.ProductID = T.ProductID
WHERE   NOT EXISTS (SELECT * FROM TargetDatabase.dbo.PRODUCTS)

quote:

---

Originally posted by AskSQLTeam

This article comes to us from Tim Young. Tim writes "One of the (few) very handy things about Access is the cascade delete function. If you delete a record from a parent table, all relating records in the child tables are also deleted. I couldn’t find any way of doing this in SQL Server 7, so I wrote a stored procedure for it." Thanks for the article Tim!<P>Article <a href="/item.asp?ItemID=8595">Link</a>.

---

USE [<DBNAME>]
GO

IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL DROP FUNCTION dbo.udfGetFullQualName
GO
CREATE FUNCTION dbo.udfGetFullQualName
(
	@ObjectId INTEGER
)
RETURNS VARCHAR(300)
AS
BEGIN
-- writen by Daniel Crowther 17 Dec 2004 primarily to support uspCascadeDelete (makes it more readable!!!)
	RETURN '[' + USER_NAME(OBJECTPROPERTY(@ObjectId, 'OwnerId')) + '].[' + OBJECT_NAME(@ObjectId) + ']'
END
GO






IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL DROP FUNCTION dbo.udfGetOnJoinClause
GO
CREATE FUNCTION dbo.udfGetOnJoinClause
(
	@fkNameId INTEGER
)
RETURNS VARCHAR(1000)
AS
BEGIN

-- writen by Daniel Crowther 16 Dec 2004 primarily to support uspCascadeDelete (makes it more readable!!!)
	DECLARE @OnClauseTemplate VARCHAR(1000)
	SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND '
	
	DECLARE @str VARCHAR(1000)
	SET @str = ''
	SELECT	
	/*	fk_name = OBJECT_NAME(constid),	-- constraint name
		cTableId = OBJECT_NAME(fkeyid),	-- child table
		c_col_id = COL_NAME(fkeyid, fkey),	-- child table column
		p_table_id = OBJECT_NAME(rkeyid),	-- parent table
		p_col_id = COL_NAME(rkeyid, rkey)	-- parent table column
	,	
	*/	@str = @str +
			REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(@OnClauseTemplate,
							'<@pTable>', 
							OBJECT_NAME(rkeyid)
						),
						'<@pCol>',
						COL_NAME(rkeyid, rkey)
					),
					'<@cTable>',
					OBJECT_NAME(fkeyid)
				),
				'<@cCol>',
				COL_NAME(fkeyid, fkey)
			)
	FROM	dbo.sysforeignkeys fk
	WHERE	fk.constid = @fkNameId --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
	
	RETURN LEFT(@str, LEN(@str) - LEN(' AND ') )
END
GO












IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL DROP PROCEDURE dbo.uspCascadeDelete
GO

CREATE PROCEDURE dbo.uspCascadeDelete
 	@ParentTableId VARCHAR(300), -- can also take object_id!!!
 	@WhereClause VARCHAR(2000),
	@ExecuteDelete CHAR(1) = 'N',
 	@FromClause VARCHAR(8000) = '',
	@Level INTEGER = 0
AS

-- writen by Daniel Crowther 16 Dec 2004 - handles composite primary keys

SET NOCOUNT ON

/* Set up debug */
DECLARE	@DebugMsg VARCHAR(4000),
	@DebugIndent VARCHAR(50)

SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ' 

IF ISNUMERIC(@ParentTableId) = 0
BEGIN -- assume owner is dbo and calculate id
	IF CHARINDEX('dbo.', @ParentTableId) = 0
		SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']')
	ELSE
		SET @ParentTableId = OBJECT_ID(@ParentTableId)
END

IF @Level = 0
BEGIN
	PRINT @DebugIndent + ' **************************************************************************'
	PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId)
	IF @ExecuteDelete = 'Y'
		PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...'
	ELSE
		PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***'
END



DECLARE @CRLF CHAR(2)
SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @strSQL VARCHAR(4000)

IF @Level = 0 SET @strSQL = 'SET NOCOUNT ON' + @CRLF
ELSE SET @strSQL = ''

SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST(@@NESTLEVEL AS VARCHAR) + ''''
IF @ExecuteDelete = 'Y' EXEC ( @strSQL )
ELSE PRINT @strSQL

DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
FOR	SELECT DISTINCT
		fkNameId = constid,	-- constraint name
		cTableId = fkeyid	-- child table
/*		cColId = fkey,		-- child table column
		pTableId = rkeyid,	-- parent table
		pColId = rkey		-- parent table column
*/	FROM	dbo.sysforeignkeys fk
	WHERE	fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
		AND
		fk.rkeyid = @ParentTableId

OPEN curs_children 

DECLARE @fkNameId INTEGER, 
	@cTableId INTEGER,
	@cColId INTEGER,
	@pTableId INTEGER,
	@pColId INTEGER

FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId --, @cColId, @pTableId, @pColId

DECLARE @strFromClause VARCHAR(1000)
DECLARE @nLevel INTEGER

IF @Level = 0
BEGIN
	SET @FromClause = 
		'FROM	' + dbo.udfGetFullQualName(@ParentTableId)
END

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @strFromClause = 
			@FromClause + @CRLF + 
			'	INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + 
			'		ON ' + dbo.udfGetOnJoinClause(@fkNameId)
	SET @nLevel = @Level + 1
	EXEC dbo.uspCascadeDelete
		@ParentTableId = @cTableId,
		@WhereClause = @WhereClause,
		@ExecuteDelete = @ExecuteDelete,
		@FromClause = @strFromClause,
		@Level = @nLevel

	SET @strSQL =  
		'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + 
		@strFromClause + @CRLF + 
		'WHERE	' + @WhereClause + @CRLF
	SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + '	Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF
	
	IF @ExecuteDelete = 'Y' EXEC ( @strSQL )
	ELSE PRINT @strSQL

	FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId --, @cColId, @pTableId, @pColId
END

IF @Level = 0
BEGIN
	SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST(@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF
	SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF
	SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF

	IF @ExecuteDelete = 'Y' EXEC ( @strSQL )
	ELSE PRINT @strSQL
END

CLOSE curs_children 
DEALLOCATE curs_children 


GO

/*
-- Example 1
EXEC uspCascadeDelete
	@ParentTableId = 'dbo.ProductClass',
	@WhereClause = 'ProductClass.Class = ''RTB2'''
--	,@ExecuteDelete = 'Y'





-- Example 2
EXEC uspCascadeDelete
	@ParentTableId = 'dbo.brand',
	@WhereClause = 'brand.brand_name <> ''Apple'''
--	,@ExecuteDelete = 'Y'

exec uspCascadeDelete
	@ParentTableId = 'dbo.product_type',
	@WhereClause = 'product_type.product_type_id NOT IN (SELECT bpt.product_type_id FROM dbo.brand_product_type bpt)'
--	,@ExecuteDelete = 'Y'





*/

quote:

---

Originally posted by GreySky

I greatly appreciated your code on the SQL cascade delete, and indeed it works as advertised. Unfortunately, I found that if the cascade tree was deep, it took a considerable amount of time to perform the cascade...

---