SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 create table from query output
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

josef
Starting Member

USA
15 Posts

Posted - 10/25/2013 :  16:47:31  Show Profile  Reply with Quote
I have the following sql query in vb.net and ms access , how do I create a table from the query result ?
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

I have tried but it does not work
create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

any help would be appreciated

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 10/25/2013 :  17:02:07  Show Profile  Reply with Quote
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result INTO YourNewTableName FROM table1
Go to Top of Page

josef
Starting Member

USA
15 Posts

Posted - 10/26/2013 :  19:08:19  Show Profile  Reply with Quote
It worked fine. Can you please tell me how do i replace the table each time i run the query.
Thank you
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 10/27/2013 :  00:13:24  Show Profile  Reply with Quote
If you want to remove the table and recreate it each time, add a "DROP Table YourNewTableName" before the select statement.
IF OBJECT_ID(YourNewTableName) IS NOT NULL DROP TABLE YourNewTableName;
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result INTO YourNewTableName FROM table1

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/27/2013 :  01:53:45  Show Profile  Reply with Quote
quote:
Originally posted by James K

If you want to remove the table and recreate it each time, add a "DROP Table YourNewTableName" before the select statement.
IF OBJECT_ID('YourNewTableName') IS NOT NULL DROP TABLE YourNewTableName;
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result INTO YourNewTableName FROM table1




should be this small tweak to avoid error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

josef
Starting Member

USA
15 Posts

Posted - 11/01/2013 :  19:56:50  Show Profile  Reply with Quote

Please help me with this query
I get an error ' an action query cannot be used as a row source'
Is there any way to fix this
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result INTO YourNeowTableName FROM table1 union all
SELECT 'Table2' AS [Table], SUM(a) - SUM(b) AS Result INTO YourNewTableName FROM table2
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 11/01/2013 :  20:11:25  Show Profile  Visit jezemine's Homepage  Reply with Quote

select * into YourNewTableName 
from 
(
  SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1 union all 
  SELECT 'Table2' AS [Table], SUM(a) - SUM(b) AS Result FROM table2
) x



elsasoft.org
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 11/01/2013 :  20:14:29  Show Profile  Visit jezemine's Homepage  Reply with Quote
btw having a column named [table] is not a very nice design imo. table is a reserved keyword, plus it's simply confusing to name a column "table". How about "TableName" instead?

that is unless you name your table "column" of course. then you can do cool stuff like this and wow your boss:

select [table] from [column]



elsasoft.org
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/01/2013 :  23:42:37  Show Profile  Reply with Quote
Hey Jesse, long time no....read.

Do you rememba?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50298&SearchTerms=I%20personally%20like%20to%20use%20Sql%20Key%20Words

Be One with the Optimizer
TG
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 11/02/2013 :  21:29:52  Show Profile  Visit jezemine's Homepage  Reply with Quote
yes I don't post much anymore. My goal is to get to 3000 before the sun goes nova.


elsasoft.org
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/04/2013 :  11:09:00  Show Profile  Reply with Quote
Worthy goal. And we can all enjoy the light show in honor of that 3000th post - briefly - or eternally - or perhaps not at all. I don't know, you're the physics guy.

Be One with the Optimizer
TG
Go to Top of Page

josef
Starting Member

USA
15 Posts

Posted - 11/06/2013 :  08:00:52  Show Profile  Reply with Quote
Its not worth reading . You better find an apprpiate place to
Post. I want to ask moderatore to remove it
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 11/06/2013 :  08:30:33  Show Profile  Reply with Quote
quote:
Originally posted by josef

Its not worth reading . You better find an apprpiate place to
Post. I want to ask moderatore to remove it

I don't know Josef, I found it well-worth reading, and was chuckling to myself as I was reading it.

I guess it all depends on one's perspective. There are those of us who enjoy messing with SQL, like poking at it, and get a thrill out of making it do strange things etc.

Along those lines, one of the presentations that I enjoyed very much was Rob Volk's "Revenge", here:

http://www.sqlsaturday.com/viewsession.aspx?sat=103&sessionid=7057
http://www.sqlsaturday.com/viewsession.aspx?sat=122&sessionid=6916
Go to Top of Page

josef
Starting Member

USA
15 Posts

Posted - 11/06/2013 :  10:42:02  Show Profile  Reply with Quote
Just a freaking wrong answer for me
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/06/2013 :  10:58:57  Show Profile  Reply with Quote
quote:
Originally posted by josef

Just a freaking wrong answer for me


You actually asked 3 different questions and all were answered quite well (in my opinion). I think folks are still quite willing to respond to any additional questions or question "refinements" you have.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000