[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

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

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
robthomas09
Asked:
robthomas09
3 Solutions
 
8080_DiverCommented:
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
 
SharathData EngineerCommented:
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
 
ZopiloteCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now