Dyamic SQl to create table - 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)

Dyamic SQl to create table

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-11 : 09:59:10

Hi all,

I hope the following makes sense. Please feel free to ask me any questions about my problem!

The objective of this exercise is pretty straightfoward. I want to be able to create a new table dynamically. I have the following table which contains my column headers for the new table

create table dbo.MyTypeLessTable (
		Ident		int identity(1, 1),
		ColumnNames varchar(200))

insert into dbo.MyTypeLessTable (ColumnNames)
select 'Column1' union
select 'DD_Addr1' union 
select 'DD_Addr2' union
select 'DD_Addr3' union
select 'DD_County' union
select 'Column6' union
select 'Column7' union
select 'Column8'

Seems simple enough, the follownig code will generate the sql string for my new table :

declare @ColumnHolder varchar(200)
declare @sql varchar(max)
declare @Ident tinyint

set @Ident = 1
set @sql = 'create table Staging.dbo.myResult ('


set @ColumnHolder = (select ColumnNames from dbo.MyTypeLessTable where Ident  = @Ident)

while @ColumnHolder is not null
	begin	
		set @sql = @sql + '[' + @ColumnHolder + '] varchar(200),' 
		set @Ident = @Ident + 1
		set @ColumnHolder = (select ColumnNames from MyTypeLessTable where Ident  = @Ident)
	end

set @sql = left(@sql, len(@sql)-1)+')'

select @sql

My problem is the columns prefixed with DD_ For these ones I want to set their data type and length to be the same as column in another table.
The table that has the same named columns is :

create table dbo.MyTypedTable (
	DD_Addr1  varchar(50),
	DD_Addr2 varchar(90),
	DD_Addr3 varchar(20),
	DD_County varchar(100)
	)

I know I need to use INFORMATION_SCHEMA.COLUMNS but for the lifeof me, i can't figire out how to include this bit of logic
in to my above WHILE loop.

The sql string that should be generated at the end should be :

create table Staging.dbo.myResult ([Column1] varchar(200),[Column6] varchar(200),[Column7] varchar(200),[Column8] varchar(200),[DD_Addr1] varchar(50),[DD_Addr2] varchar(90),[DD_Addr3] varchar(20),[DD_County] varchar(100))

Your help would be much appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 10:32:21

change like this & try:-

while @ColumnHolder is not null
begin
set @sql = @sql + '[' + @ColumnHolder +
CASE WHEN LEFT(@ColumnHolder,3)='DD_'
THEN (SELECT '] ' + DATA_TYPE + '(' +CHARACTER_MAXIMUM
_LENGTH +'),' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='dbo.MyTypeLessTable' AND COLUMN_NAME =@ColumnHolder)
ELSE '] varchar(200),'
END
set @Ident = @Ident + 1
set @ColumnHolder = (select ColumnNames from MyTypeLessTable where Ident = @Ident)
end

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-11 : 10:47:32

quote:
Originally posted by visakh16

change like this & try:-

while @ColumnHolder is not null
begin
set @sql = @sql + '[' + @ColumnHolder +
CASE WHEN LEFT(@ColumnHolder,3)='DD_'
THEN (SELECT '] ' + DATA_TYPE + '(' +CHARACTER_MAXIMUM
_LENGTH +'),' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='dbo.MyTypeLessTable' AND COLUMN_NAME =@ColumnHolder)
ELSE '] varchar(200),'
END
set @Ident = @Ident + 1
set @ColumnHolder = (select ColumnNames from MyTypeLessTable where Ident = @Ident)
end

Hi there,

I've tried the code but I get an error :

Conversion failed when converting the varchar value 'create table Staging.dbo.myResult ([Column1' to data type int

I've tried convertnig the CHRACTER_MAXIMUM_LENGTH to varchar but the string returns NULL?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-11 : 10:56:46

When I run the CASE statment on its own and apply a cast on the CHARACTER_MAXIMUM_LENGTH field it works but when I apply it to my WHILE loop the sql string returns NULL!

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-11 : 11:06:21

Okay, I've fixed the problem! But the create table statment generated jumbles up the column positioning!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 11:12:51

jumbles up? what do you mean?Arent yoiu using ident value to retrieve them?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-11 : 11:45:28

quote:
Originally posted by visakh16

jumbles up? what do you mean?Arent yoiu using ident value to retrieve them?

Sorry my friend. It's me being stupid. The columns appear in the order I inserted them in. For some reason I thought I had my DD_ column inserted in the middle of the table.

It's all workig fine. Thanks ever so much for your help!

Have a great evening.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 11:46:25

You are welcome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources