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
 create table dynamically

Author  Topic 

zubamark
Starting Member

23 Posts

Posted - 2008-04-14 : 14:38:49
Hi,

I have N1 table where columns name(id,Field). Base on the fields of this table I want to create N2 table from SP where data from N1 will be columns in N2.
id Field
-- ------
1 ID
2 First
3 Last

Create table N2(ID,First,Last)

regards,
Mark

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 14:44:36
How do you get the datatypes of the columns?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 14:58:19
You need to use dynamic sql for this. Why do you want to do it this way?
Go to Top of Page

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-04-15 : 02:46:19
Is the tabel N1 is dynamically growing?
in such condition what should be datatypes of these new columns..?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-15 : 03:19:14
In any case refer www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

zubamark
Starting Member

23 Posts

Posted - 2008-04-15 : 14:07:06
I solve the problem. Check it out
DECLARE @SQL varchar (8000)
DECLARE @Field varchar (8000)
set @SQL='Create table N2('
DECLARE Field CURSOR FOR select '['+netid+'] varchar(50)'+
--Insert coma after each record and remove from the last one--
(case when netid=(select distinct Top 1 netid from N1 order by ID desc)
then '' else ',' end) from N1 order by ID
OPEN Field
FETCH NEXT FROM Field INTO @Field
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL= @SQL + @Field
FETCH NEXT FROM Field INTO @Field
END
SELECT @SQL = @SQL +' )'
CLOSE Field
DEALLOCATE Field

EXECUTE (@SQL)
select * from N2
drop table N2
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-15 : 16:48:05
or
DECLARE @SQL varchar (8000)
select @sql = coalesce(@sql+',','') + '[' + netid + '] varchar(50)'
from N1
select @sql = 'create table N2 (' + @sql + ')'
exec (@sql)

Only any good if you want all varchar(50) columns which would be very unusual.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -