SQL Server Replace data of one table with data of other table
One of the questions asked in a forum was about replacing data of one table by the data of another table
The solution that the questioner used was having cursor by looping through the source table and replace particular words by other words from another table by matching the words
The solution that the questioner used was having cursor by looping through the source table and replace particular words by other words from another table by matching the words
Here is my alternate solution
-- Method 1:
-- Method 1:
declare @Datatable table(Field1 nvarchar(50))
insert into @Datatable (Field1)select 'testing for this' union allselect 'testing for this to'
declare @MatchTable table(LookFor nvarchar(50),Replacewith nvarchar(50))insert into @MatchTable (LookFor,Replacewith)select 'for','with' union all
select 'to','no'
select 'to','no'
declare @replace varchar(8000),@with varchar(8000),@sql varchar(8000)
select @replace='',@with ='',@sql=''
select
@replace =@replace +'replace(', @with =@with +''''+Lookfor +''','''+Replacewith+'''),' from
@MatchTable
@replace =@replace +'replace(', @with =@with +''''+Lookfor +''','''+Replacewith+'''),' from
@MatchTable
select @replace=@replace +'''~!@#'',',@with =SUBSTRING(@with,1,len(@with)-1)
select @sql=@sql+REPLACE('select '+@replace+@with,'~!@#',Field1)+' union all ' from @Datatable
select @sql=SUBSTRING(@sql,1,len(@sql)-10)
select * from @Datatable
exec(@sql)
-- Method 2 : INSERT INTO SELECT
USE AdventureWorks2012
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Person
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO
---------------------------------------------------------
---------------------------------------------------------
-- Method 3 : SELECT INTO
USE AdventureWorks2012
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Person
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO