Solved

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

Posted on 2010-11-18
3
345 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
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 40

Accepted Solution

by:
Sharath earned 400 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 50 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 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

10 Experts available now in Live!

Get 1:1 Help Now