CODEWITHSUNDEEP
sqlsql-server
user667430
user667430

Reputation: 1547

SQL Server - Insert Select

I have a query where I am trying to select the contents of another row and insert it into the table but changing particular values. Inside this I am trying to use a Replace() function to replace certain characters in the given column.

Is this correct or will I need to take this out and do this via an Update statement?

This is my SQL statement:

INSERT INTO [dbo].[PurchaseLogic]
       ([column1]
       ,[column2]
       ,[column3]
       ,[column4])
SELECT  [column1]
       ,[column2]
       ,replace(column3, 'TextA','TextB')
       ,[column4]
FROM dbo.purchaselogic 
WHERE column1 = 1

hanks

EDIT

Sorry, this is the error I am getting when executing it:

Argument data type text is invalid for argument 1 of replace function.

Upvotes: 0

Views: 120

Answers (5)

Tom Page
Tom Page

Reputation: 1241

The various solutions presented above should make the query work but if you're going to be doing this kind of thing on a regular basis you should at least consider fixing the problem at source and changing the datatype of [dbo].[PurchaseLogic].[column3] to VARCHAR(MAX)

You can do this with an ALTER TABLE command bit I'd probably do it by creating a new table and inserting the data into it

 CREATE TABLE [dbo].[PurchaseLogic_new]
 (
 [column1] int,
 [column2] int,
 [column3] varchar(max),
 [column4] int
 )    
 INSERT INTO dbo.PurchaseLogic_new ([column1],[column2],[column3],[column4] )
 SELECT [column1],[column2],[column3],[column4]  FROM [dbo].[PurchaseLogic]

Once that's done you can swap the names round

 EXEC sp_rename 'dbo.PurchaseLogic','PurchaseLogic_old','OBJECT'
 EXEC sp_rename 'dbo.PurchaseLogic_new','PurchaseLogic','OBJECT'

You can then back out the change by swapping the names back

 EXEC sp_rename 'dbo.PurchaseLogic','PurchaseLogic_new','OBJECT'
 EXEC sp_rename 'dbo.PurchaseLogic_old','PurchaseLogic','OBJECT'

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48392

This looks correct to me and should do what you are wanting to do. But is there some reason why you simply can't try this yourself and check the results?

If Column3 is a data type of 'Text', this isn't going to work since Replace doesn't work on that data type. You could cast the data type is nvarchar(max) to make this work. For example:

Replace(Cast(column3 as varchar(max)),'TextA','TextB')

Upvotes: 2

mohan111
mohan111

Reputation: 8865

INSERT INTO [dbo].[PurchaseLogic]
       ([column1]
       ,[column2]
       ,[column3]
       ,[column4])
SELECT  [column1]
       ,[column2]
       ,cast(replace(cast(column3 as nvarchar(max)),'TextA','TextB') as ntext)
       ,[column4]
FROM dbo.purchaselogic 
WHERE column1 = 1

Upvotes: 0

Donal
Donal

Reputation: 32713

column3 is a TEXT column, you need to cast it as a VARCHAR(MAX) to use the REPLACE function:

INSERT INTO [dbo].[PurchaseLogic]
       ([column1]
       ,[column2]
       ,[column3]
       ,[column4])
SELECT  [column1]
       ,[column2]
       ,REPLACE(CAST(column3 AS VARCHAR(MAX)), 'TextA','TextB')
       ,[column4]
FROM dbo.purchaselogic 
WHERE column1 = 1

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

INSERT INTO [dbo].[PurchaseLogic]
       ([column1]
       ,[column2]
       ,[column3]
       ,[column4])
SELECT  [column1]
       ,[column2]
       ,CAST(REPLACE(CAST(column3 as Varchar(MAX)),'TextA','TextB') AS Text)
       ,[column4]
FROM dbo.purchaselogic 
WHERE column1 = 1

I got the essense of it here

Upvotes: 1

Related Questions