Replace huge amount of string data in SQL Server

Question

I have a SQL Server table which contains a NVARCHAR(N) column with URLs. Unfortunatelly the URLs are absolute. A domain has changed and now links are broken (404). This table is really huge (milions of records). I want to replace the domain or try to make the links relative to fix this issue. How to do it as fast as it is possible with no lock on the table?

[EDIT]

I have also tried UPDATE and it works only a little bit longer than INSERT INTO/RENAME.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

UPDATE [TBL] 
SET [TBL].[Url] = REPLACE([TBL].[Url], 'str1', 'str2')
FROM [MyTable] [TBL] WITH(NOLOCK)

It could be done in this way during server maintenance :) Thanks for help!

Its worth noting that the sql you have written will take shared locks. so the nolock is pointless also is the uncommitted isolation level :) — Mark
– Mark, Commented Dec 18, 2015 at 12:16

Mark · Accepted Answer · 2015-12-17 10:46:23Z

3

Create a new table with the replacement result.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT [TBL].[OTHERCOLUMNS], REPLACE([TBL].[URL],'foo.com','bar.org')
INTO ReplacementTBL
FROM [SOURCETBL] [TBL]

GO
SP_RENAME 'schema.SOURCETBL', SOURCETBL_OLD
GO
SP_RENAME 'schema.ReplacementTBL', SOURCETBL

done

edited Dec 17, 2015 at 10:46

answered Dec 17, 2015 at 10:39

Mark

1,5541 gold badge14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace huge amount of string data in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related