SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Microsoft SQL Temp Tables (without declaring columns – like Informix)?


Microsoft SQL Temp Tables (without declaring columns – like Informix)?

Author
Message
jarrell.dunson
jarrell.dunson
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 12
I recently changed positions, and came from an Informix database environment, where I could use SQL statements to select one or more columns ... and direct the output to a temporary table. In Informix, for temp tables, I neither had to declare the column names, nor the column lengths (only the name of a temp table) - I could simply write:

select [columnname1, columnname2, columnname3 ..] from [database.tablename] where... etc. into temp tablename1 with no log;

Note that in Informix, the temp table stores the column names by default... as well as the data types [by virtue of the data-type being stored in the temp table]. So, if the above statement was executed, then a developer could merely write:

select columname1, columnname2, etc. from tablename1

In my experience, I found this method was very useful - for numerous reasons ('slicing/dicing' the data, using various data sources, etc.)... as well as tremendously fast and efficient.

However, now I am using Microsoft SQL Server, I have not found a way (yet) do the same. In SQL Server, I must declare each column, along with its length:

Create table #tablename1 ( column1 numeric(13,0) );

insert into #tablename1(column1) select [column] from [database.tablename] where …

[Then use the info, as needed]:

select * from #tablename1 [ and do something...]

Drop table #tablename1

Does anyone know of how I could do this and/or set-up this capability in Microsoft SQL Server? I looked at anonymous tables (i.e. Table-Value constructors: http://technet.microsoft.com/en-us/library/dd776382.aspx)... but the guidance stated that declaring the columns was still necessary.

Thanks ahead of time - jrd
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3011 Visits: 3325
Ah! Then you need to be introduced to the INTO Clause ;-)

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
jarrell.dunson
jarrell.dunson
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 12
Thanks Abu, this really helps.
j
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3011 Visits: 3325
You're welcome!

CREATE TABLE SCCTest (Col1 INT, Col2 INT, Col3 INT)

INSERT INTO SCCTest (Col1, Col2, Col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)

SELECT * FROM SCCTest

SELECT * INTO #SCCTemp FROM SCCTest

SELECT * FROM #SCCTemp

DROP TABLE SCCTest
DROP TABLE #SCCTemp



---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17957 Visits: 6431
Abu Dina (11/18/2013)
Ah! Then you need to be introduced to the INTO Clause ;-)


A couple of caveats to using this technique:
- All columns created will default to allow NULL values.
- You may want to try to control the length of any VARCHAR columns that get created in the output table.
- The temp table will have no PRIMARY KEY, but you can add one after setting the participating columns to NOT NULL.
- No constraints (FK, default, etc.) will get inherited by the temp table.

Otherwise, I agree it is quite useful.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
jarrell.dunson
jarrell.dunson
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 12
Dwain,
Thank you very much,
J
jarrell.dunson
jarrell.dunson
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 12
Dwain,
Technically, does the 'into' clause create a temp table in the tempdb. Thus, do I need more the 'public' permission to tempdb? That is, do I need dbwriter perms also?
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3011 Visits: 3325
Dwain's probably asleep right now.. I think he's from New Zealand. :-)

Temp tables local with single # or global with ## are always created in tempdb.

Can I suggest you expand the security folder in your tempdb and locate the Guest account. This user should permissions to write to tempdb. So basically if you have a user that can access your SQL instance and your tempdb has the Guest user then you can read/write without having to add any extra permissions.

Hope this helps.

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3011 Visits: 3325
dwain.c (11/18/2013)
Abu Dina (11/18/2013)
Ah! Then you need to be introduced to the INTO Clause ;-)


A couple of caveats to using this technique:
- All columns created will default to allow NULL values.


Not sure I understand this, just tried the below:

CREATE TABLE SCCTest (Col1 INT NOT NULL, Col2 INT NULL)

INSERT INTO SCCTest(Col1, Col2)
VALUES (1, 2)

SELECT * INTO #SCCTest FROM SCCTest

INSERT INTO #SCCTest (Col1, Col2)
VALUES (NULL, 3)

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Col1', table 'tempdb.dbo.#SCCTest____________________________________________________________________________________________________________0000000007C0'; column does not allow nulls. INSERT fails.
The statement has been terminated.



---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
michal.lisinski
michal.lisinski
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1974 Visits: 1105
Hi Jarrell
I migrated from Informix to MS SQL a few years ago and at the beginning I had similar dilemma.
I simply started using"

SELECT col1,col2 
INTO #tmp_tbl
FROM some_table;



instead of informix

SELECT col1,col2 
FROM some_table
INTO TEMP tmp_tbl WITH NO LOG;




Br,
Mike
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search