T-SQL How to create tables dynamically in stored procedures

Question

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

User · Answer

You will need to build that CREATE TABLE statement from the inputs and then execute it.

A simple example:

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);

User · Answer

First up  you seem to be mixing table variables and tables   Either way  You can t pass in the table s name like that  You would have to use dynamic TSQL to do that   If you just want to declare a table variable   CREATE PROC sp createATable     name        VARCHAR 10       properties  VARCHAR 500   AS    declare  tablename TABLE          id  CHAR 10   PRIMARY KEY          The fact that you want to create a stored procedure to dynamically create tables might suggest your design is wrong

User · Answer

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    UPDATE   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   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   UPDATE 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    It s an e-commerce site that has many shops A shop can have many items  goods  to sell  A particular item  good  can be sold at many shops A shop will charge different prices for different items  goods  All prices are in    USD    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    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   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    If you execute SELECT   FROM Product you will probably see the following   ProductID   ProductDescription 1           PS3 2           Wii 3           XBOX360   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   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   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   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    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   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   You can 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      Product ProductID  3  -- You can also use Product ProductDescription    XBOX360   ORDER BY    Price ASC   This query will return a list of all shops which sells the XBOX360 with the cheapest shop first and so on    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

User · Answer

This is a way to create tables dynamically using T-SQL  stored procedures   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         Execute it as   exec  cmd

User · Answer

You can write the below code -  create procedure spCreateTable    as     begin        create table testtb Name varchar 20       end   execute it as -  exec spCreateTable

[sql] T-SQL How to create tables dynamically in stored procedures?

Examples related to sql

Examples related to sql-server

Examples related to tsql