Dynamic SQL to create table variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Dynamic SQL to create table variable

Dynamic SQL to create table variable

Rate Topic

Display Mode

Topic Options

Author

Message

fec

Posted Thursday, May 08, 2008 5:41 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 23, 2012 10:07 PM
Points: 27, Visits: 88

Task: carve a sp to perform a bulk insert from a txt file into a table variable which can then be further processed later on in the sp.

Problems: the names of and number of columns within the table variable must be able to change dynamically. The stored proc may be run by multiple people at the same time so the table variable must be available only within the scope of each instance. (ie 2 people running the same code will not interfere with each others version table variable) - i believe using a table variable instead of a temp table will handle this.

Post #497517

ksullivan

Posted Thursday, May 08, 2008 6:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 7:12 PM
Points: 144, Visits: 445

You can't bulk insert into table variables. You can bulk insert into local temp tables. That satisfies your multiple simultaneous user requirement.

Will you at least know the names and number of columns at run time? Perhaps by looking them up in some sort of data dictionary?

Post #497522

fec

Posted Thursday, May 08, 2008 6:03 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 23, 2012 10:07 PM
Points: 27, Visits: 88

If i use a local temp table do i use the prefix # ?
ie #tbl123

Is this the best solution?

1.Create the local temp table.
2.Use DynamicSQL to alter the local temp table

eg.

^{declare @dynamicsql as varchar(1000)
declare @DataloggerColumsSQL as varchar(255)

set @DataloggerColumsSQL = '[Rate43] [decimal](18, 5) NULL, [Tonn43] [decimal](18, 0) NULL'

--create the local temp table

CREATE TABLE #tblfec (
[LoadDate] [smalldatetime] NULL,
[LoadTime] [nvarchar](50) NULL
)

select * from #tblfec

-- dynamically add the columns

SET @DynamicSQL = 'ALTER TABLE #tblfec ADD ' + @DataloggerColumsSQL
EXEC(@DynamicSQL)

print @DynamicSQL
SET @DynamicSQL = 'select * from #tblfec'
EXEC(@DynamicSQL)}

Post #497523

fec

Posted Thursday, May 08, 2008 6:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 23, 2012 10:07 PM
Points: 27, Visits: 88

The columns names and data types are being provided from the application calling the sp.

Post #497524

ksullivan

Posted Thursday, May 08, 2008 6:13 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 7:12 PM
Points: 144, Visits: 445

That will work.

Do you have to make or use format files too? Be sure they allow for your LoadDate and LoadTime columns. Of course, you could make the table with a dummy column, add all the ones you really need with dynamic sql, drop the dummy, insert the text, then add the LoadDate and LoadTime but that's messy.

Post #497525

« Prev Topic | Next Topic »

Permissions