Solved

How to perform an action on each of a list of tables

Posted on 2011-09-28
2
216 Views
Last Modified: 2012-05-12
My company's database has over a hundred files which contain the column "CoreID".  I want to create a SP that will delete all records for a certain value of CoreID.

I'm guessing I should use a cursor, but I can't find a good example of exactly what I'm trying to do.  First, I have an SQL statement that will give me the names of all files which have the CoreID column.  For each of those files, I want to DELETE FROM <filename> WHERE CoreID = <some value>

What should my SQL look like?
0
Comment
Question by:FrancineTaylor
2 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 36718629
You could do something like the code that I listed below.  Or if the tables are fairly static, meaning you don't add new tables with a CoreID column regularly, you could use your select statement to generate the delete statements and then use them in the stored procedure.

Greg


DECLARE @SQL VARCHAR(8000), @Count INTEGER = 1, @TableName VARCHAR(100), @CoreID INTEGER

DECLARE @Tables TABLE(ID INTEGER IDENTITY(1,1), TableName VARCHAR(100))

INSERT INTO @Tables (TableName)
<Your Select Statement>

WHILE @Count <= (SELECT MAX(ID) FROM @Tables)

BEGIN

	SELECT @TableName = TableName FROM @Tables WHERE ID = @Count
	
	SELECT @SQL = 'DELETE FROM ' + @TableName + 'WHERE CoreID = ' + CONVERT(VARCHAR(10), @CoreID)
	
	EXECUTE sp_executesql @SQL
	
	SET @Count += 1
	
END

Open in new window

0
 
LVL 1

Author Comment

by:FrancineTaylor
ID: 36817084
Thanks, it was the creation and execution of the command that was the part I was missing.

I guess you can do it with a temp table or a cursor...
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question