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 Tuesday, November 19, 2013 9:06 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
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)?
Post #1515629
Posted Tuesday, November 19, 2013 9:09 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
sorry (duplicate)
Post #1515631
Posted Tuesday, November 19, 2013 5:20 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 @ 8:30 PM
Points: 3,627, Visits: 5,273
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!
Post #1515842
Posted Tuesday, November 19, 2013 6:33 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 @ 8:30 PM
Points: 3,627, Visits: 5,273
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!
Post #1515858
Posted Wednesday, November 20, 2013 12:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 8:05 AM
Points: 285, Visits: 334
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
Post #1515912
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse