在特定文件组下创建表 WHERE 1=2

2021-09-10 00:00:00 sql tsql sql-server select-into

我有一个 WinForms 应用程序，它根据给定的表格动态创建表格，例如:

I have an WinForms application which creates tables dynamically based on a given table such as:

SELECT * INTO TempTable FROM MyTable WHERE 1=2

我希望使用上述语法在特定的 filegroup 下创建那些 Temp 表.

I want those Temp tables to be created under a specific filegroup though using the above syntax.

在文件组下创建表的语法是:

The syntax to create the table under a filegroup is:

CREATE TABLE [dbo].[TempTable](

            [RECORDID] [numeric](10, 0) NOT NULL,
            --etc etc

) ON [TempFileGroup] TEXTIMAGE_ON [TempFileGroup]

是否可以使用我上面的语法在特定文件组下创建表?

Is it possible to use my syntax above to create the table under the specific filegroup?

推荐答案

我希望使用上述语法在特定文件组下创建这些临时表

从 SQL Server 2017+ 你可以使用 ON filegroup 语法.

From SQL Server 2017+ you could use ON filegroup syntax.

INTO子句

SELECT...INTO 在默认文件组中创建一个新表，并将查询的结果行插入其中.

SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it. 

[ INTO new_table ]
[ ON filegroup]

文件组

指定将在其中创建新表的文件组的名称.指定的文件组应该存在于数据库中，否则 SQL Server 引擎会抛出错误.仅从 SQL Server 2017 开始支持此选项.

Specifies the name of the filegroup in which new table will be created. The filegroup specified should exist on the database else the SQL Server engine throws an error. This option is only supported beginning with SQL Server 2017.

来自 MSDN 的示例:

Example from MSDN:

创建一个新表作为另一个表的副本并将其加载到指定的文件组

Creating a new table as a copy of another table and loading it a specified filegroup

ALTER DATABASE [AdventureWorksDW2016] ADD FILEGROUP FG2;
ALTER DATABASE [AdventureWorksDW2016]
ADD FILE
(
NAME='FG2_Data',
FILENAME = '/var/opt/mssql/data/AdventureWorksDW2016_Data1.mdf'
)
TO FILEGROUP FG2;
GO
SELECT *  INTO [dbo].[FactResellerSalesXL] ON FG2 from [dbo].[FactResellerSales]

相关文章