Create table with cursor data as columns
-
Hi all,
Here i have one question that
I am fetching some names through the cursor.
Now i want to create one table columns with that names.
For ex: If the cursor returns XYZ,ABC then want to create table with xyz and abc are columns names
Like:Create table tab1(xyz varchar(100),abc varchar(100))
Could any one help on this?
Thanks,
Bagath
-
Dynamic SQL. Build up the create table statement as you un through the cursor, then execute the statement. Something like the following (rough pseudo-code)
SET @Creation = 'CREATE TABLE MYTable ('
Fetch next from myCursor into @columnName
WHILE @@Fetch_Status=0
begin
set @Creation =@Creation + @columnName + ' VARCHAR(50), '
Fetch next from myCursor into @columnName
END
SET @Creation = LEFT(@Creation, LEN(@creation-2) + ')'
EXEC (@Creation)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you very much for your valuable suggestion.
I have tried as above and succeeded
Thanks once again
Cheers,
Bagath.
-
Would be nice if you posted your solution so the rest of us can learn something...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Why not sir....
Following is the script for to create a table with cursor data....
ALTER PROCEDURE Reports1 @START_DATE DATETIME,@END_DATE DATETIME
AS
DECLARE @T_DBANAME VARCHAR(40)
DECLARE @T_TEAM INT
DECLARE @CreateTab VARCHAR(2000)
SET @CreateTab='CREATE TABLE TabDbaData('
--CURSOR TO FETCH ALL DBA NAMES
DECLARE TEMP_DBA_NAMES_CURSOR CURSOR FOR
SELECT DISTINCT DBANAME,TEAM FROM CLARIFY..CLIENTALLOCATIONS ORDER BY TEAM,DBANAME ASC
OPEN TEMP_DBA_NAMES_CURSOR
FETCH NEXT FROM TEMP_DBA_NAMES_CURSOR into @T_DBANAME,@T_TEAM
WHILE (@@FETCH_STATUS =0)
BEGIN
PRINT @T_DBANAME
SET @T_DBANAME=REPLACE(@T_DBANAME,' ','_')
SET @T_DBANAME=REPLACE(@T_DBANAME,'.','_')
SET @CreateTab=@CreateTab+@T_DBANAME+' '+'VARCHAR(50),'
PRINT @CreateTab
FETCH NEXT FROM TEMP_DBA_NAMES_CURSOR INTO @T_DBANAME,@T_TEAM
END
SET @CreateTab=LEFT(@CreateTab,LEN(@CreateTab)-1)
SET @CreateTab=@CreateTab+')'
PRINT @CreateTab
EXEC(@CreateTab)
CLOSE TEMP_DBA_NAMES_CURSOR
DEALLOCATE TEMP_DBA_NAMES_CURSOR
Cheers,
Bagath.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply