?
Solved

Modify SQL Script to Delete Record

Posted on 2013-01-30
6
Medium Priority
?
375 Views
Last Modified: 2013-01-31
Hello Experts,

I currently have this SQL script that goes through all my tables in the database and deletes the record when it finds the item I specify. It is working well for me so far.
It searches for the field name "ITEMNMBR". But I just found out that the item is also located as a different field name "ITEMNO".
I would like to modify the script to be able to look at both fields for the item.

Thanks
*****************************************************************

CREATE TABLE zzResults (
      TableName VARCHAR(100)                                            
)      

EXEC sp_msforeachtable '
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''ITEMNMBR'')
            exec (''IF EXISTS(SELECT 1 FROM ? WHERE ITEMNMBR = ''''01AAABBBCCC-XX'''')
                        INSERT zzResults(TableName) VALUES (''''?'''') '')
           
'
declare @tabname nvarchar(100)
while exists (select top 1 1 from zzResults)
begin
select @tabname = min(TableName) from zzResults
exec ('delete from ' + @tabname + ' where ITEMNMBR = ''01AAABBBCCC-XX''')
delete from zzResults where TableName = @tabname
end
SELECT TableName
FROM zzResults

DROP TABLE zzResults
0
Comment
Question by:jann3891
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38837723
just run the same code with changed columnname one more time:
CREATE TABLE zzResults (
      TableName VARCHAR(100)                                            
)      

EXEC sp_msforeachtable '
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''ITEMNMBR'')
            exec (''IF EXISTS(SELECT 1 FROM ? WHERE ITEMNMBR = ''''01AAABBBCCC-XX'''')
                        INSERT zzResults(TableName) VALUES (''''?'''') '') 
            
'
declare @tabname nvarchar(100)
while exists (select top 1 1 from zzResults)
begin
select @tabname = min(TableName) from zzResults
exec ('delete from ' + @tabname + ' where ITEMNMBR = ''01AAABBBCCC-XX''')
delete from zzResults where TableName = @tabname
end
SELECT TableName
FROM zzResults


EXEC sp_msforeachtable '
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''ITEMNO'')
            exec (''IF EXISTS(SELECT 1 FROM ? WHERE ITEMNO = ''''01AAABBBCCC-XX'''')
                        INSERT zzResults(TableName) VALUES (''''?'''') '') 
            
'
declare @tabname nvarchar(100)
while exists (select top 1 1 from zzResults)
begin
select @tabname = min(TableName) from zzResults
exec ('delete from ' + @tabname + ' where ITEMNO = ''01AAABBBCCC-XX''')
delete from zzResults where TableName = @tabname
end

SELECT TableName
FROM zzResults

DROP TABLE zzResults 

Open in new window

0
 

Author Comment

by:jann3891
ID: 38837753
Yes, that's what I have been doing, just hoping there is a way to combine this that way I won't have to change the item 4 times.
If you don't mind, I will wait for other experts to put in their comment.

Thanks
0
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 38837796
then try like this
CREATE TABLE zzResults (
      TableName VARCHAR(100) ,
      ColumnName varchar(100)
)      

EXEC sp_msforeachtable ' INSERT into zzResults(TableName,ColumnName)  SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME in ( ''ITEMNMBR'', ''ITEMNO'') '
SELECT * 
FROM zzResults

declare @tabname nvarchar(100)
declare @colname nvarchar(100)

while exists (select top 1 1 from zzResults)
begin
   Select @tabname = TableName, @colname = ColumnName from ( Select TableName, ColumnName, row_number() over(order by TableName, ColumnName) RowID from 
            zzResults) A where RowID = 1

	exec ('delete from ' + @tabname + ' where ' + @colname + ' = ''01AAABBBCCC-XX''')

	Delete from zzResults where  @tabname = TableName and @colname = ColumnName

end

SELECT * 
FROM zzResults

drop table zzResults

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 39

Expert Comment

by:appari
ID: 38837810
you can replace, this works even if your sql version is SQL 2000.

   Select @tabname = TableName, @colname = ColumnName from ( Select TableName, ColumnName, row_number() over(order by TableName, ColumnName) RowID from
            zzResults) A where RowID = 1

with

   Select @tabname = TableName, @colname = ColumnName from ( Select TOP 1 TableName, ColumnName from
            zzResults) A
0
 

Author Comment

by:jann3891
ID: 38840015
getting this error:
Server: Msg 195, Level 15, State 10, Line 16
'row_number' is not a recognized function name.
0
 

Author Comment

by:jann3891
ID: 38840848
Got it. Had to make a few changes, but got it to work.
Thank you very much for your help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

777 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