T-SQL 如何在存储过程中动态创建表? [英] T-SQL How to create tables dynamically in stored procedures?
问题描述
像这样的代码，但它是错误的:
Code like this, but it's wrong:
CREATE PROC sp_createATable
@name VARCHAR(10),
@properties VARCHAR(500)
AS
CREATE TABLE @name
(
id CHAR(10) PRIMARY KEY,
--...Properties extracted from @properties
);
你能告诉我如何处理吗?真的让我很困扰.
Could you tell me how to deal with it? It really troubles me.
推荐答案
您正在使用表变量，即您应该声明该表.这不是临时表.
You are using a table variable i.e. you should declare the table. This is not a temporary table.
您可以像这样创建临时表:
You create a temp table like so:
CREATE TABLE #customer
(
Name varchar(32) not null
)
你像这样声明一个表变量:
You declare a table variable like so:
DECLARE @Customer TABLE
(
Name varchar(32) not null
)
请注意临时表是使用# 声明的，而表变量是使用@ 声明的.去阅读表变量和临时表之间的区别.
Notice that a temp table is declared using # and a table variable is declared using a @. Go read about the difference between table variables and temp tables.
更新:
根据您在下面的评论，您实际上是在尝试在存储过程中创建表.为此，您需要使用动态 SQL.基本上动态 SQL 允许您以字符串的形式构造 SQL 语句，然后执行它.这是您能够在存储过程中创建表的唯一方法.我将向您展示如何并讨论为什么这通常不是一个好主意.
Based on your comment below you are actually trying to create tables in a stored procedure. For this you would need to use dynamic SQL. Basically dynamic SQL allows you to construct a SQL Statement in the form of a string and then execute it. This is the ONLY way you will be able to create a table in a stored procedure. I am going to show you how and then discuss why this is not generally a good idea.
现在举一个简单的例子(我没有测试过这段代码，但它应该能很好地告诉你如何去做):
Now for a simple example (I have not tested this code but it should give you a good indication of how to do it):
CREATE PROCEDURE sproc_BuildTable
@TableName NVARCHAR(128)
,@Column1Name NVARCHAR(32)
,@Column1DataType NVARCHAR(32)
,@Column1Nullable NVARCHAR(32)
AS
DECLARE @SQLString NVARCHAR(MAX)
SET @SQString = 'CREATE TABLE '+@TableName + '( '+@Column1Name+' '+@Column1DataType +' '+@Column1Nullable +') ON PRIMARY '
EXEC (@SQLString)
GO
这个存储过程可以这样执行:
This stored procedure can be executed like this:
sproc_BuildTable 'Customers','CustomerName','VARCHAR(32)','NOT NULL'
这种类型的存储过程存在一些主要问题.
There are some major problems with this type of stored procedure.
将很难满足复杂的表格.想象一下下面的表结构:
Its going to be difficult to cater for complex tables. Imagine the following table structure:
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [nvarchar](64) NOT NULL,
[CustomerSUrname] [nvarchar](64) NOT NULL,
[CustomerDateOfBirth] [datetime] NOT NULL,
[CustomerApprovedDiscount] [decimal](3, 2) NOT NULL,
[CustomerActive] [bit] NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [DF_Customers_CustomerApprovedDiscount] DEFAULT ((0.00)) FOR [CustomerApprovedDiscount]
GO
这个表比第一个例子复杂一点，但不是很多.存储过程的处理要复杂得多.因此，虽然这种方法可能适用于小表，但很快就会变得难以管理.
This table is a little more complex than the first example, but not a lot. The stored procedure will be much, much more complex to deal with. So while this approach might work for small tables it is quickly going to be unmanageable.
创建表格需要计划.创建表时，应将它们战略性地放置在不同的文件组中.这是为了确保您不会导致磁盘 I/O 争用.如果所有内容都在主文件组上创建，您将如何解决可扩展性问题?
Creating tables require planning. When you create tables they should be placed strategically on different filegroups. This is to ensure that you don't cause disk I/O contention. How will you address scalability if everything is created on the primary file group?
能否解释一下为什么需要动态创建表?
Could you clarify why you need tables to be created dynamically?
更新 2:
由于工作量而延迟更新.我阅读了您关于需要为每家商店创建一张桌子的评论，我认为您应该像我将要给您的示例一样进行操作.
Delayed update due to workload. I read your comment about needing to create a table for each shop and I think you should look at doing it like the example I am about to give you.
在这个例子中，我做了以下假设:
In this example I make the following assumptions:
- 这是一个拥有众多商店的电子商务网站
- 一家商店可以出售许多商品(商品).
- 可以在许多商店出售特定商品(商品)
- 一家商店会对不同的商品(商品)收取不同的价格
- 所有价格均以 $ (USD) 为单位
假设该电子商务网站销售游戏机(即 Wii、PS3、XBOX360).
Let say this e-commerce site sells gaming consoles (i.e. Wii, PS3, XBOX360).
看看我的假设，我看到了一种经典的多对多关系.一个商店可以出售许多物品(商品)，并且可以在许多商店出售物品(商品).让我们把它分解成表格.
Looking at my assumptions I see a classical many-to-many relationship. A shop can sell many items (goods) and items (goods) can be sold at many shops. Let's break this down into tables.
首先，我需要一个商店表来存储有关商店的所有信息.
First I would need a shop table to store all the information about the shop.
一个简单的商店表可能如下所示:
A simple shop table might look like this:
CREATE TABLE [dbo].[Shop](
[ShopID] [int] IDENTITY(1,1) NOT NULL,
[ShopName] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_Shop] PRIMARY KEY CLUSTERED
(
[ShopID] ASC
) WITH (
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
让我们将三个商店插入数据库以在我们的示例中使用.以下代码将插入三个商店:
Let's insert three shops into the database to use during our example. The following code will insert three shops:
INSERT INTO Shop
SELECT 'American Games R US'
UNION
SELECT 'Europe Gaming Experience'
UNION
SELECT 'Asian Games Emporium'
如果您执行SELECT * FROM Shop，您可能会看到以下内容:
If you execute a SELECT * FROM Shop you will probably see the following:
ShopID ShopName
1 American Games R US
2 Asian Games Emporium
3 Europe Gaming Experience
好的，现在让我们进入 Items(货物)表.由于物品/商品是各种公司的产品，因此我将称其为表产品.您可以执行以下代码来创建一个简单的 Product 表.
Right, so now let's move onto the Items (goods) table. Since the items/goods are products of various companies I am going to call the table product. You can execute the following code to create a simple Product table.
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductDescription] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
让我们用一些产品填充产品表.执行以下代码插入一些产品:
Let's populate the products table with some products. Execute the following code to insert some products:
INSERT INTO Product
SELECT 'Wii'
UNION
SELECT 'PS3'
UNION
SELECT 'XBOX360'
如果您执行SELECT * FROM Product，您可能会看到以下内容:
If you execute SELECT * FROM Product you will probably see the following:
ProductID ProductDescription
1 PS3
2 Wii
3 XBOX360
好的，此时您拥有产品和商店信息.那么如何将它们组合在一起呢?我们知道我们可以通过 ShopID 主键列来识别商店，并且我们知道我们可以通过 ProductID 主键列来识别产品.此外，由于每个商店对每种产品的价格不同，我们需要存储商店对产品收取的价格.
OK, at this point you have both product and shop information. So how do you bring them together? Well we know we can identify the shop by its ShopID primary key column and we know we can identify a product by its ProductID primary key column. Also, since each shop has a different price for each product we need to store the price the shop charges for the product.
所以我们有一个表，将 Shop 映射到产品.我们将此表称为 ShopProduct.此表的简单版本可能如下所示:
So we have a table that maps the Shop to the product. We will call this table ShopProduct. A simple version of this table might look like this:
CREATE TABLE [dbo].[ShopProduct](
[ShopID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK_ShopProduct] PRIMARY KEY CLUSTERED
(
[ShopID] ASC,
[ProductID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
因此，假设 American Games R Us 商店仅销售美国游戏机，Europe Gaming Experience 销售所有游戏机，而 Asian Games Emporium 仅销售亚洲游戏机.我们需要将 shop 和 product 表中的主键映射到 ShopProduct 表中.
So let's assume the American Games R Us shop only sells American consoles, the Europe Gaming Experience sells all consoles and the Asian Games Emporium sells only Asian consoles. We would need to map the primary keys from the shop and product tables into the ShopProduct table.
这是我们将如何进行映射.在我的示例中，American Games R Us 的 ShopID 值为 1(这是主键值)，我可以看到 XBOX360 的值为 3，商店以 159.99 美元的价格列出了 XBOX360
Here is how we are going to do the mapping. In my example the American Games R Us has a ShopID value of 1 (this is the primary key value) and I can see that the XBOX360 has a value of 3 and the shop has listed the XBOX360 for $159.99
通过执行以下代码，您将完成映射:
By executing the following code you would complete the mapping:
INSERT INTO ShopProduct VALUES(1,3,159.99)
现在我们要将所有产品添加到欧洲游戏体验商店.在此示例中，我们知道 Europe Gaming Experience 商店的 ShopID 为 3，由于它销售所有游戏机，因此我们需要将 ProductID 1、2 和 3 插入映射表中.假设欧洲游戏体验店的游戏机(产品)价格如下:1- PS3 售价 259.99 美元，2- Wii 售价 159.99 美元，3- XBOX360 售价 199.99 美元.
Now we want to add all product to the Europe Gaming Experience shop. In this example we know that the Europe Gaming Experience shop has a ShopID of 3 and since it sells all consoles we will need to insert the ProductID 1, 2 and 3 into the mapping table. Let's assume the prices for the consoles (products) at the Europe Gaming Experience shop are as follows: 1- The PS3 sells for $259.99 , 2- The Wii sells for $159.99 , 3- The XBOX360 sells for $199.99.
要完成此映射，您需要执行以下代码:
To get this mapping done you would need to execute the following code:
INSERT INTO ShopProduct VALUES(3,2,159.99) --This will insert the WII console into the mapping table for the Europe Gaming Experience Shop with a price of 159.99
INSERT INTO ShopProduct VALUES(3,1,259.99) --This will insert the PS3 console into the mapping table for the Europe Gaming Experience Shop with a price of 259.99
INSERT INTO ShopProduct VALUES(3,3,199.99) --This will insert the XBOX360 console into the mapping table for the Europe Gaming Experience Shop with a price of 199.99
此时您已将两个商店及其产品映射到映射表中.好的，那么现在我如何将所有这些组合在一起以显示浏览网站的用户?假设您想在网页上向用户显示欧洲游戏体验的所有产品 - 您需要执行以下查询:
At this point you have mapped two shops and their products into the mapping table. OK, so now how do I bring this all together to show a user browsing the website? Let's say you want to show all the product for the European Gaming Experience to a user on a web page – you would need to execute the following query:
SELECT Shop.*
, ShopProduct.*
, Product.*
FROM Shop
INNER JOIN ShopProduct ON Shop.ShopID = ShopProduct.ShopID
INNER JOIN Product ON ShopProduct.ProductID = Product.ProductID
WHERE Shop.ShopID=3
您可能会看到以下结果:
You will probably see the following results:
ShopID ShopName ShopID ProductID Price ProductID ProductDescription
3 Europe Gaming Experience 3 1 259.99 1 PS3
3 Europe Gaming Experience 3 2 159.99 2 Wii
3 Europe Gaming Experience 3 3 199.99 3 XBOX360
现在举最后一个例子，让我们假设您的网站有一项功能可以找到最便宜的主机价格.用户要求查找 XBOX360 的最便宜价格.
Now for one last example, let's assume that your website has a feature which finds the cheapest price for a console. A user asks to find the cheapest prices for XBOX360.
您可以执行以下查询:
SELECT Shop.*
, ShopProduct.*
, Product.*
FROM Shop
INNER JOIN ShopProduct ON Shop.ShopID = ShopProduct.ShopID
INNER JOIN Product ON ShopProduct.ProductID = Product.ProductID
WHERE Product.ProductID =3 -- You can also use Product.ProductDescription = 'XBOX360'
ORDER BY Price ASC
此查询将返回所有销售 XBOX360 的商店的列表，其中最便宜的商店排在首位，依此类推.
This query will return a list of all shops which sells the XBOX360 with the cheapest shop first and so on.
你会注意到我没有添加亚运商店.作为练习，将亚运商店添加到具有以下产品的映射表中:亚运商场以 99.99 美元的价格出售 Wii 游戏机，以 159.99 美元的价格出售 PS3 游戏机.如果您完成了这个示例，您现在应该了解如何建模多对多关系.
You will notice that I have not added the Asian Games shop. As an exercise, add the Asian games shop to the mapping table with the following products: the Asian Games Emporium sells the Wii games console for $99.99 and the PS3 console for $159.99. If you work through this example you should now understand how to model a many-to-many relationship.
我希望这对您的数据库设计之旅有所帮助.
I hope this helps you in your travels with database design.
这篇关于T-SQL 如何在存储过程中动态创建表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
