• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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

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
FrancineTaylor
Asked:
FrancineTaylor
1 Solution
 
JestersGrindCommented:
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
 
FrancineTaylorAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now