Temporary Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Temporary Table

Author

Topic

confuzed04
Starting Member

39 Posts

Posted - 2007-06-20 : 14:37:09

How would someone set up a temporary table from a database in order to then run a query against it to get more accurate data??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-20 : 14:57:55

How does using a temporary table give you more accurate data?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

confuzed04
Starting Member

39 Posts

Posted - 2007-06-20 : 15:04:19

It can help me narrow things down, I hope. I am not sure how to create a temporary table though. Please advise

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-20 : 15:09:56

It's the same way as a normal table, but with a #.

CREATE TABLE #tableName (Column1 int, ...)

Tara Kizer
http://weblogs.sqlteam.com/tarad/

confuzed04
Starting Member

39 Posts

Posted - 2007-06-20 : 15:18:16

So, if I had a database and wanted to pull only certain data from it that I could then run a query against, I could just do a create table? So if my Database was named CUSTOMERS and I just wanted to get the address info, I could just do CREATE TABLE # CUSTOMERS (Address info), and that will give me a table that I could then run queries off??

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-06-20 : 15:21:55

Even easier. You can SELECT <column1>, <column2> INTO #temp FROM customers WHERE <column1> = <your_criteria>, and then do a SELECT <whatever> FROM #temp WHERE <whatever_condition_is_met>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-20 : 15:23:39

Create table simply creates the object. If you want data in it, then you need to insert data into it. You can also do this instead of the create/insert:

SELECT ...
INTO #temp
FROM YourTable
WHERE...

That statement creates the table and moves the data that you want to it. Sometimes you can't use this method though, such as when the data is coming from a stored procedure.

Why can't you just query your CUSTOMERS table directly?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

confuzed04
Starting Member

39 Posts

Posted - 2007-06-20 : 15:30:40

Do you know how to do this with a WITH statements

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 15:37:31

Oh... Sounds more and more like an assignment.
Read about Common Table Expressions in Books Online.

Peter Larsson
Helsingborg, Sweden

cardgunner

326 Posts

Posted - 2007-06-22 : 09:46:33

Well I'm not working on a home work assignment.

I created this


SELECT *
INTO #QRY
FROM QRY9962

SELECT BUYID
FROM #QRY

and I get an error saying that #QRY already exists.

So if I create this temp table and notice the info is wrong i need to correct my logic and create a NEW table such as #QRY2?

In BOL they have ## as well. What is the difference.

And these are temp right? I'm not polluting my companies db with my corrections?

Card Gunner

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-22 : 10:00:40

1 You need to drop the table #QRY1 and then recreate it
2 You didnt explain what you are actually doing with the temp table
3 BOL will explain you the difference if you read it fully

Madhivanan

Failing to plan is Planning to fail

cardgunner

326 Posts

Posted - 2007-06-22 : 10:22:12

1) This is my first efforts at creating a temp table. How do I drop it? I will search the forum for my answer or BOL. If I don't find it I may be asking.
2) I have further detail a=on my underlying pronblem at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85403
they told me in that forum I needed to create a temp table which I didn't know how to do which lead me to this thread.
3) My fault, What is the difference between global and local?

You being a master of all answers I just noted my profile says "Yak Posting Veteran". How do "I" get distinguished with this? That name could be missleading that I may know more then I do

Card Gunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources