Click here to monitor SSC
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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
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)?
jarrell.dunson
jarrell.dunson
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
sorry (duplicate)
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
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 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
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
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 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
michal.lisinski
michal.lisinski
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 1090
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].[table], simple example:


INSERT INTO [svrA\instanceA].dbA.dbo.tableA
SELECT col1,col2 FORM any_table;



Regards
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