使用动态sql创建表-Java 学习之路

我想在存储过程中使用动态SQL来创建表 .

这是对存储过程的调用：

EXEC [spCreateAColDiffTable] 'hq193.dbo.arch_con_col_s193_s202'

以下是存储过程的相关部分：

CREATE PROCEDURE sp_createAColDiffTable (@table_name nvarchar(128))

...

SET @sSQL = 'CREATE TABLE ' + @table_name + ' ( ' +
' [table_name]       VARCHAR (128) NOT NULL, ' +
' [column_name]      VARCHAR (128) NULL, ' +
' [what_changed]     VARCHAR (128) NULL, ' +
' [sr_data_type]     VARCHAR (128) NOT NULL, ' +
' [tr_data_type]     VARCHAR (128) NOT NULL, ' +
' [sr_max_length]    SMALLINT NOT NULL, ' +
' [tr_max_length]    SMALLINT NOT NULL, ' +
' [sr_is_nullable]   CHAR NULL, ' +
' [tr_is_nullable]   CHAR NULL, ' +
' [sr_precision]     SMALLINT NULL, ' +
' [tr_precision]     SMALLINT NULL, ' +
' [sr_scale]         SMALLINT NULL, ' +
' [tr_scale]         SMALLINT NULL ) ' +
' ON [PRIMARY] WITH (DATA_COMPRESSION = NONE)'
PRINT @sSQL
Exec @sSQL

走

当我运行存储过程时，我收到错误：

SQL Server数据库错误：名称'CREATE TABLE hq193.dbo.arch_con_col_s193_s202（[table_name] VARCHAR（128）NOT NULL，[column_name] VARCHAR（128）NULL，[what_changed] VARCHAR（128）NULL，[sr_data_type] VARCHAR（ 128）NOT NULL，[tr_data_type] VARCHAR（128）NOT NULL，[sr_max_length] SMALLINT NOT NULL，[tr_max_length] SMALLINT NOT NULL，[sr_is_nullable] CHAR NULL，[tr_is_nullable] CHAR NULL，[sr_precision] SMALLINT NULL，[tr_precision] SMALLINT NULL，[sr_scale] SMALLINT NULL，[tr_scale] SMALLINT NULL）ON [PRIMARY] WITH（DATA_COMPRESSION = NONE）'不是有效的标识符 .

请注意，在执行存储过程之前，我打印出了SQL . 如果我将打印的SQL剪切并粘贴到查询编辑器窗口中，它可以正常工作 .

我错过了什么？

2 回答

13
试试这样：
```
EXEC(@sSQL)
```
这是一个非常常见的错误 . 如果没有括号， EXEC @sSQL 表示“执行名称在@sSQL变量中的存储过程", rather than what you want which is probably "在变量@sSQL中执行命令字符串” .
回复于 2024-04-19T03:09:32+08:00

我看到这是一篇旧帖子，但是我有一个类似的问题，我需要阅读一个文本文件，其中列可能会根据文件的拉取方式而变化越来越少 . 所以我编写了一个程序来读取文本文件并将其放入动态创建的临时表中，我可以在其中处理输出 .

也许这可以帮助别人..

DECLARE @NUM_COL    AS INT
DECLARE @I          AS INT
DECLARE @CREATE_TBL AS NVARCHAR(MAX)
DECLARE @DATA       AS NVARCHAR (MAX)
DECLARE @XML_ROW    AS XML
DECLARE @MAX_CHAR   AS INT

--Sets the column max characters for temp table ##FILE_TABLE
SET @MAX_CHAR = 1000

--File import of data as single rows, no columns
IF OBJECT_ID('tempdb..#FILE_ROWS') IS NOT NULL
DROP TABLE #FILE_ROWS

CREATE TABLE #FILE_ROWS
    ( [Row_data]  NVARCHAR(MAX) NULL )


--Global temp table used because the table is being built dynamically.
IF OBJECT_ID('tempdb..##FILE_TABLE') IS NOT NULL
DROP TABLE ##FILE_TABLE

--This is only so the debugger thinks the table is created when referenced in later SQL code.
IF 1 <> 1 CREATE TABLE ##FILE_TABLE (X INT)

BULK INSERT #FILE_ROWS
FROM 'C:\Users\Wayne\Desktop\777434633016764.txt'
WITH
(
  FIELDTERMINATOR = '\t'  --Tab Delimited
 ,ROWTERMINATOR = '\n'
)

--Figures out how many columns were in the file.
SET @NUM_COL = (SELECT MAX(LEN(Row_data) - LEN(REPLACE(Row_data, CHAR(9), ''))) + 1 AS [NO_COL] FROM #FILE_ROWS)

SET @CREATE_TBL = 'CREATE TABLE ##FILE_TABLE ( ID INT IDENTITY(1,1),'
SET @I = 1

Declare COUNTER Cursor for
        SELECT 
            CAST('<A>' + REPLACE(Row_data, CHAR(9), '</A><A>') + '</A>' AS XML)
        FROM #FILE_ROWS
open COUNTER
         fetch next from COUNTER into @XML_ROW
         while @@fetch_Status != -1
         begin

            IF @I = 1
            BEGIN
                SELECT @CREATE_TBL = @CREATE_TBL 
                                     + '[' + REPLACE(dbo.Trim(DATA.value('.','char(30)')), N'''', '`')
                                     + ']' + ' NVARCHAR(' + CAST(@MAX_CHAR AS NVARCHAR(5)) + ') NULL,' 
                FROM @XML_ROW.nodes('/A') AS x(DATA)

                SET @CREATE_TBL = LEFT(@CREATE_TBL, LEN(@CREATE_TBL) - 1) + ')'

                EXEC(@CREATE_TBL)

                SET @I = 2
            END
          --ELSE  --If you do not want the column names in the first row, remove the ELSE
            BEGIN
                SET @DATA = 'INSERT INTO ##FILE_TABLE SELECT '
                SELECT @DATA = @DATA 
                               + '''' + REPLACE(dbo.Trim(DATA.value('.','char(30)')), N'''', '`') 
                               + '''' + ','
                FROM @XML_ROW.nodes('/A') AS x(DATA)

                SET @DATA = LEFT(@DATA, LEN(@DATA) -1)

                EXEC(@DATA)
            END

            FETCH NEXT FROM COUNTER INTO @XML_ROW
         END
CLOSE COUNTER
DEALLOCATE COUNTER

SELECT * from ##FILE_TABLE

回复于 2024-04-19T03:09:32+08:00

使用动态sql创建表

2 回答

相关问题