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