Prev: Trigger error
Next: Check if a row is empty
From: Sammy on 16 Jun 2010 21:53
I have 26 databases database, Database,......Database.

I have a view as below that I want to modify so that it can get data from
all 26 databases. I would appreciate some help on this.

create view Unpostedallcompany as
select
cpnyid
Periodpost, jrnltype, trantype,origcpnyid,acct,sub, perent,refnbr,
trandate, trandesc,dramt, cramt from gltran order by cpnyid


Thanks

sunny

From: Dan Guzman on 16 Jun 2010 22:13
> I have a view as below that I want to modify so that it can get data from
> all 26 databases. I would appreciate some help on this.
>
> create view Unpostedallcompany as
> select
> cpnyid
> Periodpost, jrnltype, trantype,origcpnyid,acct,sub, perent,refnbr,
> trandate, trandesc,dramt, cramt from gltran order by cpnyid

Assuming all database are on the same server, you can use UNION ALL:

CREATE VIEW dbo.Unpostedallcompany
AS
SELECT
cpnyid
,Periodpost
,jrnltype
,trantype
,origcpnyid
,acct,sub
,perent,refnbr
,trandate
,trandesc
,dramt
,cramt
FROM Database1.dbo.gltran
UNION ALL
SELECT
cpnyid
,Periodpost
,jrnltype
,trantype
,origcpnyid
,acct,sub
,perent,refnbr
,trandate
,trandesc
,dramt
,cramt
FROM Database2.dbo.gltran
....
ORDER BY cpnyid;
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Sammy" <s_commar(a)hotmail.com> wrote in message
news:D9C973AB-BCAC-4ECA-91BB-6EF6C7781FA1(a)microsoft.com...
> I have 26 databases database, Database,......Database.
>
> I have a view as below that I want to modify so that it can get data from
> all 26 databases. I would appreciate some help on this.
>
> create view Unpostedallcompany as
> select
> cpnyid
> Periodpost, jrnltype, trantype,origcpnyid,acct,sub, perent,refnbr,
> trandate, trandesc,dramt, cramt from gltran order by cpnyid
>
>
> Thanks
>
> sunny

 | 
Pages: 1
Prev: Trigger error
Next: Check if a row is empty