Creating table fields dynamically

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 2000 Forums

SQL Server Development (2000)

Creating table fields dynamically

Author

Topic

getsaby
Starting Member

11 Posts

Posted - 2006-08-30 : 13:19:58

Hello all:

We all know, we can create table name dynamically.
Is it possible to creates fields of the table dynamically too.
Lets say you have one table.....EMP,
the first row is the header info,
i.e..empname, empage etc.....
From second row the actual record starts.

Is this can be done dynamically, to map those first row so that it becomes field for another table.

Am using MS SQL Server..

Replies will b appreciated,

Thanks,
saby

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-30 : 13:28:30

quote:
Originally posted by getsaby

Hello all:
We all know, we can create table name dynamically.
Is it possible to creates fields of the table dynamically too.
Lets say you have one table.....EMP,
the first row is the header info,
i.e..empname, empage etc.....
From second row the actual record starts.

Is this can be done dynamically, to map those first row so that it becomes field for another table.

Maybe its the 4 hours of sleep, but I have no idea what you are asking. Do you have an example of what you are trying to do? I am
not sure I understand first row is the header info, second row actual record starts.... are you counting the column headers as a row?

________________________________________________
I am a man, I can change, if I have to, I guess.

getsaby
Starting Member

11 Posts

Posted - 2006-08-30 : 14:39:32

Ok..

The description of table is as
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100) )

Table: Dummy
====================================================
field1 field2 field3
Empid Empname empaage
1 sam 23
2 rai 22
.
.
.
n raj 45
====================================================
Now I want to create another table as "EMP" as, with proper data type
fields too..

create table emp (
empid int, empname varchar(100), empage int)

Table: EMP
====================================================
Empid Empname empaage
1 sam 23
2 rai 22
.
.
.
n raj 45
====================================================

So this is what I want. But I want to do this dynamically.....
Some how I need to extract those field from table "dummy" and using them to create field for another table "EMP"...

I hope this help...some wht...

Thanks for the reponse..

saby

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-30 : 14:55:31

interesting data model.

hmm. first thing to do is get the columns and datatypes
from sysobjects, syscolumns, systypes so you have the type info to create the table. (and get the column order field) (colorder... i think)

Then, I would figure out how many fields you have and do a while loop of the first record to get the real column names, replacing them with the names from the sys table lookups (but not replacing the varchar(20) info.

Then do a create table.

Build a string of pull from old table where record > first and insert into new table.

kind of twisted, but possible

________________________________________________
I am a man, I can change, if I have to, I guess.

getsaby
Starting Member

11 Posts

Posted - 2006-08-30 : 16:53:19

Hi:

Thanks for ur quick response:

If you can give mi rough layout of the code, that would help mi a lot..

Thanks,
saby

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources