Insert single qoutes into a column for a SQL table

Question

Insert single qoutes into a column for a SQL table

Culbertson Geoffrey 60

This seems simple enough but I am struggling with how to do it. I need add single quotes around all the records in a column call Manager in a table called ChronusExport. I know how to add them in a select statement but not how to do a bulk update to all the records in that table.

Yitzhak Khabinsky 26,486 Reputation points

2023-08-25T15:18:55.7666667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

Culbertson Geoffrey 60

This is the Select Statement -

SELECT [Employee ID]
      ,[First Name]
      ,[Middle Name]
      ,[Last Name]
      ,[Display Name]
      ,[Email Address]
      ,[Manager]
      ,[Manager User Sys ID]
      ,[Job Title]
      ,[Location]
      ,[Division]
      ,[Department Code]
      ,[Department]
      ,[Company]
      ,[Hire Date]
      ,[Pay Scale]
  FROM [Chronus].[dbo].[ChronusExport]

2. I have not been able to come up with anything that updates the table just this - which add it to the output of a select statement -
SELECT [Employee ID]       ,Concat([First Name],' ',[Middle Name]) AS [First Name]       ,[Last Name]       ,[Display Name]       ,[Email Address]      ,QUOTENAME(Manager, '''') AS Manager        ,[Job Title]       ,[Location]       ,[Division]       ,[Department Code]       ,[Department]       ,[Company]       ,[Hire Date]       ,[Pay Scale] 	    FROM [Chronus].[dbo].[ChronusExport]

3. Adding single quotes to the Manager column

4. Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64)   Aug  7 2021 01:20:37   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Accepted answer

0 additional answers

Your answer

Yitzhak Khabinsky 26,486 Reputation points

2023-08-25T15:18:55.7666667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
Culbertson Geoffrey 60 Reputation points

2023-08-25T15:24:34.47+00:00

This is the Select Statement -

SELECT [Employee ID] ,[First Name] ,[Middle Name] ,[Last Name] ,[Display Name] ,[Email Address] ,[Manager] ,[Manager User Sys ID] ,[Job Title] ,[Location] ,[Division] ,[Department Code] ,[Department] ,[Company] ,[Hire Date] ,[Pay Scale] FROM [Chronus].[dbo].[ChronusExport] 2. I have not been able to come up with anything that updates the table just this - which add it to the output of a select statement - SELECT [Employee ID] ,Concat([First Name],' ',[Middle Name]) AS [First Name] ,[Last Name] ,[Display Name] ,[Email Address] ,QUOTENAME(Manager, '''') AS Manager ,[Job Title] ,[Location] ,[Division] ,[Department Code] ,[Department] ,[Company] ,[Hire Date] ,[Pay Scale] FROM [Chronus].[dbo].[ChronusExport] 3. Adding single quotes to the Manager column 4. Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Answer 1

Erland Sommarskog 120.2K MVP

So use the same SELECT statement, but just add INSERT on top;

INSERT (col1, col2, col3, ..)
SELECT ..., quotname(Manager, ''''), ...

Note that quotename will return NULL if the input is more than 128 characters.

Note also that qoutename will also double any single quotes in the Manager column. So Brian O'Brien will result in 'Brian O''Brien'.

Culbertson Geoffrey 60 Reputation points

2023-08-25T15:56:54.47+00:00

That did the trick -

Insert ChronusExport (Manager)

Select QUOTENAME(Manager, '''')

FROM [Chronus].[dbo].[ChronusExport]

Share via

Insert single qoutes into a column for a SQL table

0 additional answers

Your answer