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.
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 First3 LastCreate 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.htmlMadhivananFailing to plan is Planning to fail |
|
|
zubamark
Starting Member
23 Posts |
Posted - 2008-04-15 : 14:07:06
|
I solve the problem. Check it outDECLARE @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 IDOPEN Field FETCH NEXT FROM Field INTO @FieldWHILE @@FETCH_STATUS = 0BEGIN SELECT @SQL= @SQL + @Field FETCH NEXT FROM Field INTO @FieldEND SELECT @SQL = @SQL +' )' CLOSE FieldDEALLOCATE FieldEXECUTE (@SQL)select * from N2drop table N2 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-15 : 16:48:05
|
orDECLARE @SQL varchar (8000) select @sql = coalesce(@sql+',','') + '[' + netid + '] varchar(50)'from N1select @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. |
|
|
|
|
|