• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 50
  • Last Modified:

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

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
Erwin Pombett
Asked:
Erwin Pombett
  • 5
  • 4
  • 4
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
try..

select * into ##globaltemptable from temptable

hope it helps...
0
 
Erwin PombettAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Pawan KumarDatabase ExpertCommented:
no you don't have to create it. it will created on the fly by SQL server..
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Erwin PombettAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Erwin PombettAuthor Commented:
@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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Erwin PombettAuthor Commented:
@Vitor:
indeed i need the data....i'll keep them in a normal table.

thank you for this  !
0
 
Pawan KumarDatabase ExpertCommented:
Yes please proceed with the physical table so that you can have data and all the people can access with issues.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now