用select拼接insert into，单引号转义

SELECT 'INSERT INTO dbo.CMS_Transformation (   TransformationName ,
                                       TransformationCode ,
                                       TransformationType ,
                                       TransformationClassID ,
                                       TransformationVersionGUID ,
                                       TransformationGUID ,
                                       TransformationLastModified ,
                                       TransformationIsHierarchical ,
                                       TransformationHierarchicalXML ,
                                       TransformationCSS ,
                                       TransformationPreferredDocument
                                   )
VALUES ('''+ TransformationName + '''）'
                FROM dbo.CMS_Transformation
WHERE TransformationClassID=3452

2个单引号，代表一个字符

VALUES (|'+ TransformationName + '|）'，用|替换掉上面的''

原始的sql是Values('+name+'),正常的情况拼接，拼接的时候，需要把原来的单引号，替换成两个单引号。

 

本来的','需要先转义成'',''来，然后再在外部分别加上'来表示

SELECT 'INSERT INTO dbo.CMS_Transformation (   TransformationName ,
                                       TransformationCode ,
                                       TransformationType ,
                                       TransformationClassID ,
                                       TransformationVersionGUID ,
                                       TransformationGUID ,
                                       TransformationLastModified ,
                                       TransformationIsHierarchical ,
                                       TransformationHierarchicalXML ,
                                       TransformationCSS ,
                                       TransformationPreferredDocument
                                   )
VALUES ('''+ TransformationName +''','''+TransformationCode+ '''）'
                FROM dbo.CMS_Transformation
WHERE TransformationClassID=3452

 

SELECT * FROM dbo.CMS_Transformation
WHERE TransformationClassID=3452
SELECT 'INSERT INTO dbo.CMS_Transformation (   TransformationName ,
                                       TransformationCode ,
                                       TransformationType ,
                                       TransformationClassID ,
                                       TransformationVersionGUID ,
                                       TransformationGUID ,
                                       TransformationLastModified ,
                                       TransformationIsHierarchical ,
                                       TransformationHierarchicalXML ,
                                       TransformationCSS ,
                                       TransformationPreferredDocument
                                   )
VALUES (''' + TransformationName + ''',''' + TransformationCode + ''','''
       + TransformationType + ''',''' + TransformationClassID + ''','''
       + TransformationVersionGUID + ''',''' + TransformationGUID + ''','''
       + TransformationLastModified + ''',''' + TransformationIsHierarchical
       + ''',''' + TransformationHierarchicalXML + ''','''
       + TransformationCSS + ''',''' + TransformationPreferredDocument
       + '''）'
FROM   dbo.CMS_Transformation
WHERE  TransformationClassID = 3452;

 

 

SELECT 'exec lisa_pi_WebpartInit '''+convert(nvarchar(max), cms_webpartcategory.CategoryGUID)+''', 
'''+ convert(varchar(400),WebpartGUID)+''', 
'''+WebPartName+''', 
'''+WebpartDisplayName+''', 
'''+Replace(CONVERT(NVARCHAR(max),WebpartDescription),'''', '''''')+''', 
'''+WebpartFileName+''', ',
''''+Replace(CONVERT(NVARCHAR(max),WebpartProperties),'''', '''''')+'''', 
','+convert(varchar,isnull(WebpartType,0) )+','+convert(varchar,isnull(WebpartLoadGeneration,0))
FROM cms_webpart
JOIN cms_webpartcategory on (cms_webpart.WebpartCategoryID = cms_webpartcategory.CategoryID) 
WHERE [WebPartCategoryID] IN 
(SELECT [CategoryID] FROM [CMS_WebPartCategory]
WHERE [CategoryParentID] IN (SELECT [CategoryID] FROM [CMS_WebPartCategory] WHERE [CategoryName]='LISA')
AND [CategoryName]!='LISA_Animation'
AND [CategoryName]!='LISA_Custom'
AND [CategoryName]!='LISA_CustomerSupport'
AND [CategoryName]!='LISA_NoUse'
AND [CategoryName]!='LISA_Product')

 

作者:Chuck Lu    GitHub