Dynamically creating 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 2008 Forums

Transact-SQL (2008)

Dynamically creating table

Author

Topic

pradeepmanne
Starting Member

31 Posts

Posted - 2011-12-21 : 09:22:14

hi,

i need a store procedure to create table dynamically
based on the value in temp table
i.e
if i pass 5 then table should be like
colname ='col'+ value
col1,col2,col3,col4,col5 all of them of same datatype

my SP to alter the table i need loop in it
Create proc [dbo].[altertemp1](@Value_c varchar)
AS
BEGIN
DECLARE @intFlag INT
DECLARE @ColName nvarchar(100)
set @ColName='col'
DECLARE @DynamicSQL nvarchar(250)
SET @intFlag = 3
WHILE (@intFlag <=@Value_c)
BEGIN
set @ColName= @ColName + cast(@intFlag as nvarchar(100))
SET @DynamicSQL = 'ALTER TABLE ##Mytemp ADD ['+ CAST(@ColName AS nvarchar(100)) +'] binary(100) NULL'
EXEC(@DynamicSQL)
SET @intFlag = @intFlag + 1

end

END

the above SP am trying am getting like if i pass value 5 then mu out put is like col1,col12,col123 .....,any suggestion in mysp
thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:15:26

why are you trying to build table like this?
Whats the need of this requirement? what are you going to store in this table? Cant the metadata of resultset be determined beforehand to create table and do the population?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 13:32:11

yeah...can you give a good reason WHY you are trying to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-21 : 13:56:09

Sounds like a really bad idea, but here is a tweaked version of your stored procedure that should work:

Create proc [dbo].[altertemp1](@Value_c varchar)
AS
BEGIN
    DECLARE @intFlag INT
    DECLARE @ColName nvarchar(100)
    DECLARE @ColNameBase nvarchar(100)
    set @ColNameBase='col'
    DECLARE @DynamicSQL nvarchar(250)
    SET @intFlag = 3
    WHILE (@intFlag <= CAST(@Value_c AS INT))
    BEGIN
        set @ColName= @ColNameBase + cast(@intFlag as nvarchar(100)) 
        SET @DynamicSQL = 'ALTER TABLE ##Mytemp ADD ['+ CAST(@ColName AS nvarchar(100)) +'] binary(100) NULL'
        EXEC(@DynamicSQL)
        SET @intFlag = @intFlag + 1
    END
END

pradeepmanne
Starting Member

31 Posts

Posted - 2011-12-22 : 08:26:10

hi,
i nedd a Temp tables so that i can insert binary values into it
so based on the column count of excelfile need to create intially i created a tables with 2 columns if the value is more than that it will add the columns to the created table

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 10:08:01

how do you know the column count of an excel file?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 10:42:46

quote:
Originally posted by pradeepmanne

hi,
i nedd a Temp tables so that i can insert binary values into it
so based on the column count of excelfile need to create intially i created a tables with 2 columns if the value is more than that it will add the columns to the created table

why should you do like that?

you can directly create table based on number of excel columns in one shot using OPENROWSET
something like


SELECT * INTO #NewTable 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=excel path here;', 
    'SELECT * FROM [SheetName$]')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pradeepmanne
Starting Member

31 Posts

Posted - 2011-12-24 : 07:30:01

i have two storeprocedures which will create table with cols 2
if max value is mote then another SP will add columns to it

my sp are
Begin
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##Mytemp')

)

DROP TABLE ##Mytemp
ELSE

CREATE TABLE ##Mytemp(Col1 binary(100), Col2 binary(200))
End

PROCEDURE altertemp3(@ColNumMax int)
AS
BEGIN
DECLARE @counter INT
DECLARE @ColName nvarchar(100)
DECLARE @DynamicSQL nvarchar(250)

SET @counter = 3
WHILE (@counter <= @ColNumMax) BEGIN
SET @ColName= 'Col' + cast(@counter as nvarchar(100))
SET @DynamicSQL = 'ALTER TABLE ##Mytemp ADD ['+ @ColName +'] binary(100) NULL'
EXEC(@DynamicSQL)
SET @counter = @counter + 1
END
END

i want it to be in a single sp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-24 : 07:37:07

why do you add columns dynamically? are you not sure on resultset of sp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pradeepmanne
Starting Member

31 Posts

Posted - 2011-12-24 : 08:04:53

quote:
Originally posted by visakh16

why do you add columns dynamically? are you not sure on resultset of sp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

my asp.net application will have data to be uploaded into temptable
based on the selection (max cols) table has to be created
i followed this process.
any advice for new process

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-24 : 09:24:01

based on selection why do you need to vary number of columns?
Isnt it matter of keeping static number of columns based on your selection boxes and then populating values. Other columns will have default NULL value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources