this is a fairly common requirement.....delete lots of data, but not the look up tables kind of thing.

the things to watch out for are the order of the deletes...you can't delete parent data if you have child data referencing it due to the foreign key order.

you cannot issue the TRUNCATE command against a table that has foreign keys, so in that case you HAVE to delete.

here's a script i use to generate the commands...take a look at this, and see if this gives you a starting point for what you are asking.

SET NOCOUNT ON

--the list of tables we do not want to touch.

CREATE TABLE #Skipme(SchemaName varchar(255),TableName varchar(255))

INSERT INTO #Skipme

SELECT 'dbo','tbCity' UNION ALL

SELECT 'dbo','tbState' UNION ALL

SELECT 'dbo','tbCounty' UNION ALL

SELECT 'dbo','OtherLookupTables'

--adding some more based on a known factor: my lookup tables all start with LU* or TB*

INSERT INTO #Skipme

SELECT schema_name(schema_id),name from sys.objects

where LEFT(name,2) = 'LU'

OR LEFT(name,2) = 'TB'

--get the list of tables, and put them in FK hierarchy order.

CREATE TABLE #MyObjectHierarchy

(

HID int identity(1,1) not null primary key,

ObjectId int,

ObjectFullName varchar(510),

ObjectType int,

TypeDesc AS CASE

WHEN ObjectType = 1 THEN 'FUNCTION'

WHEN ObjectType = 4 THEN 'VIEW'

WHEN ObjectType = 8 THEN 'TABLE'

WHEN ObjectType = 16 THEN 'PROCEDURE'

WHEN ObjectType =128 THEN 'RULE'

ELSE ''

END,

ObjectSchema varchar(255),

ObjectName varchar(255),

DependencyOrder int

)

--our list of objects in dependancy order

INSERT #MyObjectHierarchy (ObjectType,ObjectName,ObjectSchema,DependencyOrder)

EXEC sp_msdependencies @intrans = 1

--Now simply select our results based on the joins of these two tables.

--the join excluses tables in out #SkipMe table

SELECT --*, --uncomment to see the full results.

CASE

WHEN DependencyOrder <= 1 --no dependancies at all

THEN 'TRUNCATE TABLE ' + QUOTENAME(T.ObjectSchema) + '.' + QUOTENAME(T.ObjectName)

ELSE 'DELETE FROM ' + QUOTENAME(T.ObjectSchema) + '.' + QUOTENAME(T.ObjectName)

END as cmd

FROM #MyObjectHierarchy T

LEFT OUTER JOIN #Skipme S

ON T.ObjectSchema = S.SchemaName

AND T.ObjectName = S.TableName

WHERE S.TableName IS NULL --not in the join

AND T.ObjectType = 8 --only tables

ORDER BY DependencyOrder,HID

--cleanup our temp tables

DROP TABLE #SkipMe

DROP TABLE #MyObjectHierarchy