how does one save a table to diff database

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

how does one save a table to diff database

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-09-17 : 22:15:49

Hoping someone can assist. I have a very basic program which reads data from one specific database (i.e. DATA_db).
I need to save the results to a table called Temp_bal within a different database (RESULTS_db)on the same server. I am not sure exactly
how to do this. Any guidance is greatly appreciated. Thank you.



USE DATA_db;
GO

SELECT  account ,
          SUM(CASE a.L4_DESC WHEN 'Liability' THEN 
                    b.balance * -1  ELSE 0 END)  liability,
          SUM(CASE a.L4_DESC WHEN 'asset' THEN 
                    b.balance   ELSE 0 END)  asset

into Temp_bal

FROM dbo.MonthEndBalances b
group by account
LEFT OUTER JOIN dbo.ACCOUNT a 
ON b.account = a.ACCOUNT
group by b.account

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-18 : 01:29:15

[code]
USE Temp_bal;
GO

SELECT account ,
SUM(CASE a.L4_DESC WHEN 'Liability' THEN
b.balance * -1 ELSE 0 END) liability,
SUM(CASE a.L4_DESC WHEN 'asset' THEN
b.balance ELSE 0 END) asset

into TableNameHere

FROM DATA_db.dbo.MonthEndBalances b
LEFT OUTER JOIN DATA_db.dbo.ACCOUNT a
ON b.account = a.ACCOUNT
group by b.account
[/code]

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-09-18 : 01:35:25

Thank you for your reply, but I am still a bit unclear. I actually want to create a table called Temp_bal
as shown in my original post ( 'into Temp_bal') but into a different database on the same server called
RESULTS_db.

I am just unclear why you would do a USE statement on the file I am trying to create??

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-18 : 02:10:10

I misunderstood. This looks like what you're after. Basically, execute in the target db and select from the source db.


USE RESULTS_db;
GO

SELECT  account ,
          SUM(CASE a.L4_DESC WHEN 'Liability' THEN 
                    b.balance * -1  ELSE 0 END)  liability,
          SUM(CASE a.L4_DESC WHEN 'asset' THEN 
                    b.balance   ELSE 0 END)  asset

into Temp_bal

FROM DATA_db.dbo.MonthEndBalances b
group by account
LEFT OUTER JOIN DATA_db.dbo.ACCOUNT a 
ON b.account = a.ACCOUNT
group by b.account

Edit: one too many group by clauses

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-09-18 : 02:40:08

Thank you this worked perfectly.

Just 1 more question. Is there a way in the statement below I can assign it an alias for creating a shorter name?

USE DATA_db;
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-18 : 22:52:54

Don't think you can alias a database name. Can't create a synonym for it either.

So, as far as I know, nothing you can do in this case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources