create table with dynamic sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

create table with dynamic sql

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-12-30 : 04:21:20

I have a stored procure where i have
@id

I then want to create tables so if @id=1 it will do

create table 1_admin
( etc....

and I want to do this for a number of tables

what would be the syntax and can this be done?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-30 : 06:34:16

if @id=1
create table 1_admin
( etc....

else if @id=2
create table 2_admin
( etc....
.
.
.
.

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-12-30 : 10:46:06

but I have a storedprocedure that returns an identity and I want to create the tables with the number of the id returned by the identity field

is this possible?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-30 : 11:53:18

declare @sql nvarchar(max)

set @sql = 'create table ' + @id + '_admin ( etc...'

exec sp_ExecuteSQL @sql

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-31 : 04:24:32

Make sure to read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-01 : 14:53:56

would it be better for me to use dynamic sql or to just pass each create sql from my asp application?

(i thought sql is better as I can do it all at once and I need to create 10 tables but if dynamic sql is an issue..

what do you experts say?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-01-01 : 20:45:00

Not sure what the experts say, but I have to ask why you want to create multiple tables with the exact same structure. That seems to me to be an issue with your design and you should not do this.

Should be easy enough to just create the ten tables and use another column that ties to your ID value from the stored procedure. But, without seeing the rest of your design that is just a guess.

Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-01 : 21:22:06

Dynamic SQL is the way to go if you need to dynamically create objects. However, I agree with Jeff's post completely. You've got a design issue. You should not be creating tables like this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-02 : 01:26:59

i'm setting up different tables for different stores and I want each one to have it's own table so that ID fields for the table are per store

is there a better way to do this?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-02 : 01:27:33

for tables that the ID does not matter I can make it a query that is created dynamically - is that better?

Kristen
Test

22859 Posts

Posted - 2011-01-02 : 04:38:03

"is there a better way to do this?"

We have a Column for the ID/Attribute of the Store. Otherwise all your queries are going to have to be dynamic too (in order to determine which Table Name to use)

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-02 : 04:53:25

what do you mean by that

my problem is I want orders to go in order by number per store and not for all stores together so I created it as a separate table
is there a way around that?

Kristen
Test

22859 Posts

Posted - 2011-01-02 : 06:15:31

So ... you order table could have columns

Shop_ID
Order_ID

You can make the PK be (Shop_ID, Order_ID) - or, in addition to those two columns, you could have a third ID column using IDENTITY attribute so that you have a simple single-column PK (e.g. for use as Foreign Key to Order_Items table and so on).

Note that if you have a table with just Order_ID as the PK (e.g. in your example of having one Order table for EACH Shop), and IF that is using an IDENTITY attribute (to automatically assign the "next" number) then there WILL be gaps in the number as transactions are rolled back etc. So ... if you want strictly contiguous ascending numbers you should not use IDENTITY. (Most people don't care if there are a few gags though).

In your case you do NOT want large / frequent gaps in the Order_ID where numbers have been allocated to "other" shops. I would say that is "normal" too

Managers like to look at the Order ID and "know" that they have made 1,000 orders since last week - by just subtracting last week's Order_ID in their head ...

So instead of using IDENTITY you could have an order INTEGER datatype for the Order_ID column and when you create a new order you just do:


INSERT INTO MyOrders(Shop_ID, Order_ID, Customer_ID, OrderDate, ...)
SELECT [Shop_ID] = @Shop_ID,
       [Order_ID] = COALESCE(SELECT MAX(Order_ID) FROM MyOrders WHERE Shop_ID = @Shop_ID)+1, 12345), -- 12345 is the "initial" start number
       [Customer_ID] = @Customer_ID,
       [OrderDate] = GetDate(),
       ...

to get the "next" Order_ID to use for THAT shop.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources