?
Solved

SQL - replace value in all tables of entire database - SQL Server 2005

Posted on 2010-11-18
3
Medium Priority
?
350 Views
Last Modified: 2012-05-10
Hello experts,

I have a SQL database that contains employee information in the person table, and each employee has an id number that is a varchar.  However, some ID's have an extra period within the number, and I need to remove that period.  I then need to replace that new person number in every other instance that exists in the data base.  So:

table: person
ID                   last_name      first_name
923456.1       Smith              John
753456.1       Smith              Jane
123356.1       Jones             Randy
423456.1       Wright            Collin

becomes:

ID                   last_name      first_name
9234561       Smith              John
7534561       Smith              Jane
1233561       Jones             Randy
4234561       Wright            Collin

However, this needs to happen in all tables everywhere in the data base.  Anytime you see "123456.1" replace with "1234561" etc.  

Thoughts?

Thanks!
0
Comment
Question by:robthomas09
[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 Comments
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 200 total points
ID: 34169147
You can set up either an SSIS package or a Stored procedure (SSIS may actually be easier in a sense, though) to do the following:

1) Find all tables with the ID column as VarChar datatype;

2) Using each of the table names, create a SQL statement to update the column (see attached SQL).


UPDATE @tablename
SET ID = REPLACE(ID, '.', '');

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 1600 total points
ID: 34169257
try this.
use YourDatabase
declare @query table (query nvarchar(max))
declare @sql nvarchar(max)
select @sql = ''
insert @query
select 'update ' + TABLE_NAME + ' set ID = REPLACE(ID,''.'','''')'
  from INFORMATION_SCHEMA.COLUMNS 
 where COLUMN_NAME = 'ID' 
   and DATA_TYPE in ('nvarchar','char','varchar','nchar')
while @sql is not null
begin
set @sql = (select MIN(query) from @query where query > @sql)
if @sql is not null
--print @sql
execute(@sql)
end

Open in new window

0
 
LVL 5

Assisted Solution

by:Zopilote
Zopilote earned 200 total points
ID: 34169270
if the ID is the primary key, you will need to defer validation or disable the foreign keys temporarily before updating the values.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

743 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