Solved

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

Posted on 2010-11-18
3
341 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
3 Comments
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 50 total points
Comment Utility
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 40

Accepted Solution

by:
Sharath earned 400 total points
Comment Utility
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 50 total points
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Join Query 12 47
oracle query help 18 74
Query criteria to get previous month's data not working 3 25
Help with SQL Query 23 39
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 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

12 Experts available now in Live!

Get 1:1 Help Now