Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Microsoft SQL Temp Tables (without declaring columns – like Informix)? Expand / Collapse
Author
Message
Posted Monday, November 18, 2013 1:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:42 AM
Points: 6, 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


Post #1515338
Posted Monday, November 18, 2013 1:29 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
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
Post #1515341
Posted Monday, November 18, 2013 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:42 AM
Points: 6, Visits: 12
Thanks Abu, this really helps.
j
Post #1515343
Posted Monday, November 18, 2013 1:50 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
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
Post #1515346
Posted Monday, November 18, 2013 6:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:49 PM
Points: 3,427, Visits: 5,378
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!
Post #1515408
Posted Tuesday, November 19, 2013 5:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:42 AM
Points: 6, Visits: 12
Dwain,
Thank you very much,
J
Post #1515501
Posted Tuesday, November 19, 2013 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:42 AM
Points: 6, 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?
Post #1515550
Posted Tuesday, November 19, 2013 7:50 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
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
Post #1515559
Posted Tuesday, November 19, 2013 7:56 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
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
Post #1515567
Posted Tuesday, November 19, 2013 8:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 5, 2014 12:02 AM
Points: 315, Visits: 344
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
Post #1515588
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse