Hi,
I am trying to create a stored procedure to delete all data from the
tables in a test database.
So we can fill it with a data set, run tests and load another set of
data....
Deleting the data itself is not an issue. But we have many
relationships between tables. So just doing a loop over the tables to
delete the data gets constraint errors. Its not a big deal to run the
SP a few times, but I was wondering if there was a way to query for the
relations, delete/suspend them and then add them back?
So far my SP looks like:
ALTER PROCEDURE [dbo].[SP_DataDump]
@Tblname varchar(250)
AS
DECLARE @tableName varchar(250)
DECLARE @newSQL varchar(1000)
IF (@Tblname = 'all')
BEGIN
DECLARE tables_csr cursor for
Select name
From sysObjects
Where type = 'U' and Patindex('%_LU_%', name ) = 0
END
ELSE
BEGIN
DECLARE tables_csr cursor for
Select name
From sysObjects
Where type = 'U' and Patindex('%'[EMAIL PROTECTED] '%', name )
> 0
END
Open tables_csr
fetch next from tables_csr into @tableName
while @@fetch_status = 0
Begin
SET @newSQL = 'Delete From ' + @tablename
exec(@newSQL)
fetch next from tables_csr into @tableName
end
_LU_ are our "LOOKUP" tables like states etc, data never changes so
don't need/want to empty them.
I know a similar statement to this can add/remove the constraints. I
just don't know how/if possible to get the constraints for each table in
the sp.
ALTER TABLE [dbo].[Components_tbl] WITH CHECK ADD CONSTRAINT
[FK_Components_tbl_Image_tbl] FOREIGN KEY([ImageID])
REFERENCES [dbo].[Image_tbl] ([ImageID])
Any help would be appreciated.
Rodney
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 and Flex 2
Build sales & marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2877
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
