CODEWITHSUNDEEP
sql-servert-sql
Chris Chadwick
Chris Chadwick

Reputation: 607

How do I Switch single quotes to double single quotes on output from T-SQL SELECT statement

I am trying to script the creation of the INSERT SQL for data in a table. (similar to the SQL Server built in scripts)

DECLARE @HEADER varchar(255)

SET @HEADER = 'insert into Table ( Column1 , Column2)
values ( N'''

SELECT 
@Header + Column1 + ''', N''' + Column2 + ''')'
+ CHAR(10)
FROM Table
ORDER BY Column1

In Column1 I have text that contains single quotes and I need it to come out as double single quoted text like it does when using "Generate scripts" directly from SQL Server.

Upvotes: 1

Views: 563

Answers (3)

Phil Hunt
Phil Hunt

Reputation: 8541

Use the REPLACE function to double up your quotes, and you'll need to wrap the resulting string in quotes. Note that this works for NOT NULL columns, so tweak it if you need to support nullable columns.

SELECT
  'INSERT INTO Table (Column1, Column2) VALUES (N''' +
     REPLACE(Column1, '''', '''''') + ''', N''' +
     REPLACE(Column2, '''', '''''') + ''')'
FROM Table
ORDER BY Column1

Upvotes: 4

codingbadger
codingbadger

Reputation: 43984

I am guessing you want to generate an insert script based on an existing table?

I would recommend you download SSMS Tools. It's a great add in with additional features for use with SSMS. One of these features allows you to right click a table and it will generate an insert script for you that contains the data within the table.

Upvotes: 0

bobs
bobs

Reputation: 22194

You can use the QUOTENAME function.

SELECT 
@Header + QUOTENAME(Column1) + ''', N''' + Column2 + ''')'
+ CHAR(10)
FROM Table
ORDER BY Column1

Upvotes: 2

Related Questions