reusing temp table across different procs

Is there any way I can put the following consolidated statement into a proc
or function for reuse'
I have to use dynamic exec statement because filename,extension, or linked
server could change
<consolidated>
create table #t1(
c1 int,
c2 int,
c3 int,
c4 int,
c5 int
)
insert into #t1
EXEC('SELECT
c1,
c2,
c3,
c4,
c5
FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension + ']
')
select * from #t1
</consolidated>
And then I could call this consolidate code in different procs and do some
thing like this
inside proc1...
insert into dbo.mytbl1(c2,c3)
select c2,c3 from <consolidated #t1>
inside proc2...
insert into dbo.mytbl2(c4,c5)
select c4,c5 from <consolidated #t1>
Please let me know if this needs more clarification and TIA..what about global temp tables? prefixed with a double pound sign
http://sqlservercode.blogspot.com/|||If you create the #temp table in the "parent" proc, then execute other procs
from the parent, you can.
USE model;
GO
CREATE PROCEDURE dbo.foo2
AS
BEGIN
SET NOCOUNT ON;
INSERT #foo SELECT 1 UNION ALL SELECT 2;
END;
GO
CREATE PROCEDURE dbo.foo1
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #foo(a INT);
EXEC dbo.foo2;
SELECT * FROM #foo;
DROP TABLE #foo;
END;
GO
EXEC dbo.foo1;
GO
DROP PROCEDURE dbo.foo1, dbo.foo2;
GO
I've read your requirements and <consolidated> but I still don't quite
understand the actual goal and whether the above meets your requirements.
If you need to have independent stored procedures called separately and
still have access to the temp table, I think you are up the wrong tree.
http://www.sommarskog.se/share_data.html
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||Sorry, I missed the cross-server bit.
I don't think that will be possible because the #temp table will probably
belong to a different session. Again, I don't think you can use #temp
tables for this.
While I'll admit I haven't tried this extensively, I don't think global temp
tables will help either, because the session won't necessarily be maintained
across servers, and the calling proc's session dictates the life of the
global temp table.
A
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||I would like to avoid global temp tables
"SQL" wrote:

> what about global temp tables? prefixed with a double pound sign
> http://sqlservercode.blogspot.com/
>|||Aaron,
I would like to import data from a csv file into separate tables. To pull
rows into a table format in query analyzer, I am using linked server.
Temp table gives me staging table functionality. I would like to pull all
the data into a central location and then reuse that central location in
different procs to populate or process that data.
Please let me know if this clarifies the overall approach and thanks again
in advance.
"Aaron Bertrand [SQL Server MVP]" wrote:

> If you create the #temp table in the "parent" proc, then execute other pro
cs
> from the parent, you can.
> USE model;
> GO
> CREATE PROCEDURE dbo.foo2
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT #foo SELECT 1 UNION ALL SELECT 2;
> END;
> GO
> CREATE PROCEDURE dbo.foo1
> AS
> BEGIN
> SET NOCOUNT ON;
> CREATE TABLE #foo(a INT);
> EXEC dbo.foo2;
> SELECT * FROM #foo;
> DROP TABLE #foo;
> END;
> GO
> EXEC dbo.foo1;
> GO
> DROP PROCEDURE dbo.foo1, dbo.foo2;
> GO
>
> I've read your requirements and <consolidated> but I still don't quite
> understand the actual goal and whether the above meets your requirements.
> If you need to have independent stored procedures called separately and
> still have access to the temp table, I think you are up the wrong tree.
> http://www.sommarskog.se/share_data.html
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>
>|||I hate procs that reference temporary tables created by other procs. 8-[
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKthgGKEGHA.3920@.tk2msftngp13.phx.gbl...
> Sorry, I missed the cross-server bit.
> I don't think that will be possible because the #temp table will probably
> belong to a different session. Again, I don't think you can use #temp
> tables for this.
> While I'll admit I haven't tried this extensively, I don't think global
> temp tables will help either, because the session won't necessarily be
> maintained across servers, and the calling proc's session dictates the
> life of the global temp table.
> A
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>|||i a different proc references a "temp" table, it's not really a temp
table.
but for this problem you're having, you can use tempdb. just drop and
create table as you please. for cross server, you can link the servers
together or use opendatasource. try not to do too much cross server
queries.|||>I hate procs that reference temporary tables created by other procs. 8-[
Ok. They can be useful for others, in spite of your hatred for them.
http://www.aspfaq.com/2248
Yes, full of bad practices, but useful nonetheless
PS I hate slow drivers in the left lane, but they still exist, and I still
have to deal with them on my commute.
A|||> but for this problem you're having, you can use tempdb. just drop and
> create table as you please.
Except if the proc is called by two sessions at the same time, they will
both try CREATE TABLE dbo.MyTable, oops, one of them wins, one of them
loses.
A