Solved

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

Posted on 2011-09-28
2
221 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

791 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