SQL 使用动态列创建表

Question

提问by Billa

I need to insert some data into a temp table.

我需要将一些数据插入到临时表中。

I have some conditional based columns like Salary, Code.

我有一些基于条件的列，例如Salary,Code。

How can I create a table for conditional based columns? I don't want to useSELECT INTO #tempTable

如何为基于条件的列创建表？我不想使用SELECT INTO #tempTable

Here is the code:

这是代码：

DECLARE @sql NVARCHAR(MAX)
,@sqlSelect NVARCHAR(MAX) = ''
,@sqlFrom NVARCHAR(MAX) =''

CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100))


SET @sqlSelect ='INSERT INTO #myTempTable
SELECT EMP.Id, EMP.DeptId, EMP.DeptName'

SET @sqlFrom =' FROM dbo.EMPLOYEE AS EMP'

IF (someCondition)
BEGIN
    SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code'            
END

SET @sql =  @sqlSelect +@sqlFrom 

EXEC sp_executesql @sql

Any help/suggestion on how better i can make this?

关于我如何做得更好的任何帮助/建议？

Update:

更新：

Initially I used SELECT INTO #TempTablewithout specifying no of columns, As SQL Azure not supporting that, I decided to go with INSERT INTO. But not sure how can I add dynamic columns in a defined structure already. Its fully dynamic SQL :(

最初我使用时SELECT INTO #TempTable没有指定列数，由于 SQL Azure 不支持，我决定使用INSERT INTO. 但不确定如何在已定义的结构中添加动态列。它的完全动态 SQL :(

Answer 1

回答by rhughes

How many dynamic columns do you need?

您需要多少个动态列？

What about having a column called Dynamicof type nvarchar(MAX)or something of the like, and then you can just put your data in there and format as appropriate.

如果有一个名为Dynamictypenvarchar(MAX)或类似的列，然后您可以将数据放在那里并根据需要进行格式化。

Another option is to create a table with NULLcolumns.

另一种选择是创建一个带有NULL列的表。

To do this in SQL, you can do the following:

要在 SQL 中执行此操作，您可以执行以下操作：

CREATE TABLE tblPerson
(
    PersonId INT,
    FirstName NVARCHAR(256),
    LastName NVARCHAR(256) NULL,
    PRIMARY KEY (PersonId)
)

Notice the NULLcolumn above also.

NULL还要注意上面的列。

See here for further explanation on creating tables with Primary Keysand NULLcolumns in SQL:

有关在 SQL 中使用Primary Keys和NULL列创建表的进一步说明，请参见此处：

Create tables in SQL

在 SQL 中创建表

Answer 2

回答by Shock

As alluded to, your best bet is to likely create all of the columns that you will need and make them NULLable if optional. So:

如前所述，您最好的办法是创建您需要的所有列，并在可选时将它们设为 NULLable。所以：

CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100), Salary INT NULL, EmpCode VARCHAR(45) NULL);

And

和

IF (someCondition)
BEGIN
    SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code'            
END
ELSE
BEGIN
    SET @sqlSelect = @sqlSelect +', NULL, NULL'            
END

Answer 3

回答by Sandesh

use the code and tell me is it working or not ??

使用代码并告诉我它是否有效？

-----------------------------------------------------

DECLARE @sql NVARCHAR(MAX)
,@sqlSelect NVARCHAR(MAX) = ''

,@sqlFrom NVARCHAR(MAX) =''

IF (someCondition)
BEGIN
    CREATE TABLE #tblInfo (Id INT, DeptId INT, DeptName VARCHAR(100))    
END
ELSE
BEGIN
    CREATE TABLE #tblInfo (Id INT, DeptId INT, DeptName VARCHAR(100),Salary numeric(18,2), Code INT)    
END

SET @sqlSelect ='INSERT INTO #tblInfo 
SELECT EMP.Id, EMP.DeptId, EMP.DeptName'

SET @sqlFrom =' FROM dbo.EMPLOYEE AS EMP'

IF (someCondition)
BEGIN
    SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code'            
END

SET @sql =  @sqlSelect +@sqlFrom 

EXEC sp_executesql @sql

------------------------------------

I hope it will work

我希望它会起作用

Answer 4

回答by Valentin Petkov

If this table is BIG I think your and in your logic you make JOINS with this table you need to create indexes in TARGET TABLE

如果这张表很大，我认为你和在你的逻辑中你用这张表进行 JOINS 你需要在 TARGET TABLE 中创建索引

You can go around with Physical table not TEMP

您可以使用物理表而不是 TEMP

DECLARE @guid NVARCHAR(64), @sql NVARCHAR(MAX)
,@sqlSelect NVARCHAR(MAX) = ''
,@sqlFrom NVARCHAR(MAX) ='', @sqlSchema NVARCHAR(MAX) =''

SET @guid = NEWID()  

declare @tableName NVARCHAR(64)= 'myTempTable'+ @guid

select @tableName

--use some transaction

set @sql = 'CREATE TABLE ' + @tableName  
set @sqlSchema = '(Id INT, DeptId INT, DeptName VARCHAR(100)'  -- DO INDEXES IF YOU ARE JOIN 

SET @sqlSelect ='INSERT INTO ' + @tableName +
'SELECT EMP.Id, EMP.DeptId, EMP.DeptName'


IF (1=1) -- your condition
BEGIN
  SET @sqlSchema = @sqlSchema +', EMP.Salary, EMP.Code'            
END

SET @sqlSchema =   @sqlSchema + ')' -- close last )

set @sql =  @sql +@sqlSchema --create the TargetTable


--DO YOUR logic

EXEC sp_executesql @sql

set @sql = N'DROP TABLE ' + @tableName

EXEC sp_executesql @sql

Answer 5

回答by user3605493

Try This.

尝试这个。

Begin Tran


IF (someCondition)

    Begin 
      CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100), Salary money , Code Int)

        Insert Into #myTempTable(Id, DeptId, DeptName, Salary, Code)
        Select Emp.Id, Emp.DeptID, Emp.DeptName, EMP.Salary, EMP.Code 
        From dbo.Employee As Emp

        Select Id, DeptID, DeptName, Salary, Code From dbo.Employee
              Drop Table #myTempTable     
    End

    Else

    Begin

      CREATE TABLE #myTempTable2 (Id INT, DeptId INT, DeptName VARCHAR(100))

        Insert Into #myTempTable(Id, DeptId, DeptName)
        Select Emp.Id, Emp.DeptID, Emp.DeptName 
        From dbo.Employee As Emp

        Select Id, DeptID, DeptName From dbo.Employee

                    Drop Table #myTempTable2 
    End



Commit Tran

Answer 6

回答by Alex Peshik

Here is another solution with completely dynamic columns:

这是另一个具有完全动态列的解决方案：

DECLARE @sql NVARCHAR(MAX)
,@sqlTableName NVARCHAR(MAX) = ''
,@sqlColumnsDefinitions NVARCHAR(MAX) =''
,@sqlColumnsList NVARCHAR(MAX) =''
,@sqlFrom NVARCHAR(MAX) =''

SET @sqlTableName = 'myTempTable'
SET @sqlFrom = 'FROM dbo.EMPLOYEE AS EMP'

--conditions to define columns
IF (1=1)
    set @sqlColumnsDefinitions='Col1 int, Col2 int, Col3 int'
else
    set @sqlColumnsDefinitions='Col1 int, Col2 int, Col3 int, Col4 int, Col5 int'

--new table with dynamic fields creation
SET @sql = 'if (object_id('''+@sqlTableName+''') is not null) DROP TABLE '+@sqlTableName+'
CREATE TABLE '+@sqlTableName+' ('+@sqlColumnsDefinitions+')'+char(10)
print (@sql)
exec (@sql)

--get columns list
select @sqlColumnsList=STUFF((SELECT ',' + column_name
from INFORMATION_SCHEMA.COLUMNS where table_name like @sqlTableName+'%' FOR XML PATH('')), 1, 1, '')

--main insert
SET @sql = 'INSERT INTO '+@sqlTableName+char(10)+'SELECT '+@sqlColumnsList+char(10)+@sqlFrom
print (@sql)
exec  (@sql)

This won't work if you substite myTempTable with #myTempTable because of SQL engine. To fix it and get correct column names list you may use some tricks that are out of scope. But still it works with non-temp tables.

如果由于 SQL 引擎而用 #myTempTable 替换 myTempTable，这将不起作用。要修复它并获得正确的列名列表，您可能会使用一些超出范围的技巧。但它仍然适用于非临时表。

Answer 7

回答by Suresh

See example below:

请参阅下面的示例：

declare @sql nvarchar(400)
declare @ColFlag int
set @ColFlag = 1
if(@ColFlag = 0)

set  @sql= 'CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100)); 
    INSERT INTO #myTempTable SELECT 1,2,''DeptName1''; 
    SELECT * FROM #myTempTable'
else
set  @sql= 'CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100), MyNewColHere VARCHAR(25)); 
    INSERT INTO #myTempTable SELECT 1,2,''DeptName1'', ''MyNewColHereValue''; 
    SELECT * FROM #myTempTable'

 exec (@sql)

Answer 8

回答by Pratik Gohil

Below code may work for you.

下面的代码可能对你有用。

DECLARE @sql NVARCHAR(MAX)
,@sqlSelect NVARCHAR(MAX) = ''

,@sqlFrom NVARCHAR(MAX) =''

IF (someCondition)
BEGIN
    CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100))    
END
ELSE
BEGIN
    CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100),Salary numeric(18,2), Code INT)    
END

SET @sqlSelect ='INSERT INTO #myTempTable
SELECT EMP.Id, EMP.DeptId, EMP.DeptName'

SET @sqlFrom =' FROM dbo.EMPLOYEE AS EMP'

IF (someCondition)
BEGIN
    SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code'            
END

SET @sql =  @sqlSelect +@sqlFrom 

EXEC sp_executesql @sql

Answer 9

回答by Stephen

You could just alter the temp table when the condition is met (I'm assuming you know the columns required at this point?)

您可以在满足条件时更改临时表（我假设您知道此时所需的列？）

IF (someCondition)
BEGIN
    ALTER TABLE #myTempTable ADD Salary VARCHAR(20), Code VARCHAR(20)
    SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code'            
END

You may want to test if the column exists first, if you have more than one condition that could be activated - which you can do like so:

您可能想先测试该列是否存在，如果您有多个可以激活的条件 - 您可以这样做：

IF COL_LENGTH('#myTemptable','Salary') IS NULL
BEGIN
     -- do something if salary doesn't exist
     ALTER TABLE #myTempTable ADD Salary NUMERIC(6,2)
END

As others have noted, you could create the columns upfront as nullable - or create the table after the condition is met.

正如其他人所指出的，您可以预先将列创建为可为空的 - 或者在满足条件后创建表。

Answer 10

回答by MichaelSc

Can you use the

你能用

sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

command to change the name of a previously defined place holder column.
For example, create the #tempTablewith extra fields such as "VCHAR100_1", "BOOL_1", "Int_1", etc. If you need to use the field, rename it to the desired name so your code later on can refer to the column by a non-cryptic name.

命令更改先前定义的占位符列的名称。
例如，创建一个#tempTable额外的字段，如"VCHAR100_1"，"BOOL_1"，"Int_1"，等。如果你需要使用领域，将其重命名为所需的名称，以便您的代码以后可以由非神秘的名字指的是列。

SQL 使用动态列创建表

提问by Billa

回答by rhughes

回答by Shock

回答by Sandesh

回答by Valentin Petkov

回答by user3605493

回答by Alex Peshik

回答by Suresh

回答by Pratik Gohil

回答by Stephen

回答by MichaelSc

相关推荐

最近更新

标签

SQL 使用动态列创建表

提问by Billa

回答by rhughes

回答by Shock

回答by Sandesh

回答by Valentin Petkov

回答by user3605493

回答by Alex Peshik

回答by Suresh

回答by Pratik Gohil

回答by Stephen

回答by MichaelSc

相关推荐

在 Oracle 中将 SQL 行转换为逗号分隔值

在 Microsoft SQL Server 中比较日期的最佳方法是什么？

SQL 使用 sqlplus 在 Oracle 中插入多行字符串

使用 SQL*Loader 更新表中的列？

相关推荐

最近更新

标签