Run time Table creation - 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)

Run time Table creation

Author

Topic

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-07-16 : 07:53:54

I want to create runtime table creation along with the runtime columns number ,names and datatype creation.
Please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 07:59:10

do you mean creating structure from query? if yes, use SELECT INTO

SELECT fields INTO NewTable FROM Oldtable1 JOIN...

if not, you need dynamic sql.

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-07-16 : 08:29:59

I have a mastertable that has the value lik
Column1 Column2 column3
1 aus/nz/Uk/other COUNTRY
2 Hindi/English/Polish / LANG

Through some logic , I want my new table creation (run time) with name
Extracted from Column3 and its columns from Column 2 aus(bit), nz(bit),UK(bit),other(varchar(50).

My result set should be

Create COUNTRY
(
AUS bit
NZ bit
UK bit
Other varchar(50)
)

This is what I want.I hope you understood Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 09:38:35

quote:
Originally posted by pradeep_iete

I have a mastertable that has the value lik
Column1 Column2 column3
1 aus/nz/Uk/other COUNTRY
2 Hindi/English/Polish / LANG

Through some logic , I want my new table creation (run time) with name
Extracted from Column3 and its columns from Column 2 aus(bit), nz(bit),UK(bit),other(varchar(50).

My result set should be

Create COUNTRY
(
AUS bit
NZ bit
UK bit
Other varchar(50)
)

This is what I want.I hope you understood Visakh

For doing this i think you need dynamic sql where you extract the COLUMN 3 and COLUMN 2 values to variables and create a table with them.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-16 : 10:11:12

and why do you want dynamic column names at the first place? it can be done with dynamic SQL but I'm curious what approach are you taking?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-16 : 11:09:22

It appears that The methodolgy that should be used

is

Create Table Country(CountryID int, CountryName varchar(5),Checked bit, Other Varchar(50))
Insert Into Country(CountryID,CountryName,Checked)
Select 1,'AUS',1 Union All
Select 2,'NZ',0 Union All
Select 3,'UK',1

Once you have that table you can use it in a cross tab, plus it's far more scalable then the other design. Please explain more of what you are looking for to ensure you are using the best approach.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 11:40:20

quote:
Originally posted by Vinnie881

It appears that The methodolgy that should be used

is

Create Table Country(CountryID int, CountryName varchar(5),Checked bit, Other Varchar(50))
Insert Into Country(CountryID,CountryName,Checked)
Select 1,'AUS',1 Union All
Select 2,'NZ',0 Union All
Select 3,'UK',1

Once you have that table you can use it in a cross tab, plus it's far more scalable then the other design. Please explain more of what you are looking for to ensure you are using the best approach.

still OP needs dynamic sql

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-16 : 11:52:03

visakh16,

My post might not have been clear. I was wrote it with the assumption that there was no real need to create the dynamic tables, so I was suggesting to use a different methodology. I feel that the question was asked the way it was by pradeep_iete because he was assuming it was the only/best way to resolve his issue. That is why I asked for him to explain more.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 12:06:17

quote:
Originally posted by Vinnie881

visakh16,

My post might not have been clear. I was wrote it with the assumption that there was no real need to create the dynamic tables, so I was suggesting to use a different methodology. I feel that the question was asked the way it was by pradeep_iete because he was assuming it was the only/best way to resolve his issue. That is why I asked for him to explain more.

ok. i was just pointing that if OP wants to name the table based on field value then he need dynamic sql.

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-07-17 : 08:27:57

I m not Still gettin the Logic.
Keypoints are
Create table name at run time
Create Columns numbers at run Time
Create Column datatypes at run time.

Suggest me Dynamic SQL Logic.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-17 : 09:57:14

quote:
Originally posted by pradeep_iete

I m not Still gettin the Logic.
Keypoints are
Create table name at run time
Create Columns numbers at run Time
Create Column datatypes at run time.

Suggest me Dynamic SQL Logic.

read about dynamic SQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 09:58:39

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources