Constructing create table query from the meta info of multiple rows

[network18]

Hi,
Needs to construct the tables with the meta info retrived by another stored procedure I wrote, given the table id or name.
There could be some flaws in the logic, not sure.
The flow is -
- iterate through one table which stores tbl name, id info
- pass above info to sp which would return meta info to calling cursor as result set
- store this result set into temp tbl so that another cursor can iterate through them, to construct "create table query" as a string
- end inner cursor
- execute the concatenated string query
- end main or outer cursor

It looks like below -

CREATE PROCEDURE CREATE_TBL
@tbl_id INT
AS
DECLARE @res nvarchar(200) 
select mcol.NEW_COL, col.DATA_TYPE, col.COLUMN_DEFAULT, col.IS_NULLABLE, col.CHARACTER_MAXIMUM_LENGTH size--, mcol.TBL_ID
from seminator.INFORMATION_SCHEMA.COLUMNS col 
inner join MAPPING_TBL mtbl
on col.TABLE_NAME = mtbl.OLD_TBL collate Latin1_General_CI_AS
inner join MAPPING_COL mcol
on col.COLUMN_NAME = mcol.OLD_COL collate Latin1_General_CI_AS and mcol.TBL_ID = mtbl.ID
where mtbl.ID = @tbl_id
 order by COLUMN_DEFAULT

 GO
-- 


CREATE TABLE #plan(
    NEW_COL varchar(40) NOT NULL,
    DATA_TYPE [varchar](30) NULL,
    COLUMN_DEFAULT [varchar](15) NULL,
    IS_NULL [varchar](10) NULL,
    SIZE [varchar](30) NULL
)
GO



Declare @query varchar(5000)
Declare @tbl_id int
Declare c Cursor For SELECT ID FROM MAPPING_TBL where old_tbl = 'ADRESSE'
Open c

Fetch next From c into @tbl_id

While @@Fetch_Status=0 Begin
   --do operations using this current tbl name
   SET @query = 'CREATE TABLE TBL'
   INSERT INTO #plan EXEC CREATE_TBL @tbl_id
   --select * from #plan
   -------------------------2nd
Declare @col varchar(50)
Declare @type varchar(50)
Declare @def varchar(50)
Declare @is_null varchar(50)
Declare @size varchar(50)
Declare @col_list varchar(5000)

Declare c2 Cursor For SELECT * FROM #plan
Open c2

Fetch next From c2 into @col,@type,@def,@is_null,@size

While @@Fetch_Status=0 Begin
   --do operations using this current row

   SET @col_list +=  @col+' '+@type+' '+'('+@size+')'+@is_null+','
   select @col_list
   Fetch next From c2 into @col,@type,@def,@is_null,@size
End


Close c2
Deallocate c2
   -------------------------2nd end
   Fetch next From c into @tbl_id
End


Close c
Deallocate c

And having tough time creating @col_list += @col+' '+@type+' '+'('+@size+')'+@is_null+','

Any suggestions & improvements would be appreciated
Thanks

[pritaeas]

I do not think += is supported. Have you tried @col_list = @col_list + @col + ... etc. ?

Note that you will end with a comma in this situation, that could trigger an error.

[network18]

Yes, that's right, I tried with

SET @col_list = @col_list + @col+' '+@type+' '+'('+@size+')'+@is_null+','

But didn't work either

And about the comma, I am sure there must be some way we can strip it in the end

[network18]

yes, I found the reason behind it, if any of the variables like @col_list, @col or @type are null the whole string would be null, making it the dina, output as null..
But still as need to construct a string (part of a create table query) like "col1 varchar (20) " etc
It would be part of the whole string as -

"create table tbl_name(
col1 int ,
col2 varchar (20) not null)"

eventually. Any thought?

[pritaeas]

Make sure that you pass empty strings instead of NULL's.

[network18]

yes, that's what I am doing now, I am converting them to empty strings if isnull and also initializing the variables with empty strings, which solved the issue and I could create a proper query in the string format, besides I have added identity to the columns as well, wherever applicable.

Now, I am wondering is it possible to avoid the use of temp table at all, can I use Common table expression to achive the same goal??
Do I need to create a fresh thread to address this issue?

[pritaeas]

It should be possible. You are filling the temp table with the results from a query. If you can just loop those results, you can build your query from that directly.

[network18]

And how would be that, can you please elaborate with modifying some code.

I used temp table to store set of rows , so that I could iterate over them, and do some operation for each row; the result set returned from procedure is stored into temp, couldn't think any other way to do this at the moment.

As if you suggest to modify the procedure itself, to include CTE in it for each table id passed to it, and iterate over the result set of CTE inside the procedure itself, and return back the constructed query string to the calling cursor.
could you please elaborate, about CTE, it would need an anchor member & a recursive member

[pritaeas]

I would have to recreate your situation. It will have to wait a while, because I do not have access to my servers at work.

[network18]

That's fine, I look forward to it. Please feel free to ask, if need any more details. thanks

[pritaeas]

Can you show an example of what CREATE_TBL returns (for one specific tbl_id) ?

[network18]

CREATE_TBL would return the result set like below for given table id -

NEW_COL     DATA_TYPE       COLUMN_DEFAULT  IS_NULLABLE     size    IS_IDENTITY
BP_NR       int             NULL                NO          NULL    BP_NR
BP_POSNR    tinyint         NULL                NO          NULL    NULL
BP_MENGE    tinyint         NULL                NO          NULL    NULL

It would return the meta info for any gievn table's object id

[pritaeas]

I've not yet solved your issue. Am having some MSSQL issues I need to solve first, sorry.

[network18]

That's ok..maybe later both of us can give a thought on this.and its not the problem, rather better architectural design aproach, so its possible different people might suggest diff things.
Anyway I have finished creating this entire script, and right now I am workin on its last phase of inserting data back.
But improving it, to make more efficient would be a nice thing too.