Geek asks and answers

Creating stored procedures that can work with different tables

I need to use the same stored procedures for many tables that have the same structure in my database. This is data downloaded from clients with one table / client, and the data needs to be calculated / checked before uploading to our DataWarehouse.

So far these are the options and problems that I have found, and I am looking for the best template / approach.

  • Create a view that points to the table I want to process, SP then talk to this point of view. This works well (especially after I developed how to create automatic views based on their columns). But the view can only be used with one table at a time, forcing the system to communicate with one client at a time.

  • Use dynamic sql in each SP - makes SP much more difficult to read / debug and for these reasons it was ruled out

  • Create a partitioned view in all the tables, and then use the parameterized table function to return just the data that interests us - oh, but then I can not update the data because the function returns a table that can be used only for selection

  • Use dynamic sql inside a function (cannot be done) to create a view (which cannot be done either) .... give up
  • Inside the SP, a temporary table is created using the target table using dynamic sql, but then the temporary table exists only in a session that runs dynamic sql, not the "parent", the session in which SP is executed ... refuse
  • ,    SQL,    5, SP    .    .
  • 1   , SP    commit -   ,      
  • ...    /SQL
  • .Net -    ,   tsql /

, , , - , , .

+3
5

, , , , , SSIS

- :

-, :

- script . , , , , - , . SQL script , .

script , , .. , script . .

. . - . , , sp, .

- script. , , ExecuteSQLTask sql . , , , .

Ok. .

:

CREATE TABLE [dbo].[t_n](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [name] [varchar](50) NOT NULL,
  [start] [datetime] NULL,
  CONSTRAINT [PK_t_n] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]

t_n (t_1, t_2, t_3 ..).

:

CREATE PROCEDURE SpProcessT_n 
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM [t1]; 
END
GO

Sql script,

    SET NOCOUNT ON;
    SELECT * FROM [$table_name];

.sql , POC.

SSIS :

Basic SSIS Package

, : enter image description here

, _table_name_ enter image description here

script, , _table_name_ , SqlExec /:

enter image description here

:

    public void Main()
    {
        String Table_Name = Dts.Variables["table_name"].Value.ToString();
        String SqlScript;
        Regex reg = new Regex(@"\$table_name", RegexOptions.Compiled);
        using (var f = File.OpenText(@"c:\sqlscript.sql")) {
            SqlScript = f.ReadToEnd();
            f.Close();
        }
        SqlScript = reg.Replace(SqlScript, Table_Name);
        Dts.Variables["SqlExec"].Value = SqlScript;
        Dts.TaskResult = (int)ScriptResults.Success;
    }

, Dts Variable SqlExec sql script, . ExecuteSqlTask:

enter image description here

MSSQL 2008, script, .

, !

+11

, SSIS, 150+ . "", - , .

. , 30 , , , , : , . , .

+2

, ?

SELECT 'TableName', t.* FROM TableName t
UNION ALL 
SELECT 'TableName2', t.* FROM TableName2 t

, SQL ( SQL, CRUD , )

+1

SQL. , , ETL.

, , . "", ETL "" . .

SSIS ( , SQL Server, ), ETL...

E (xtract): . , foreach , ( , , ).

T (ransform): /, , ...

L (oad): .

, .    1. .    2. t-sql. , tsql .

, , , Business Intelligence. , , - , .

+1

, - SQL sp ( 2), .

Your goal is to create generic, multi-tasking SQL. I do not understand how you intend to achieve this without sacrificing some efficiency and readability.

0
source

More articles:


All Articles