November 29, 2008 at 9:28 am
Hi
I am used to quickly creating recordsets for this sort of thing and have no idea how to do that (or equivelent in VS.Net)
so I am thinking of creating a temp table to store the results of a query. this is what I tried but obviously i need to creat a new temp table first ? How do you do that ?
INSERT INTO newTempDS
select account.nameof as account,
sum (entry.credit-entry.debit) as total from entry,account
where entry.account=account.nameof
and entry.Taxyear < 2008 and entry.Cleared = 1
group by account.nameof
thanks
Mike
November 29, 2008 at 9:35 am
There are 2 ways to create a temp table.
Create Table #temp
(
colA int,
colB varchar(10)
)
Or
Select
colA,
colB
Into
#temp
From
tableA
The "#" signifies that you are creating a temporary table.
Can explain in more detail what you are trying to accomplish? That way someone may be able to provide you with some better instruction.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 29, 2008 at 9:48 am
Suggest reading Books On Line (BOL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1e068443-b9ea-486a-804f-ce7b6e048e8b.htm
Scroll half-way down the help file page and find
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
November 29, 2008 at 9:51 am
Thanks
I tried this in management studio and got the error 'Incorrect syntax near the keyword 'Into'.
select account.nameof as account,
sum (entry.credit-entry.debit) as total from entry,account
where entry.account=account.nameof
and entry.Taxyear < 2008 and entry.Cleared = 1
group by account.nameof
Into #temp
From entry, account
Basically the select part of the sql works ok but i just need the output into something temporary
the sql is summing credits and debits from the entry table and grouping them by account names to give a year brought forward balance - its been working for years but i am trying to upgrade to sql server lol
November 29, 2008 at 10:01 am
The INTO needs to go immediately after the select list and before the from, so your code would need to be this:
select
account.nameof as account,
sum (entry.credit-entry.debit) as total
Into
#temp
from
entry,account
where
entry.account = account.nameof and
entry.Taxyear < 2008 and
entry.Cleared = 1
group by
account.nameof
I'd also suggest you start to use the ANSI standard JOIN syntax as well, which would make the query look like this:
select
account.nameof as account,
sum (entry.credit-entry.debit) as total
Into
#temp
from
entry INNER JOIN
account ON
entry.account = account.nameof
where
entry.Taxyear < 2008 and
entry.Cleared = 1
group by
account.nameof
Are you using the temporary table later or just returning the data from it?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 29, 2008 at 10:06 am
Thankyou thats great!:D
I am using the data in the same procedure - will not be kept long
Mike
November 29, 2008 at 11:13 am
Hi again
one problem is that when i import the new table into a dataset.table it has 8 columns which are the same as the "Account" table
where as my original returned two columns named account and total?
I cant see the temp table in management studio?
mike
November 29, 2008 at 11:21 am
What question are you asking?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 29, 2008 at 12:06 pm
Have you run Jack Corbett's code in SSMS?
select
account.nameof as account,
sum (entry.credit-entry.debit) as total
Into
#temp
from
entry INNER JOIN
account ON
entry.account = account.nameof
where
entry.Taxyear < 2008 and
entry.Cleared = 1
group by
account.nameof
If you have not, please do so, remain in SSMS and then execute the the following single additional line of code:
SELECT * FROM #temp
and post what data is returned so that you can get additional assistance.
November 30, 2008 at 3:23 am
SELECT * FROM#temp1 (yes why didnt i think of that? LOL )
Ah that returns what i am expecting - so error is somewhere in my vb code
thanks
Mike
November 30, 2008 at 3:52 am
mike (11/29/2008)
I cant see the temp table in management studio?
Temp tables are local to the connection that creates them, they cannot be affected by anyone else and they are automatically dropped when the connection that created them is closed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply