从 Excel 导入 SQL Server 2008 中删除添加到列名的引号 [英] Removing quotes added to column names from Excel import SQL Server 2008

问题描述

我注意到当我使用 SSMS 将 Excel 电子表格导入 SQL Server 时添加了引号.我在某处读到过，无论出于何种原因，Excel 都需要这样做.一旦在 SQL Server 中，列名周围的这些引号就没有用了，我想有一种编程方式来删除它们.我试图做的最接近的事情是 EXEC sp_rename 'Table.["withquotes"]', NewColumnName, 'replace(Table.["withquotes",'"',''].我想遍历表中的所有列名，并在这些列名包含引号的任何地方使用 replace 函数.有没有一种典型的惯用方法这样做吗?

I've noticed that when I use SSMS to import an Excel spreadsheet into SQL Server quotation marks are added. I've read somewhere that for whatever reason it's necessary for Excel to do this. Once in SQL Server, these quotes around the column names are useless and I'd like to have a programmatic way to remove them. The closest thing, which doesn't work, that I have tried to make is EXEC sp_rename 'Table.["withquotes"]', NewColumnName, 'replace(Table.["withquotes",'"','']. I'd like to loop through all of the column names in a table and use the replace function wherever a those column names contain quotation marks. Is there a typical, idiomatic way to do this?

推荐答案

我相信这应该会有所帮助...

I believe this should help...

DECLARE @tbl sysname, @col sysname
DECLARE @cmd nvarchar(max)

DECLARE cCol CURSOR FOR
    SELECT TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '"%"'

OPEN cCol
FETCH NEXT FROM cCol INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN
  SET @cmd = 
    N'EXEC sp_rename ''[' + @tbl + '].[' + @col + ']'', ' + 
    '''' + REPLACE(@col, '"', '') + N''', ''COLUMN''' 

  --PRINT @cmd

  EXEC sp_executeSQL @cmd

  FETCH NEXT FROM cCol INTO @tbl, @col
END

CLOSE cCol 
DEALLOCATE cCol

这篇关于从 Excel 导入 SQL Server 2008 中删除添加到列名的引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！