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