Create dynamic 'Create Table' statement
Hello,
I have this logic below where it gets the columns from a table - 'Proc_Layouts' and there is a while loop that puts these columns together, and at the end, uses these columns to create a table.
I had it working one time, where it did select the columns and create the string - 'col1 varchar(10), col2 varchar(20), col3(varchar2), .....)
But when I added the last statement - 'Create table ' +
It doesn't even print it or retrieve the columns. Am I out of scope somewhere or is there a better way to write this.
The 'Print @pSQL' statement doesn't seem to work and the result or message page just shows:
'(42 row(s) affected)'
Not showing the DDL statement that is supposed to be shown.
Thanks,
Steve
Declare @pSQL varchar(8000)
, @pTypeKey int
, @pEXTColumns varchar(8000)
, @FieldOrder int
, @ColumnName varchar(80)
, @CurrentVariable Varchar(10)
, @FieldLength varchar(4)
,@ud_ext_temp_table varchar(40)
Set @pTypeKey = 1711
Set @ud_ext_temp_table = 'My_EXT_Table'
Declare @columns table
(
FieldOrder int,
columnname varchar(80),
fieldlength varchar(5)
)
Insert Into @columns (FieldOrder, ColumnName, FieldLength)
Select FieldOrder, fieldname, fieldlength
From proc_Layouts
Where LayoutKey = @pTypeKey
Set @FieldOrder = (Select Min(FieldOrder) From @columns)
While @FieldOrder Is Not Null
Begin
Select @ColumnName = IsNull(columnname,'colxxx'
@FieldLength = IsNull(FieldLength, '80')
From @columns
Where FieldOrder = @FieldOrder
Set @pEXTColumns = @pEXTColumns + ', '+@ColumnName+ ' VarChar(' + @FieldLength + ') '
Set @FieldOrder = (Select Min(fieldorder) From @columns Where fieldorder > @fieldorder)
End
Set @pEXTColumns = right(@pEXTColumns, len(@pEXTColumns)) -- remove the first character (comma) of this string..
set @pSQL = 'Create Table ' + @ud_ext_temp_table + ' (' + @pEXTColumns + ')'
print @pSQL
I have this logic below where it gets the columns from a table - 'Proc_Layouts' and there is a while loop that puts these columns together, and at the end, uses these columns to create a table.
I had it working one time, where it did select the columns and create the string - 'col1 varchar(10), col2 varchar(20), col3(varchar2), .....)
But when I added the last statement - 'Create table ' +
It doesn't even print it or retrieve the columns. Am I out of scope somewhere or is there a better way to write this.
The 'Print @pSQL' statement doesn't seem to work and the result or message page just shows:
'(42 row(s) affected)'
Not showing the DDL statement that is supposed to be shown.
Thanks,
Steve
Declare @pSQL varchar(8000)
, @pTypeKey int
, @pEXTColumns varchar(8000)
, @FieldOrder int
, @ColumnName varchar(80)
, @CurrentVariable Varchar(10)
, @FieldLength varchar(4)
,@ud_ext_temp_table varchar(40)
Set @pTypeKey = 1711
Set @ud_ext_temp_table = 'My_EXT_Table'
Declare @columns table
(
FieldOrder int,
columnname varchar(80),
fieldlength varchar(5)
)
Insert Into @columns (FieldOrder, ColumnName, FieldLength)
Select FieldOrder, fieldname, fieldlength
From proc_Layouts
Where LayoutKey = @pTypeKey
Set @FieldOrder = (Select Min(FieldOrder) From @columns)
While @FieldOrder Is Not Null
Begin
Select @ColumnName = IsNull(columnname,'colxxx'
@FieldLength = IsNull(FieldLength, '80')
From @columns
Where FieldOrder = @FieldOrder
Set @pEXTColumns = @pEXTColumns + ', '+@ColumnName+ ' VarChar(' + @FieldLength + ') '
Set @FieldOrder = (Select Min(fieldorder) From @columns Where fieldorder > @fieldorder)
End
Set @pEXTColumns = right(@pEXTColumns, len(@pEXTColumns)) -- remove the first character (comma) of this string..
set @pSQL = 'Create Table ' + @ud_ext_temp_table + ' (' + @pEXTColumns + ')'
print @pSQL
can you select @pSQL . .. does that return anything?
ASKER
Thanks for the replies, the @pSQL is coming back as NULL, when I do a 'Select @pSQL' and looks like some kind of endless loop somewhere?
Should I reinitialize @FieldOrder somewhere or another variable, to kick it out of the endless loop?
Thanks,
Steve
Should I reinitialize @FieldOrder somewhere or another variable, to kick it out of the endless loop?
Thanks,
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also you don't need cursor and temp table . try this code intead:
Declare @pSQL varchar(8000), @pTypeKey int, @ud_ext_temp_table varchar(40)
SELECT @pTypeKey = 1711, @ud_ext_temp_table = 'My_EXT_Table'
SELECT @pSQL = isnull(@pSQL + ',' ,'') + '[' +fieldname + '] VarChar(' + fieldlength + ') '
FROM proc_Layouts
WHERE LayoutKey = @pTypeKey
ORDER BY fieldorder
SET @pSQL = 'Create Table ' + @ud_ext_temp_table + ' (' + @pSQL + ')'
print @pSQL
Declare @pSQL varchar(8000), @pTypeKey int, @ud_ext_temp_table varchar(40)
SELECT @pTypeKey = 1711, @ud_ext_temp_table = 'My_EXT_Table'
SELECT @pSQL = isnull(@pSQL + ',' ,'') + '[' +fieldname + '] VarChar(' + fieldlength + ') '
FROM proc_Layouts
WHERE LayoutKey = @pTypeKey
ORDER BY fieldorder
SET @pSQL = 'Create Table ' + @ud_ext_temp_table + ' (' + @pSQL + ')'
print @pSQL
ASKER
Thanks for the solution as the setting of the @pEXTColumns = '' in the beginning, worked and created the DDL statement just fine.
Thanks also for the other logic, for creating without a cursor.
Much appreciated!
Thanks,
Steve
Thanks also for the other logic, for creating without a cursor.
Much appreciated!
Thanks,
Steve
Regards,