, good, meet a problem now, want to ask, under the condition of now:
Under the group have more branches, but need to look at the whole group all the inventory data of a branch, now have this table:

 SELECT * FROM [erp_company_a (subsidiary of a database library name)], [dbo]. [inventory data table] 
UNION ALL 
SELECT * FROM [erp_company_b database library name (unit b)]. [dbo]. [inventory data table] 
UNION ALL 
SELECT * FROM [erp_company_c (subsidiary c database library name)], [dbo]. [inventory data table] 

Now after this string of statement execution, is the ability to realize one-time view all subsidiary inventory data, and can realize this demand,
But now encountered confusion is: under the group have more than 10 branches, if according to the code written above, each with the code I will repeat
About 10 times, is there any short code can achieve the same effect?

CodePudding user response:

Packaging for the stored procedure, in the process of storage after dynamic generating SQL execution.

CodePudding user response:

Special maintenance table, each subsidiary of the library, in order to query, dynamic loop to generate and execute the statement,

CodePudding user response:

# 1, # 2, the combination of
In addition, the Suggestions to make the view, if you have any increase or decrease, subsidiary to use stored procedures to regenerate the view at a time,

In this way, everyone access to view only, do not need to write so much code, also need not worry about how many subsidiaries,

The view in the form of a similar:

 IF OBJECT_ID (' _ALL view_ inventory data table) IS NOT NULL 
DROP the VIEW view_ inventory data table _ALL 
GO 
The CREATE VIEW view_ inventory data table _ALL 
AS 
SELECT * FROM [erp_company_a (subsidiary of a database library name)], [dbo]. [inventory data table] 
UNION ALL 
SELECT * FROM [erp_company_b database library name (unit b)]. [dbo]. [inventory data table] 
UNION ALL 
SELECT * FROM [erp_company_c (subsidiary c database library name)], [dbo]. [inventory data table] 
GO