T-SQL Как динамически создавать таблицы в хранимых процедурах?

Ответ 1

Вы используете переменную таблицы, т.е. вы должны объявить таблицу. Это не временная таблица.

Вы создаете временную таблицу следующим образом:

CREATE TABLE #customer
(
     Name varchar(32) not null
)

Вы объявляете переменную таблицы следующим образом:

DECLARE @Customer TABLE
(
      Name varchar(32) not null
)

Обратите внимание, что временная таблица объявляется с помощью #, а переменная таблицы объявляется с помощью @. Читайте о разнице между табличными переменными и временными таблицами.

ОБНОВИТЬ:

Исходя из вашего комментария ниже, вы на самом деле пытаетесь создать таблицы в хранимой процедуре. Для этого вам нужно будет использовать динамический SQL. В основном динамический SQL позволяет вам создавать оператор SQL в форме строки и затем выполнять его. Это ЕДИНСТВЕННЫЙ способ создания таблицы в хранимой процедуре. Я собираюсь показать вам, как, а затем обсудить, почему это не очень хорошая идея.

Теперь простой пример (я не тестировал этот код, но он должен дать вам хорошее представление о том, как это сделать):

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 '[email protected] + '( '[email protected]+' '[email protected] +' '[email protected] +') ON PRIMARY '

   EXEC (@SQLString)
   GO

Эта хранимая процедура может быть выполнена следующим образом:

sproc_BuildTable 'Customers','CustomerName','VARCHAR(32)','NOT NULL'

Есть некоторые серьезные проблемы с этим типом хранимой процедуры.

Это будет трудно обслуживать сложные столы. Представьте себе следующую структуру таблицы:

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 

Эта таблица немного сложнее, чем в первом примере, но не намного. Хранимая процедура будет намного сложнее иметь дело. Таким образом, хотя этот подход может работать для небольших таблиц, он быстро станет неуправляемым.

Создание таблиц требует планирования. Когда вы создаете таблицы, они должны быть стратегически размещены в разных файловых группах. Это должно гарантировать, что вы не вызовете конфликт ввода-вывода диска. Как вы будете решать проблему масштабируемости, если все создано в основной группе файлов?

Не могли бы вы уточнить, почему вам нужно динамически создавать таблицы?

ОБНОВЛЕНИЕ 2:

Задержка обновления из-за загруженности. Я прочитал ваш комментарий о необходимости создания таблицы для каждого магазина, и я думаю, что вы должны смотреть на это как пример, который я собираюсь дать вам.

В этом примере я делаю следующие предположения:

1. Это сайт электронной коммерции, который имеет много магазинов
2. В магазине может быть много предметов (товаров) для продажи.
3. Отдельный товар (товар) можно продать во многих магазинах
4. Магазин будет взимать разные цены за разные предметы (товары)
5. Все цены указаны в долларах США

Допустим, на этом сайте электронной коммерции продаются игровые приставки (например, Wii, PS3, XBOX360).

Глядя на мои предположения, я вижу классические отношения "многие ко многим". Магазин может продавать много предметов (товаров), а предметы (товары) могут продаваться во многих магазинах. Позвольте разбить это на таблицы.

Для начала мне понадобится стол для магазина, чтобы хранить всю информацию о магазине.

Простой магазинный стол может выглядеть так:

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

Давайте добавим три магазина в базу данных для использования в нашем примере. Следующий код вставит три магазина:

INSERT INTO Shop
SELECT 'American Games R US'
UNION
SELECT 'Europe Gaming Experience'
UNION
SELECT 'Asian Games Emporium'

Если вы выполните SELECT * FROM Shop вы, вероятно, увидите следующее:

ShopID  ShopName
1           American Games R US
2           Asian Games Emporium
3           Europe Gaming Experience

Итак, теперь давайте перейдем к таблице товаров (товаров). Поскольку товары/товары являются продуктами разных компаний, я буду называть стол продуктом. Вы можете выполнить следующий код, чтобы создать простую таблицу Product.

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

Позвольте заполнить таблицу продуктов некоторыми продуктами. Выполните следующий код, чтобы вставить некоторые продукты:

INSERT INTO Product
SELECT 'Wii'
UNION 
SELECT 'PS3'
UNION 
SELECT 'XBOX360'

Если вы выполните SELECT * FROM Product вы, вероятно, увидите следующее:

ProductID   ProductDescription
1           PS3
2           Wii
3           XBOX360

Хорошо, на данный момент у вас есть информация о продукте и магазине. Итак, как вы сводите их вместе? Мы знаем, что можем идентифицировать магазин по столбцу первичного ключа ShopID, и мы знаем, что можем идентифицировать продукт по столбцу первичного ключа ProductID. Кроме того, поскольку каждый магазин имеет свою цену для каждого продукта, нам нужно хранить цену, которую магазин взимает за этот продукт.

Итак, у нас есть таблица, которая отображает магазин на продукт. Мы назовем этот стол ShopProduct. Простая версия этой таблицы может выглядеть так:

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 продает все приставки, а Азиатские Игры Emporium продает только азиатские приставки. Нам нужно сопоставить первичные ключи из таблиц shop и product в таблицу ShopProduct.

Вот как мы собираемся сделать отображение. В моем примере American Games R Us имеет значение ShopID 1 (это значение первичного ключа), и я вижу, что XBOX360 имеет значение 3, а магазин выставил XBOX360 за $ 159,99.

Выполнив следующий код, вы завершите отображение:

INSERT INTO ShopProduct VALUES(1,3,159.99)

Теперь мы хотим добавить весь продукт в магазин Europe Gaming Experience. В этом примере мы знаем, что магазин Europe Gaming Experience имеет ShopID, равный 3, и, поскольку он продает все консоли, нам нужно будет вставить ProductID 1, 2 и 3 в таблицу сопоставления. Предположим, что цены на консоли (продукты) в магазине Europe Gaming Experience следующие: 1- PS3 продается за 259,99 долларов, 2- Wii продается за 159,99 долларов, 3- XBOX360 продается за 199,99 долларов.

Чтобы сделать это отображение, вам нужно выполнить следующий код:

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

На данный момент вы сопоставили два магазина и их продукты в таблице сопоставления. Хорошо, теперь как мне собрать все это вместе, чтобы показать пользователю, просматривающему веб-сайт? Допустим, вы хотите показать весь продукт для европейского игрового процесса пользователю на веб-странице - вам нужно выполнить следующий запрос:

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

Вы, вероятно, увидите следующие результаты:

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.

Вы можете выполнить следующий запрос:

 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 с самым дешевым магазином, и так далее.

Вы заметите, что я не добавил магазин Азиатских игр. В качестве упражнения добавьте магазин азиатских игр в таблицу сопоставления со следующими продуктами: Asian Games Emporium продает игровую приставку Wii за 99,99 долларов, а консоль PS3 - за 159,99 долларов. Если вы работаете с этим примером, то теперь вы должны понимать, как моделировать отношения "многие ко многим".

Я надеюсь, что это поможет вам в ваших путешествиях с дизайном базы данных.

Ответ 2

Вам нужно будет создать инструкцию CREATE TABLE из входов, а затем выполнить ее.

Простой пример:

declare @cmd nvarchar(1000), @TableName nvarchar(100);

set @TableName = 'NewTable';

set @cmd = 'CREATE TABLE dbo.' + quotename(@TableName, '[') + '(newCol int not null);';

print @cmd;

--exec(@cmd);

Ответ 3

Во-первых, вы, кажется, смешиваете переменные таблицы и таблицы.

В любом случае вы не можете передать имя таблицы так. Для этого вам придется использовать динамический TSQL.

Если вы просто хотите объявить переменную таблицы:

CREATE PROC sp_createATable 
  @name        VARCHAR(10), 
  @properties  VARCHAR(500) 
AS 
  declare @tablename TABLE
  ( 
    id  CHAR(10)  PRIMARY KEY 
  ); 

Тот факт, что вы хотите создать хранимую процедуру для динамического создания таблиц, может свидетельствовать о неправильном дизайне.

Ответ 4

Это способ динамического создания таблиц с помощью хранимых процедур T-SQL:

declare @cmd nvarchar(1000), @MyTableName nvarchar(100);
set @MyTableName = 'CustomerDetails';
set @cmd = 'CREATE TABLE dbo.' + quotename(@MyTableName, '[') + '(ColumnName1 int not null,ColumnName2 int not null);';

Выполните его как:

exec(@cmd);

Ответ 5

Вы можете написать приведенный ниже код:

create procedure spCreateTable
   as
    begin
       create table testtb(Name varchar(20))
    end

выполните его как: -

exec spCreateTable