use @Parameter value for create table name

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)

use @Parameter value for create table name

Author

Topic

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-09-14 : 05:06:12

I would like to create a few tables on a loop and would like to use a parameter to create the different table names.

Can this be done and if so how?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 05:16:47

quote:
Originally posted by jtwork

I would like to create a few tables on a loop and would like to use a parameter to create the different table names.

Can this be done and if so how?

Denormalised structure?
Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-14 : 05:30:07

jtwork, yes it can be done

check out while or if you like actual cursor, then dynamic sql for the query to be executed... BOL has a 'template' for cursor

or

select 'create table ' + tablename +
'your fields and definitions here '
from tablesourcename

this will give you the create statements

-cheers-

--------------------
keeping it simple...

swathigardas
Posting Yak Master

149 Posts

Posted - 2007-09-14 : 05:39:24

hi jen
even i had similar kind of problem

but this query doesnt work.
it shows invalid column name where i gave table name according to the syntax you gave

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 05:41:35

quote:
Originally posted by swathigardas

hi jen
even i had similar kind of problem

but this query doesnt work.
it shows invalid column name where i gave table name according to the syntax you gave

As I told, You should read fully the article I posted

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-14 : 06:33:18

well the tablesourcename is a table where all your tablenames are stored...

the query is just something you should try to work out

look up create table in BOL

--------------------
keeping it simple...

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-09-14 : 09:01:34

thanks for your help so far.

Here is my code

SELECT @Date_From as Date_From, @Date_To as Date_To, #Temp_Top_10.agent_login, #Temp_Top_10.fname, #Temp_Top_10.lname, #Temp_Top_10.latest_team, #Temp_Top_10.latest_area, #Temp_Top_10.rpc_count, #Temp_Top_10.hellos, #Temp_Top_10.percentage,
@Week2_From as Week2, isnull(#Temp_Week2.rpc_count,0) as Wk2RPC, isnull(#Temp_Week2.hellos,0) as Wk2Hellos,
@Week3_From as Week3, isnull(#Temp_Week3.rpc_count,0) as Wk3RPC, isnull(#Temp_Week3.hellos,0) as Wk3Hellos,
@Week4_From as Week4, isnull(#Temp_Week4.rpc_count,0) as Wk4RPC, isnull(#Temp_Week4.hellos,0) as Wk4Hellos
INTO database.dbo.Report_Top_10
FROM ((#Temp_Top_10 left join #temp_Week2 ON (#Temp_Top_10.agent_login = #Temp_Week2.agent_login) and (#Temp_Top_10.app_id = #Temp_Week2.app_id)) Left Join #Temp_Week3 on (#Temp_Top_10.agent_login = #Temp_Week3.agent_login) and (#Temp_Top_10.app_id = #Temp_Week3.app_id)) Left Join #Temp_Week4 on (#Temp_Top_10.agent_login = #Temp_Week4.agent_login) and (#Temp_Top_10.app_id = #Temp_Week4.app_id)
ORDER BY #Temp_Top_10.percentage Desc

I want to change the
INTO database.dbo.Report_Top_10

so that i can use my parameter @Temp_Name to say

INTO database.dbo.@Temp_Name

Ive had a look at your suggestions but cant work it out?

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-09-14 : 09:07:22

actually i wat it to be an insert as i plan to reuse the tables and just delete them first and then insert into existing tables but i can sort that out afterwards

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-14 : 10:55:59

declare @cmd nvarchar(4000)
declare @tablename nvarchar(200)

set @cmd='select *
into ' + @tablename + '
from anOldTableWithSameSchema
where 1=0'

exec (@cmd)

that will create a table with the same schema as the anOldTableWithSameSchema, no rows... then just do an insert perhaps afterwards

--------------------
keeping it simple...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-09-14 : 12:03:55

generally it's quite dangerous to generate sql on the fly and then execute it blindly. you could end up with data loss or worse.

it's much safer to call print(@cmd) and then inspect the output for correctness. If it looks ok, paste it into a query window and execute it.

elsasoft.org

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-14 : 12:41:54

generally that is true if you don't test or understand the dsql
and if it involves updates and deletes

--------------------
keeping it simple...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-09-14 : 23:32:01

I was thinking more because this dynamic sql is generating DDL. It's modifying the schema and therefore has greater potential for harm, imo. blindly creating tables is not a good idea.

elsasoft.org

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-09-17 : 04:47:33

This is great thank guys. I have one problem though. Because i am using a parameter to create a column

like so @date_from as Date_From

This is getting rejected with an error, must declare @From_Date (this is already declared and works if i dont try and use the @table_name parameter to name my table.

I then tried placing the parameter @from_date outside of the sql string make up like so

set @CMD='INSERT INTO ' + @Table_Name + ' SELECT ' + @Date_From + ' as Date_From

but this also returns an error
Syntax error converting character string to smalldatetime data type.

Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 05:05:04

smalldatetime column should be converted to Varchar

Madhivanan

Failing to plan is Planning to fail

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-09-17 : 06:13:22

ive tried converting like so

set @CMD='INSERT INTO ' + @Table_Name + ' SELECT ' + convert(varchar(10), @Date_From) + ' as Date_From,

but i get the following error
Syntax error converting character string to smalldatetime data type.

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-09-25 : 05:50:25

ive got this working but have come across a problem. I am unable to create a tempoary table using this method only a physical table??

Anyone know why and possibly a work around?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 07:31:50

<<
Anyone know why and possibly a work around?
>>

Read the article I posted

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 08:42:28

jtwork -- there are so many things wrong with what you are doing that it will take hours to sort this all out. Why are you doing this? Why are you trying to build tables dynamically? Can you step back a little and explain? Does each of these dynamically created tables have the same structure (i.e., column names and data types) ?

I promise there is a better way to do what you want rather than stringing together long, complicated dynamic sql strings and creating random tables in your database ....

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources