SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 create table dynamically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zubamark
Starting Member

23 Posts

Posted - 04/14/2008 :  14:38:49  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/14/2008 :  14:44:36  Show Profile  Visit nr's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 04/14/2008 :  14:58:19  Show Profile  Reply with Quote
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

India
52 Posts

Posted - 04/15/2008 :  02:46:19  Show Profile  Reply with Quote
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

India
22754 Posts

Posted - 04/15/2008 :  03:19:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 04/15/2008 :  14:07:06  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/15/2008 :  16:48:05  Show Profile  Visit nr's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000