November 18, 2013 at 1:16 pm
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
November 18, 2013 at 1:29 pm
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[/url]
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
November 18, 2013 at 1:39 pm
Thanks Abu, this really helps.
j
November 18, 2013 at 1:50 pm
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[/url]
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
November 18, 2013 at 6:15 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 19, 2013 at 5:13 am
Dwain,
Thank you very much,
J
November 19, 2013 at 7:35 am
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?
November 19, 2013 at 7:50 am
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[/url]
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
November 19, 2013 at 7:56 am
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[/url]
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
November 19, 2013 at 8:17 am
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
November 19, 2013 at 9:06 am
Mike,
Thanks.
I was trying to select a large dataset, from a read-only SQL Server database (that is, the data is stored on ServerA in #tmp_tbl)... and insert the data into another instance of SQL server (insert into newtable1 on ServerB)? How do I do this using two db connections?
insert into newtable1 (col1, col2) <--- newtable1 is ServerB, db connectionB
values (select col1, col2 from #tmp_tbl) <--- #tmp_tbl is ServerA, db connectionA
Do I need to write the output to a file (from ServerA.database.table), then read it again (inserting it into ServerB.database.table)?
November 19, 2013 at 9:09 am
sorry (duplicate)
November 19, 2013 at 5:20 pm
Abu Dina (11/19/2013)
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.
Abu - That's an interesting result. Perhaps in my case the NOT NULL attribute only occurred because the SELECT query is more complicated so the INTO can't inherit the column attributes.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 19, 2013 at 6:33 pm
Abu Dina (11/19/2013)
Dwain's probably asleep right now.. I think he's from New Zealand. 🙂
Bangkok actually. 4-5 time zones removed from NZ.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 20, 2013 at 12:22 am
Hi
If remote table is a table on a database server other than the current server. The general syntax for accessing a table on another informix server is: database@server:[owner.]table, so as you remeber, you can do:
INSERT INTO dbA@svrA:tableA
SELECT col1,col2 FORM any_table;
Syntax for MS SQL is [server\instance].[database].[schema].
, simple example:
INSERT INTO [svrA\instanceA].dbA.dbo.tableA
SELECT col1,col2 FORM any_table;
Regards
Mike
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply