Dyamically creating the table in sql server

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

General SQL Server Forums

New to SQL Server Programming

Dyamically creating the table in sql server

Author

Topic

sqlservernovice
Starting Member

9 Posts

Posted - 2012-04-12 : 16:36:36

all,

how can i create a table by name like during runtime. For example, I want to be able to create a table with the name like

schemaname.abcdc_getdate()_stage1

eg schemaname.abcdc_12_04_12_Stage1

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-12 : 16:55:11

You would need to use dynamic SQL to do this, however this is not recommended. Why do you need dynamic names?

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

Subscribe to my blog

sqlservernovice
Starting Member

9 Posts

Posted - 2012-04-12 : 17:06:26

i need to create the stage tables so that i can use it in the other process, and drop stage tables at the end of the process. i dont wanna use the temp table as they are huge number of rows to process. what do you suggest.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-12 : 17:12:16

I'm asking why the name needs to be dynamic. Dynamic names are going to require dynamic SQL to create them and then dynamic SQL to query them. You should avoid this.

Use static names.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 17:33:24

you need to use dynamic sql for that

EXEC 'CREATE TABLE schemaname.abcdc_'+ REPLACE(CONVERT(varchar(11),GETDATE(),103),'/','_') + '_Stage1 (.....'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 17:34:41

quote:
Originally posted by sqlservernovice

i need to create the stage tables so that i can use it in the other process, and drop stage tables at the end of the process. i dont wanna use the temp table as they are huge number of rows to process. what do you suggest.

why do you need dates to be in table name?
are you creating stage tables for each day seperate? why not add them to same table with a date field to indicate date they belong?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlservernovice
Starting Member

9 Posts

Posted - 2012-04-12 : 17:55:43

i am trying to automate some of the data loading process that is to happen everyday day, and requiremtn is that the stage tables need to be created dyanamically and use the tables to load the data into the target tables and then drop the stage tables after the load is complete. So this is the approach i was wondering to take.

any other suggestions, would be great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 18:01:25

using stage tables is fine. but question why you need to name them using dates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-12 : 18:50:10

quote:
Originally posted by sqlservernovice

i am trying to automate some of the data loading process that is to happen everyday day, and requiremtn is that the stage tables need to be created dyanamically and use the tables to load the data into the target tables and then drop the stage tables after the load is complete. So this is the approach i was wondering to take.

any other suggestions, would be great.

Your approach as described is fine, just use static table names to avoid the dynamic SQL that will be required.

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

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 05:01:42

Use a static staging table and truncate it after the data load is over

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:02:08

Just to reiterate, you can have a column to store date inside temporary table to indicate time period of data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources