Creating a 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

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a table dynamically

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-10 : 12:21:15

Greetings all,

I want to be able to generate a table on the fly. I know I can use dynamic sql to do this but my brains have forzen this afternoon so any help would be much appreciated.

I have the first query that returns one column with three rows. The contents of the these three rows will form the new columns in the dynamically generated table. How can I do this?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 12:23:01

Without seeing sample data its very difficult to provide a solution. Can you provide some sample data with output you would like to see?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-10 : 13:02:04

quote:
Originally posted by visakh16

Without seeing sample data its very difficult to provide a solution. Can you provide some sample data with output you would like to see?

Hi Vikash,

What I want to know is if you have a table like :


create table #test(ColumnName varchar(20))
insert into #test select 'Column1' union all select 'Column2' union all select 'Column3'

How can I create a table that has columns names, say Column1 and Column3?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:14:25

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

Without seeing sample data its very difficult to provide a solution. Can you provide some sample data with output you would like to see?

Hi Vikash,

What I want to know is if you have a table like :

create table #test(ColumnName varchar(20))
insert into #test select 'Column1' union all select 'Column2' union all select 'Column3'

How can I create a table that has columns names, say Column1 and Column3?

Thanks.

Try like this:-

create table #test(ColumnName varchar(20),DataType varchar(100),Properties varchar(1000))

insert into #test 
select 'Column1','varchar(50)','DEFAULT ''default value''' 
union all 
select 'Column2','int','IDENTITY(1,1) PRIMARY KEY CLUSTERED' 
union all 
select 'Column3','datetime'

DECLARE @Sql varchar(8000),@SQLColList varchar(8000)

SELECT @SQLColList=LEFT(cl.collist,LEN(collist)-1)
FROM
(SELECT ColumnName+ ' ' + DataType + ' '+Properties+ ',' AS [text()]
FROM #Test
FOR XML PATH())cl(collist)

SET @Sql='CREATE TABLE TableName('+@SQLColList + ')'

EXEC(@Sql)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-10 : 14:31:47

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-11 : 04:13:45

quote:
Originally posted by madhivanan

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan,

Trust me I know all about this but this is the way our system is designed! And it is th eonly way to do it as we have different staging tables depending on the client data we are loading.

Thanks forthe article anyway.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-11 : 04:21:49

quote:
Originally posted by visakh16

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

Without seeing sample data its very difficult to provide a solution. Can you provide some sample data with output you would like to see?

Hi Vikash,

What I want to know is if you have a table like :

create table #test(ColumnName varchar(20))
insert into #test select 'Column1' union all select 'Column2' union all select 'Column3'

How can I create a table that has columns names, say Column1 and Column3?

Thanks.

Try like this:-

create table #test(ColumnName varchar(20),DataType varchar(100),Properties varchar(1000))

insert into #test
select 'Column1','varchar(50)','DEFAULT ''default value'''
union all
select 'Column2','int','IDENTITY(1,1) PRIMARY KEY CLUSTERED'
union all
select 'Column3','datetime'

DECLARE @Sql varchar(8000),@SQLColList varchar(8000)

SELECT @SQLColList=LEFT(cl.collist,LEN(collist)-1)
FROM
(SELECT ColumnName+ ' ' + DataType + ' '+Properties+ ',' AS [text()]
FROM #Test
FOR XML PATH())cl(collist)

SET @Sql='CREATE TABLE TableName('+@SQLColList + ')'

EXEC(@Sql)

I get an error when I run this. It says:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

Something isn't right in:


FOR XML PATH())cl(collist)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 04:34:55

Yup missed a ''. modify like this

create table #test(ColumnName varchar(20),DataType varchar(100),Properties varchar(1000))

insert into #test 
select 'Column1','varchar(50)','DEFAULT ''default value''' 
union all 
select 'Column2','int','IDENTITY(1,1) PRIMARY KEY CLUSTERED' 
union all 
select 'Column3','datetime',''

DECLARE @Sql varchar(8000),@SQLColList varchar(8000)

SELECT @SQLColList=LEFT(cl.collist,LEN(collist)-1)
FROM
(SELECT ColumnName+ ' ' + DataType + ' '+Properties+ ',' AS [text()]
FROM #Test
FOR XML PATH(''))cl(collist)

SET @Sql='CREATE TABLE TableName('+@SQLColList + ')'

EXEC(@Sql)

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-11 : 05:15:52

quote:

Originally posted by visakh16

Yup missed a ''. modify like this

create table #test(ColumnName varchar(20),DataType varchar(100),Properties varchar(1000))

insert into #test 
select 'Column1','varchar(50)','DEFAULT ''default value''' 
union all 
select 'Column2','int','IDENTITY(1,1) PRIMARY KEY CLUSTERED' 
union all 
select 'Column3','datetime',''

DECLARE @Sql varchar(8000),@SQLColList varchar(8000)

SELECT @SQLColList=LEFT(cl.collist,LEN(collist)-1)
FROM
(SELECT ColumnName+ ' ' + DataType + ' '+Properties+ ',' AS [text()]
FROM #Test
FOR XML PATH(''))cl(collist)

SET @Sql='CREATE TABLE TableName('+@SQLColList + ')'

EXEC(@Sql)

Thanks for this. But what is this FOR XML PATH syntax? I am new to this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 05:17:25

quote:

Originally posted by Abu-Dina

quote:

Originally posted by visakh16

Yup missed a ''. modify like this

create table #test(ColumnName varchar(20),DataType varchar(100),Properties varchar(1000))

insert into #test 
select 'Column1','varchar(50)','DEFAULT ''default value''' 
union all 
select 'Column2','int','IDENTITY(1,1) PRIMARY KEY CLUSTERED' 
union all 
select 'Column3','datetime',''

DECLARE @Sql varchar(8000),@SQLColList varchar(8000)

SELECT @SQLColList=LEFT(cl.collist,LEN(collist)-1)
FROM
(SELECT ColumnName+ ' ' + DataType + ' '+Properties+ ',' AS [text()]
FROM #Test
FOR XML PATH(''))cl(collist)

SET @Sql='CREATE TABLE TableName('+@SQLColList + ')'

EXEC(@Sql)

Thanks for this. But what is this FOR XML PATH syntax? I am new to this.

It will give you a comma delimited list of column names along with datatype and properties

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-11 : 05:44:44

quote:
Originally posted by visakh16

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

Yup missed a ''. modify like this

create table #test(ColumnName varchar(20),DataType varchar(100),Properties varchar(1000))

insert into #test
select 'Column1','varchar(50)','DEFAULT ''default value'''
union all
select 'Column2','int','IDENTITY(1,1) PRIMARY KEY CLUSTERED'
union all
select 'Column3','datetime',''

DECLARE @Sql varchar(8000),@SQLColList varchar(8000)

SELECT @SQLColList=LEFT(cl.collist,LEN(collist)-1)
FROM
(SELECT ColumnName+ ' ' + DataType + ' '+Properties+ ',' AS [text()]
FROM #Test
FOR XML PATH(''))cl(collist)

SET @Sql='CREATE TABLE TableName('+@SQLColList + ')'

EXEC(@Sql)

Thanks for this. But what is this FOR XML PATH syntax? I am new to this.

It will give you a comma delimited list of column names along with datatype and properties

Hi Vikash,

Yes that makes sense. I've done a bit of reading on FOR XML but the bit that I still don't get is the

 cl (collist)

What does that do?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 09:03:13

c1 is the derived table alias name and collist is the alias name of the column

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources