creating temp tables, must i specify all columns ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

creating temp tables, must i specify all columns ?

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-02-16 : 11:41:12

Cant I just create a temp table without naming all the columns, if im just going to dump data into it from a stored procedure call ? Speaking of which,

how ?

insert into #temp_table
exec my_proc_name @some_var = 'some_value'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 11:58:55

You must alter the stored procedure my_proc_name to name all column in the result set.
And since you are going to create a temp table with the resultset, no duplicate column names are allowed.

Peter Larsson
Helsingborg, Sweden

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-02-16 : 12:09:05

what am i doing wrong ?


create table #t1
insert into #t1 exec sp_columns @table_name = 'MAIN TABLE'
select column_name from #t1
drop #t1

result:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'insert'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '#t1'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 13:49:03

Maybe you should try

select column_name from information_schema.columns where table_name = @tablename

instead?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 13:49:40

When using CREATE TABLE, you must specify the columns and their datatypes!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources