Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Posts
    1

    Unanswered: creating table fields dynamically from another table row

    Hello all:

    Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.

    The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.

    create dummy emp (
    field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )

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

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

    The table should look like as:

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

    I want to do this dynamically.....
    Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]

    A small rough snippet of the code will be appreciated....

    Waiting for replies........
    saby

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    you can do something like below. but determining the exact filed type in CREATE table is difficult and avoiding that I have taken all fields as varchar(100)

    Code:
    create table #T (SQLTxt varchar(5000))
    
    declare @SQL varchar(5000)
    declare @FinalSQL varchar(5000)
    declare @ii int
    
    set @SQL=''
    set @ii=1
    while @ii <= 140
    begin 
    	set @SQL = @SQL + ' Field' + cast(@ii as varchar) + ' + '' varchar(100) null,''+'
    	set @ii=@ii+1
    end
    set @SQL = left(@SQL,len(@SQL)-3)+''''
    set @SQL = 'insert into #T (SQLTxt) select top 1 ' + '''create table Dummy2 (''+' + @SQL + '+'')''' + ' from Dummy'
    
    exec (@SQL)
    set @FinalSQL = (select top 1 SQLTxt from #T)
    exec (@FinalSQL)
    drop table #T

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •