March 24, 2009 at 8:59 pm
I need to delete records from all the tables that has the _emp_ in its name
where the processdate field in these tables is less than 1 day from getdate().
Is it possible to delete certain set of records like that from a list of tables,
one by one? if so, how, WITHOUT using cursor?
The code below is what I have so far. I was thinking to do deletion of records
by going in the order of the table ID, but I got stuck because how can I call
the table dynamically and the query also knows to work on the where clause
as well of the process date? any idea would be greatly appreciated.
create table #tables
(tableID int identity(1,1), tableName varchar(250))
insert into #tables
select name from sysobjects
where name like '%_emp_%'
and xtype = 'U'
select * from #tables
order by tableID
March 24, 2009 at 9:33 pm
I figured it out now. Tried to delete this post, but for some reason, it won't delete.
March 27, 2009 at 7:37 am
Here is some sample code that builds a list of tables where the name matches a pattern, and then executes T-SQL for each table. No cursors 🙂
DECLARE
@TABLE_NAME sysname,
@sql nvarchar(4000)
SET NOCOUNT ON
CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254)
)
INSERT INTO #temp
EXEC sp_tables 'tbl%' -- change to desired pattern
EXEC sp_executesql N'ALTER TABLE #temp ADD Processed bit NOT NULL DEFAULT (0)'
WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
-- replace next line with your SQL statement
SET @sql = 'SELECT COUNT(*) FROM ' + @TABLE_NAME
EXEC sp_executesql @sql
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END
DROP TABLE #temp
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply