Inserting into a temp table and schema binding? - 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 2000 Forums

SQL Server Development (2000)

Inserting into a temp table and schema binding?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-21 : 08:19:44

MM submitted "Is it possible to insert into a temparary table and not have to define the schema of the table, but reference one of the schema for a table you already have?

Example

Instead of

CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t

Can you bind the temp table to an existing table?

or do something like...

CREATE TABLE #t(Schema.TableNameX)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t"

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-21 : 08:52:59

Check BOL for select...into...
Its not just limited to temporary tables.
However I would suggest you always explicitly create the temp table

Here's an example with data..

use pubs
go
select * into #authorstemp from authors
go
select * from #authorstemp
go
drop table #authorstemp
go

Here's an example without data (schema only)..

use pubs
go
select * into #authorstemp from authors where 1=0
go
use tempdb
go
sp_help #authorstemp
go
drop table #authorstemp
go

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources