I have compiled a list of columns which I hope to build a table with. Is it possible to use a procedure to create a table based on this list? Ie
List
A
B
C
New_Table
Column A Column B Column C
Asked
Active
Viewed 639 times
0
Dave Jemison
- 694
- 3
- 7
Calgar99
- 1,670
- 5
- 26
- 42
-
Perhaps `SELECT INTO` might help. Read up on that. – shree.pat18 May 12 '14 at 16:46
-
Which DBMS are you using? Postgres? Oracle? – May 12 '14 at 16:56
-
Im using Microsoft SQL server – Calgar99 May 12 '14 at 17:14
-
Why do you need a procedure? Why not just generate a `CREATE TABLE` statement? – nobody May 12 '14 at 17:51
-
The list will be updated meaning the table will need to be updated. I'm attempting to automate the process – Calgar99 May 12 '14 at 19:27
-
Maybe a duplicate of http://stackoverflow.com/questions/10877407/t-sql-how-to-create-tables-dynamically-in-stored-procedures – timo.rieber May 12 '14 at 19:28
1 Answers
0
Customer is atable consist of column List with rows:- A, B, C
What I've done is added a RowNum column on run time into the customer table which is dynamic. Due to this another column of row number got attached with List column. Using this:-
- RowNum=1 provides A
- RowNum=2 provides B
- RowNum=3 provides C
Here is the procedure as per your requirement. Please verify:-
create procedure newtable
@tablename NVARCHAR(1000)
as
Declare @col1 varchar(50),@col2 varchar(50),@col3 varchar(50), @cmd NVARCHAR(1000)
select @col1=List from (select List,ROW_NUMBER() Over(Order by List) as RowNum from customer)x where x.RowNum=1
select @col2=List from (select List,ROW_NUMBER() Over(Order by List) as RowNum from customer)x where x.RowNum=2
select @col3=List from (select List,ROW_NUMBER() Over(Order by List) as RowNum from customer)x where x.RowNum=3
set @cmd=N'Create Table '+@tablename+ N' (' + @col1+N' Varchar(50),'+@col2 + N' varchar(50),'+@col3+N' varchar(50))' ;
print @cmd
Exec(@cmd)
After this run your command:-
exec newtable 'New_table'
