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

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.

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?

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..?

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

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources