create table from query output

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

create table from query output

Author

Topic

josef
Starting Member

15 Posts

Posted - 2013-10-25 : 16:47:31

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-25 : 17:02:07

[code]SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result INTO YourNewTableName FROM table1
[/code]

josef
Starting Member

15 Posts

Posted - 2013-10-26 : 19:08:19

It worked fine. Can you please tell me how do i replace the table each time i run the query.
Thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-27 : 00:13:24

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-27 : 01:53:45

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

josef
Starting Member

15 Posts

Posted - 2013-11-01 : 19:56:50

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2013-11-01 : 20:11:25

[code]
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
[/code]

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2013-11-01 : 20:14:29

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-01 : 23:42:37

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2013-11-02 : 21:29:52

yes I don't post much anymore. My goal is to get to 3000 before the sun goes nova.

elsasoft.org

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-04 : 11:09:00

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

josef
Starting Member

15 Posts

Posted - 2013-11-06 : 08:00:52

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-06 : 08:30:33

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

josef
Starting Member

15 Posts

Posted - 2013-11-06 : 10:42:02

Just a freaking wrong answer for me

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-06 : 10:58:57

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources