Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

in tsql : how can i copy a temp table #myTable to a global table ##myTable (same columns)  ?

Posted on 2016-11-15
13
Medium Priority
?
38 Views
Last Modified: 2016-11-15
Hello experts,

i create two temp tables in a storeproc.
at the end of the SP, i'd like to copy one of my temp tables to the global tables.

1.
Is it possible to do this easily ?  like changing a pointer ?  
if yes how ?

2.
do i have to create the table then loop over it to insert all data ?  
if this is the case i'd appreciate the sql to do it.
thank you in advance for your help.


shiro
0
Comment
Question by:toshi_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41887984
Not really sure how your SP looks like since you didn't post it.
I'll assume that "copy one of my temp tables to the global tables" is a data migration process so you can use something like:
INSERT INTO GlobalTableName (Column1, Column2, ..., ColumnN)
SELECT Col1, Col2, ..., ColN
FROM #tempTableName

Open in new window

0
 
LVL 32

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41887991
try..

select * into ##globaltemptable from temptable

hope it helps...
0
 

Author Comment

by:toshi_
ID: 41888013
Hello,

thank you for your answers.

@Pawan:  
do i need to create the ##globaltemptable first ?
is there a possibility to create the ##globaltemptable from #myTemptable  ?

thank you
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41888020
no you don't have to create it. it will created on the fly by SQL server..
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41888021
shiro, can you share with us what's your understanding of a "global table"? You might misleading us if you don't talk all the same language.
0
 

Author Comment

by:toshi_
ID: 41888026
Hello again,

@pawan : wonderful !
I'll try your proposition.

@Vitor: correct me if i'm wrong :
i call temp table a table that is prefixed with "#" and will last the session.
i call a global table a table that is prefixed with "##" and will last as long as we dont restart the server.

shiro
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41888037
Ok, that's actually a Global Temporary Table. Is still a temporary table but the scope is that all users will have access to it.
Just trying to understand your solution, why are you inserting in temporary table and then copy the data to a global temporary table? Why don't you work immediately with a Global Temporary Table?
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41888055
Great ! Thank You...

your understanding is also correct. global temp are visible to all and they are removed when all the connections  tht have referenced them have closed.

# - local temp table
## - global temp table

hope it helps
0
 

Author Comment

by:toshi_
ID: 41888064
@Vitor:
I recover data - once a week - with a query from db B to insert in a table in my db A.
the first time i'll be inserting everything. On next run only the records that have changes...

i need to keep the status of the last changes in order to compare new status of records with them.

does it make sense ?
shiro
0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41888071
On next run only the records that have changes...
So isn't for other users access the data? If not then a global temporary table won't make sense. Also, don't forget that even a global temporary table will be dropped if it's created in the scope of a stored procedure and your process exits the SP. If you call again the SP all the data has been gone.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41888118
Hi Shiro,

Your requirement is very different,  you need incremental loading, only new records will inserted and the changed records will be updated.
You can use datetime column for that or change data capture or may be some other technique.

basically it is an entire new question. I think open a new thread and clearly state your requirement.
0
 

Author Comment

by:toshi_
ID: 41888127
@Vitor:
indeed i need the data....i'll keep them in a normal table.

thank you for this  !
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41888133
Yes please proceed with the physical table so that you can have data and all the people can access with issues.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question