“Scrubbing Names via SQL Query/Batch” ( Simple Solution to Use CTEs in One Run)


Here is one solution posted on stackexchange:

https://dba.stackexchange.com/questions/11719/scrubbing-names-via-sql-query-batch

But you can use the two CTEs to update the table without using a temp table.

  

ALTER TABLE [mytable] DISABLE TRIGGER ALL
;with mycte as (

SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID,
 [mytableid],[firstname],[lastname]	      
  FROM  [dbo].[mytable] e  
   WHERE  ([lastname] is not null  or [lastname]='')  and ([firstname] is not null or [firstname]='')
  )
  ,mycte1 as 
  ( 
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID,
 [mytableid] ,[firstname] ,[lastname]  FROM  [dbo].[mytable] 
 WHERE   ([lastname] is not null  or [lastname]='')  and ([firstname] is not null or [firstname]='')
  ) 
  Merge mycte1 e using mycte m on e.ID=m.ID
  WHEN  matched then 
  update
Set  e.FirstName = m.FirstName, 
e.LastName = m.LastName ;

ALTER TABLE [mytable] ENABLE TRIGGER ALL

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s