CODEWITHSUNDEEP
sqlsql-servervb.netwinformsvisual-studio-2013
Chester John
Chester John

Reputation: 85

Get data from other databases

Help I am in a situation where I needed to get data from previous year's database. I have this databases:

Manila2016
Manila2015
Manila2014
Manila2013
Manila2012
California2016
California2015
California2014

We have a centralized system made in VB.net where we record all business and payroll transactions in all branches.

We have a table where we record the release of the employees' ATMs. The problem is, if an employee's ATM was released in 2015(Manila2015), the record stays in the Manila2015 database, the same process in previous years.

What we are using now is the Manila2016 database, I need to check if an employee already got his ATM in Manila2015 or in Manila2014, in Manila2013, and so on.

I can just join all the databases but as I have said, the system is centralized, and uses the database depending on the branch you are assigned. The California branch only have 3 databases so I guess I need to loop in all the databases based on branch first before I start searching the employees ATM data.

This is what the structure of the table looks like:

RecordNo - int
EmployeeID - nvarchar
Name - nvarchar
Position -nvarchar
ATM -bit
ATMreleasedDate -smalldatetime
Status - nvarchar

This is what my code looks like in VB.net where it only checks if an employee's ATM was already released in the current year.

Dim Year as string = '2016'
Dim Branch as string = 'Manila'
Dim EmployeeID as string = '10087654321-MNL'

sql = <string>    
SELECT
EmployeeID,
Name,
Position,
CASE When ATM IS NULL THEN 'NO' ELSE 'YES' END [ATM], 
ATMrelasedDate,
Status
FROM  <%= Branch %><%= Year %> WHERE EmployeeID = '<%= EmployeeID %>'
<string>
ExecuteSQLQuery(sql)

Upvotes: 2

Views: 83

Answers (2)

smoore4
smoore4

Reputation: 4866

I agree with @Pekka. You should just create a UNION view. That does not affect the existing tables.

    CREATE VIEW vwAllBranches AS

    SELECT "Manila2016" AS Branch, RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status  
    FROM Manila2016
    UNION
    SELECT "Manila2015" AS Branch, RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status  
    FROM Manila2015
    UNION
    SELECT "Manila2014" AS Branch, RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status  
    FROM Manila2014
    UNION ALL
    SELECT "Manila2013" AS Branch, RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status  
    FROM Manila2013
    UNION
    SELECT "Manila2012" AS Branch, RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status  
    FROM Manila2012
    UNION
    SELECT "California2016" AS Branch, RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status  
    FROM California2016
    UNION ALL
    SELECT "California2015" AS Branch, RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status  
    FROM California2015
    UNION
    SELECT "California2014" AS Branch, RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status  
    FROM California2014

And then you can just do a SELECT * FROM vwAllBranches to get data. Couple of things though...

1) You should consider creating an index on the view.

2) You should use a stored procedure or parameterized query to limit SQL Injection.

Upvotes: 1

Pekka
Pekka

Reputation: 3654

Create a UNION view the references each of the tables that you require. Add a static column(s) into each element of the view containing the Branch and Year. This view will need to be updated yearly (or you can create the tables in advance).

In practice a better solution would be to have a single table with the Branch and Year as separate columns, and then introduce views for each branch is this remains an essential part of the design.

So, change: RecordNo, EmployeeID, Name, Position, ATM, ATMreleasedDate, Status to: Branch, Year, RecordNo, ....

In SQL it is almost always easier to place data into columns than into the table names.

Upvotes: 0

Related Questions