Dynamic SQL to create table variable

Question

Dynamic SQL to create table variable

fec

SSC Eights!

Points: 941
More actions
May 8, 2008 at 5:41 pm

#182048

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ksullivan SSCrazy Points: 2346 More actions · Answer 1

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?

fec SSC Eights! Points: 941 More actions · Answer 2

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)

fec SSC Eights! Points: 941 More actions · Answer 3

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

ksullivan SSCrazy Points: 2346 More actions · Answer 4

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.