Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete tables script Expand / Collapse
Author
Message
Posted Wednesday, February 23, 2011 2:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
Hi all,

I know this must be very basic, still a clear answer is appreciated.

I've been using a database made out of 2 separate SQL databases and more than 150 tables in total. Last task now is to delete most of the data (but not every table!) before going live.

Since I haven't used scripts before, my questions are:

1) How do you 'create' a delete a given table/s script? I mean the 1-2-3 steps process. Once done, how do you add new tables to that script, so that you can include as many as wanted?

2) Is it better to delete the data using Delete or Truncate?

3) If most of the tables are linked to an Access font-end, is there any technical advantage in deleting from SQL rather than from Access?

Thanks, a.

Post #1068114
Posted Wednesday, February 23, 2011 6:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,014, Visits: 10,515
a_ud (2/23/2011)
1) How do you 'create' a delete a given table/s script? I mean the 1-2-3 steps process. Once done, how do you add new tables to that script, so that you can include as many as wanted?

You could use something like this (untried):
DECLARE @tabSchema sysname
DECLARE @tabName sysname
DECLARE @deleteSQL nvarchar(4000)
DECLARE @truncateSQL nvarchar(4000)

DECLARE tabs CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN (
'Table1',
'Table2',
...
'TableN'
)

OPEN tabs

FETCH NEXT FROM tabs INTO @tabSchema, @tabName

--UNCOMMENT IF YOU WANT THE TASK TO BE PERFORMED ATOMICALLY
--BEGIN TRANSACTION

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
--UNCOMMENT AND RUN THIS IF YOU HAVE A WHERE CONDITION
SET @deleteSQL = N'DELETE FROM ' + QUOTENAME(@tabSchema) + '.' + QUOTENAME(@tabName) + ' WHERE <condition>'
--EXEC(@deleteSQL)

--UNCOMMENT AND RUN THIS IF YOU WANT TO CLEAR THE WHOLE TABLE
SET @truncateSQL = N'TRUNCATE TABLE' + QUOTENAME(@tabSchema) + '.' + QUOTENAME(@tabName)
--EXEC(@truncateSQL)
END TRY
BEGIN CATCH
PRINT 'Unable to delete table ' + @tabName + ': ' + ERROR_MESSAGE()
--UNCOMMENT IF YOU WANT THE TASK TO BE PERFORMED ATOMICALLY
--ROLLBACK
END CATCH
FETCH NEXT FROM tabs INTO @tabSchema, @tabName
END

--UNCOMMENT IF YOU WANT THE TASK TO BE PERFORMED ATOMICALLY
--IF @@TRANCOUNT > 0 COMMIT

CLOSE tabs
DEALLOCATE tabs

2) Is it better to delete the data using Delete or Truncate?


Truncate is much faster and is the way to go if you want to clear the whole table.

3) If most of the tables are linked to an Access font-end, is there any technical advantage in deleting from SQL rather than from Access?


Deleting from Access could turn into a row-by-row deletion, I would avoid doing it.

Hope this helps
Gianluca


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1068190
Posted Wednesday, February 23, 2011 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 12,881, Visits: 31,821
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





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1068201
Posted Wednesday, February 23, 2011 6:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279

Thanks both, good replies that will be taken on board.
Post #1068211
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse