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

Posted on 2010-11-18
Medium Priority
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


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.  


Question by:robthomas09
LVL 22

Assisted Solution

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

LVL 41

Accepted Solution

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,''.'','''')'
 where COLUMN_NAME = 'ID' 
   and DATA_TYPE in ('nvarchar','char','varchar','nchar')
while @sql is not null
set @sql = (select MIN(query) from @query where query > @sql)
if @sql is not null
--print @sql

Open in new window


Assisted Solution

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.

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

607 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