temp table in linked server

FAQ

Profile

temp table in linked server

Database Help (Home) -> Programming

Next: Can't open tables from object explorer using ssms..

Author

Message

rodchar

External

Since: Jan 11, 2008
Posts: 105

(Msg. 1) Posted: Wed Apr 15, 2009 1:44 pm Post subject: temp table in linked server Archived from groups: microsoft>public>sqlserver>programming (more info?)

hey all, can someone please tell me if the following is possible select * into [LINKED_SERVER].#tempTable FROM [LINKED_SERVER].[Database].dbo.[tableName] thanks, rodchar >> Stay informed about: temp table in linked server

TheSQLGuru

External

Since: Jan 11, 2008
Posts: 579

(Msg. 2) Posted: Wed Apr 15, 2009 4:04 pm Post subject: Re: temp table in linked server [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Try it and see! It is a very easy thing to test. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "rodchar" wrote in message > hey all, > > can someone please tell me if the following is possible > > select * > into [LINKED_SERVER].#tempTable > FROM [LINKED_SERVER].[Database].dbo.[tableName] > > thanks, > rodchar >> Stay informed about: temp table in linked server

rodchar

External

Since: Jan 11, 2008
Posts: 105

(Msg. 3) Posted: Wed Apr 15, 2009 4:04 pm Post subject: Re: temp table in linked server [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

it creates on my local tempdb instead of the remote one. "TheSQLGuru" wrote: > Try it and see! It is a very easy thing to test. > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "rodchar" wrote in message > > > hey all, > > > > can someone please tell me if the following is possible > > > > select * > > into [LINKED_SERVER].#tempTable > > FROM [LINKED_SERVER].[Database].dbo.[tableName] > > > > thanks, > > rodchar > > > >> Stay informed about: temp table in linked server

Tom Moreau

External

Since: Apr 21, 2004
Posts: 502

(Msg. 4) Posted: Wed Apr 15, 2009 5:51 pm Post subject: Re: temp table in linked server [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Instead of doing a "push", why not do a pull? IOW, do the query on the other server and haul the data from the other: -- execute this on LINKED_SERVER select * into #temp from [ORIGINAL_SERVER].[Database].dbo.[tableName] -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "rodchar" wrote in message it creates on my local tempdb instead of the remote one. "TheSQLGuru" wrote: > Try it and see! It is a very easy thing to test. > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "rodchar" wrote in message > > > hey all, > > > > can someone please tell me if the following is possible > > > > select * > > into [LINKED_SERVER].#tempTable > > FROM [LINKED_SERVER].[Database].dbo.[tableName] > > > > thanks, > > rodchar > > > >> Stay informed about: temp table in linked server

rodchar

External

Since: Jan 11, 2008
Posts: 105

(Msg. 5) Posted: Wed Apr 15, 2009 5:51 pm Post subject: Re: temp table in linked server [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

the thing is the remote server is pretty much off limits i can only do a link server to it with read-only access, is that going to be a problem "Tom Moreau" wrote: > Instead of doing a "push", why not do a pull? IOW, do the query on the > other server and haul the data from the other: > > -- execute this on LINKED_SERVER > select > * > into > #temp > from > [ORIGINAL_SERVER].[Database].dbo.[tableName] > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "rodchar" wrote in message > > it creates on my local tempdb instead of the remote one. > > "TheSQLGuru" wrote: > > > Try it and see! It is a very easy thing to test. > > > > -- > > Kevin G. Boles > > Indicium Resources, Inc. > > SQL Server MVP > > kgboles a earthlink dt net > > > > > > "rodchar" wrote in message > > > > > hey all, > > > > > > can someone please tell me if the following is possible > > > > > > select * > > > into [LINKED_SERVER].#tempTable > > > FROM [LINKED_SERVER].[Database].dbo.[tableName] > > > > > > thanks, > > > rodchar > > > > > > > > >> Stay informed about: temp table in linked server

Tom Moreau

External

Since: Apr 21, 2004
Posts: 502

(Msg. 6) Posted: Wed Apr 15, 2009 9:41 pm Post subject: Re: temp table in linked server [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You can still create temporary objects where you have read-only access. An admin will have to set up the linked server for you from that server back to the original server. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "rodchar" wrote in message the thing is the remote server is pretty much off limits i can only do a link server to it with read-only access, is that going to be a problem "Tom Moreau" wrote: > Instead of doing a "push", why not do a pull? IOW, do the query on the > other server and haul the data from the other: > > -- execute this on LINKED_SERVER > select > * > into > #temp > from > [ORIGINAL_SERVER].[Database].dbo.[tableName] > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "rodchar" wrote in message > > it creates on my local tempdb instead of the remote one. > > "TheSQLGuru" wrote: > > > Try it and see! It is a very easy thing to test. > > > > -- > > Kevin G. Boles > > Indicium Resources, Inc. > > SQL Server MVP > > kgboles a earthlink dt net > > > > > > "rodchar" wrote in message > > > > > hey all, > > > > > > can someone please tell me if the following is possible > > > > > > select * > > > into [LINKED_SERVER].#tempTable > > > FROM [LINKED_SERVER].[Database].dbo.[tableName] > > > > > > thanks, > > > rodchar > > > > > > > > >> Stay informed about: temp table in linked server

rodchar

External

Since: Jan 11, 2008
Posts: 105

(Msg. 7) Posted: Thu Apr 16, 2009 6:09 am Post subject: Re: temp table in linked server [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

> > -- execute this on LINKED_SERVER > > select > > * > > into > > #temp > > from > > [ORIGINAL_SERVER].[Database].dbo.[tableName] Ok, so how would i execute this on a linked server? Do i use EXEC() AT or Select * FROM OPENQUERY? Would either of these work? -rod "Tom Moreau" wrote: > You can still create temporary objects where you have read-only access. An > admin will have to set up the linked server for you from that server back to > the original server. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "rodchar" wrote in message > > the thing is the remote server is pretty much off limits i can only do a > link > server to it with read-only access, is that going to be a problem > > "Tom Moreau" wrote: > > > Instead of doing a "push", why not do a pull? IOW, do the query on the > > other server and haul the data from the other: > > > > -- execute this on LINKED_SERVER > > select > > * > > into > > #temp > > from > > [ORIGINAL_SERVER].[Database].dbo.[tableName] > > > > > > -- > > Tom > > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > SQL Server MVP > > Toronto, ON Canada > > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > > > > "rodchar" wrote in message > > > > it creates on my local tempdb instead of the remote one. > > > > "TheSQLGuru" wrote: > > > > > Try it and see! It is a very easy thing to test. > > > > > > -- > > > Kevin G. Boles > > > Indicium Resources, Inc. > > > SQL Server MVP > > > kgboles a earthlink dt net > > > > > > > > > "rodchar" wrote in message > > > > > > > hey all, > > > > > > > > can someone please tell me if the following is possible > > > > > > > > select * > > > > into [LINKED_SERVER].#tempTable > > > > FROM [LINKED_SERVER].[Database].dbo.[tableName] > > > > > > > > thanks, > > > > rodchar > > > > > > > > > > > > > > > >> Stay informed about: temp table in linked server

Tom Moreau

External

Since: Apr 21, 2004
Posts: 502

(Msg. 8) Posted: Fri Apr 17, 2009 9:33 am Post subject: Re: temp table in linked server [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Let's say that the server on which you want to create the temp table is SERVERA and the one on which the data currently resides is SERVERB. Have an admin on SERVERA create a linked server to SERVERB. Then connect to SERVERA and run the following query: select * into #temp from SERVERB.MyDB.dbo.MyTable -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "rodchar" wrote in message > > -- execute this on LINKED_SERVER > > select > > * > > into > > #temp > > from > > [ORIGINAL_SERVER].[Database].dbo.[tableName] Ok, so how would i execute this on a linked server? Do i use EXEC() AT or Select * FROM OPENQUERY? Would either of these work? -rod "Tom Moreau" wrote: > You can still create temporary objects where you have read-only access. > An > admin will have to set up the linked server for you from that server back > to > the original server. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "rodchar" wrote in message > > the thing is the remote server is pretty much off limits i can only do a > link > server to it with read-only access, is that going to be a problem > > "Tom Moreau" wrote: > > > Instead of doing a "push", why not do a pull? IOW, do the query on the > > other server and haul the data from the other: > > > > -- execute this on LINKED_SERVER > > select > > * > > into > > #temp > > from > > [ORIGINAL_SERVER].[Database].dbo.[tableName] > > > > > > -- > > Tom > > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > SQL Server MVP > > Toronto, ON Canada > > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > > > > "rodchar" wrote in message > > > > it creates on my local tempdb instead of the remote one. > > > > "TheSQLGuru" wrote: > > > > > Try it and see! It is a very easy thing to test. > > > > > > -- > > > Kevin G. Boles > > > Indicium Resources, Inc. > > > SQL Server MVP > > > kgboles a earthlink dt net > > > > > > > > > "rodchar" wrote in message > > > > > > > hey all, > > > > > > > > can someone please tell me if the following is possible > > > > > > > > select * > > > > into [LINKED_SERVER].#tempTable > > > > FROM [LINKED_SERVER].[Database].dbo.[tableName] > > > > > > > > thanks, > > > > rodchar > > > > > > > > > > > > > > > >> Stay informed about: temp table in linked server

rodchar

External

Since: Jan 11, 2008
Posts: 105

(Msg. 9) Posted: Fri Apr 17, 2009 10:58 am Post subject: Re: temp table in linked server [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

thanks for all the help, rod. "Tom Moreau" wrote: > Let's say that the server on which you want to create the temp table is > SERVERA and the one on which the data currently resides is SERVERB. Have an > admin on SERVERA create a linked server to SERVERB. Then connect to SERVERA > and run the following query: > > select > * > into > #temp > from > SERVERB.MyDB.dbo.MyTable > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "rodchar" wrote in message > > > > -- execute this on LINKED_SERVER > > > select > > > * > > > into > > > #temp > > > from > > > [ORIGINAL_SERVER].[Database].dbo.[tableName] > > Ok, so how would i execute this on a linked server? Do i use EXEC() AT or > Select * FROM OPENQUERY? Would either of these work? > > -rod > > > > > "Tom Moreau" wrote: > > > You can still create temporary objects where you have read-only access. > > An > > admin will have to set up the linked server for you from that server back > > to > > the original server. > > > > -- > > Tom > > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > SQL Server MVP > > Toronto, ON Canada > > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > > > > "rodchar" wrote in message > > > > the thing is the remote server is pretty much off limits i can only do a > > link > > server to it with read-only access, is that going to be a problem > > > > "Tom Moreau" wrote: > > > > > Instead of doing a "push", why not do a pull? IOW, do the query on the > > > other server and haul the data from the other: > > > > > > -- execute this on LINKED_SERVER > > > select > > > * > > > into > > > #temp > > > from > > > [ORIGINAL_SERVER].[Database].dbo.[tableName] > > > > > > > > > -- > > > Tom > > > > > > ---------------------------------------------------- > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > > SQL Server MVP > > > Toronto, ON Canada > > > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > > > > > > > "rodchar" wrote in message > > > > > > it creates on my local tempdb instead of the remote one. > > > > > > "TheSQLGuru" wrote: > > > > > > > Try it and see! It is a very easy thing to test. > > > > > > > > -- > > > > Kevin G. Boles > > > > Indicium Resources, Inc. > > > > SQL Server MVP > > > > kgboles a earthlink dt net > > > > > > > > > > > > "rodchar" wrote in message > > > > > > > > > hey all, > > > > > > > > > > can someone please tell me if the following is possible > > > > > > > > > > select * > > > > > into [LINKED_SERVER].#tempTable > > > > > FROM [LINKED_SERVER].[Database].dbo.[tableName] > > > > > > > > > > thanks, > > > > > rodchar > > > > > > > > > > > > > > > > > > > > > > > > >> Stay informed about: temp table in linked server

Display posts from previous:

Related Topics:

SQL 2005 view based on linked server table can't be manual.. - Hi everyone! I have two SQL Server 2005, Server_A and Server_B. On Server_A, I created a linked server (Server_B). On Server_A, I created a view that looks like: CREATE VIEW [dbo].[vwTest] AS SELECT LastName, FirstName FROM ..

inserting efficiently into a temp table/table variable - I am a newbie to Sql Server programming, and I was hoping that someone might be able to point me in the proper direction. I am trying to write a C function that takes an array of strings and insert them into either a temporary table or a table variable....

Table variable performance vs. temp table - Hi, I've got an identical query, one with temp tables and one with table variables that results in two completely different execution times. The plans look virtually the same but execution time is around 2 minutes with the variables and less than a..

Temp Table - I have created a temp table using the following query: CREATE TABLE #temptbl1 (Col1 varchar(50), Col2 int) When I run the following query: USE tempdb IF EXISTS (SELECT * FROM sysobjects WHERE NAME = '#temptbl1') PRINT 'TABLE EXISTS' ELSE PRINT 'TABL...

Temp Table's - Hello All, I use several local temp tables in an application. These tables store information that is built from some text files by the user. This data cannot be mixed up by another user logging in at the same time. Is there a chance that another user can...

	Database Help (Home) -> Programming	All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum