Access in Different Database

 Collect Data from different databases. 

-- use gBankerSMSDb

DECLARE @Counter INT , @MaxId INT

Declare @db table(DataBaseID int,DBNAME varchar(50))

Insert into @db

SELECT dbid  DataBaseID,DB_NAME(dbid) DBNAME

FROM

    sys.sysprocesses

WHERE 

    dbid > 0

and DB_NAME(dbid) not in('master','msdb','tempdb','model','distribution')

and left(DB_NAME(dbid),1)<>'R'

group by dbid;

SELECT  @Counter = min(DataBaseID) , @MaxId = max(DataBaseID) 

FROM @db

WHILE(@Counter IS NOT NULL

      AND @Counter <= @MaxId)

BEGIn

Declare @vDataBaseName varchar(60), @MaxMemberId bigint;

select @vDataBaseName=DBNAME from @db where DataBaseID=@Counter

 exec ('insert into MemberPortal([MemberID], [MemberCode], [MemberName], [RefereeName], [PhoneNo], [SmartCard], [OtherID], [NationalID], [OrgID], [UnionCode], [BirthDate], [CreateUser], [CreateDate], [DBID]

)

select [MemberID], [MemberCode],FirstName [MemberName], [RefereeName], [PhoneNo], [SmartCard],OtherIdNo [OtherID], [NationalID], [OrgID], [UnionCode], [BirthDate], [CreateUser], [CreateDate],'+@Counter+' [DBID]

 from '+ @vDataBaseName +'.dbo.member'

 )

 exec ('SELECT'+ @MaxMemberId + '= MAX(MemberId) FROM'+  @vDataBaseName +'.dbo.member')

 IF Exists( SELECT 1 FROM MemberPortalMaxID WHERE OrgName = @vDataBaseName )

 BEGIN

UPDATE MemberPortalMaxID SET MaximumMemberId = @MaxMemberId WHERE OrgName = @vDataBaseName

 END 

 ELSE

 BEGIN

INSERT INTO MemberPortalMaxID 

( [MaximumMemberId]

      ,[OrgName]

      ,[isActive]

      ,[UpdateDate]

  ) VALUES (@MaxMemberId, @vDataBaseName, 1, GETDATE());

 END

SET @Counter  = @Counter  + 1        

END

--select * from DemographicRegion