Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb latch contention 12 47
Alter table 4 23
sql server query from excel 3 57
What query can i write to find where a function is 4 15
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how the fundamental information of how to create a table.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now