-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathUpdateDataFromTable.dsql
87 lines (70 loc) · 5.94 KB
/
UpdateDataFromTable.dsql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
CREATE PROC [dbo].[UpdateDataFromTable] @Source_Table_Name [VARCHAR](200),@Main_Table_Name [VARCHAR](200),@IdentifyingColumns [VARCHAR](8000),@Filter [VARCHAR](8000) AS
/*
IF OBJECT_ID ('dbo.UpdateDataFromTable', 'P') IS NOT NULL DROP PROCEDURE dbo.UpdateDataFromTable
GO
###################################################################################################################
Example:
-------------------------------------------------------------------------------------------------------------------
DECLARE @IdentifyingColumns VARCHAR(400) = '[CustomerID]', @Source_Table_Name VARCHAR(200) = 'dbo.Customers_NEW', @Main_Table_Name VARCHAR(200) = 'dbo.Customers', @Filter VARCHAR(4000) = 'UPDATEDATE >= ''20150101'''
EXEC dbo.UpdateDataFromTable @Source_Table_Name, @Main_Table_Name, @IdentifyingColumns, @Filter
===================================================================================================================
Proc Description:
-------------------------------------------------------------------------------------------------------------------
This proc is used to move data from a stage table to a Production table. Mostly useful for Incremental load for small non-partitioned tables.
With this proc crating an Incremental load is easy - Just get all new and changed data to a stage table and call this proc.
@Main_Table_Name - Production Table name (with Schema) that should get all rows from the New Table.
@Source_Table_Name - The name of a table with the same columns as Table_Name (Nulls and Types can differ) with new data to update Production table. If empty or Null new name will be Table_Name on a 'New' schema
@IdentifyingColumns - List of the columns to uniqually identify rows in both tables. Needed - can't be empty or Null.
@Filter - String that needed to filter rows from source table. Source table name should have allias 'NSET', for Destination table allias = Table name. Can be Null.
###################################################################################################################
*/
BEGIN
/*====================================== TESTING =======================================================================*/
--DECLARE @Main_Table_Name VARCHAR(4000) = 'dbo.Customers', @Source_Table_Name VARCHAR(200) = 'dbo.Customers_NEW', @IdentifyingColumns VARCHAR(400) = '[CustomerID]', @Filter VARCHAR(4000)
/*====================================== TESTING =======================================================================*/
DECLARE @LOG_SOURCE VARCHAR(200) = @Main_Table_Name, @Error VARCHAR(MAX) = ''
DECLARE @START_DATE DATETIME2 (3) = SYSDATETIME(), @LOG_MESSAGE VARCHAR(1000), @Trace_Flag BIT = 0 -- Testing
IF @Main_Table_Name IS NULL SET @Error = @Error + 'Source Table name cannot be NULL'
IF @Main_Table_Name = @Source_Table_Name SET @Error = @Error + 'Destination Table name cannot be equal to Source Table name'
IF LEN(@Error) > 0
BEGIN
PRINT @Error
END
ELSE
BEGIN
DECLARE @Param VARCHAR(100) = 'Alias,Type,NoPrint'
DECLARE @Table_DISTRIBUTION VARCHAR(100) = @Param, @Table_INDEX VARCHAR(MAX) = @Param, @Table_PARTITION VARCHAR(MAX) = @Param, @CreateStatistics_SQL VARCHAR(8000) = @Param
DECLARE @Schema VARCHAR(30), @Table VARCHAR(100), @New_Schema VARCHAR(30), @New_Table VARCHAR(100)
DECLARE @sql VARCHAR(MAX), @SQL_SELECT VARCHAR(MAX) = @Param, @SQL_WHERE VARCHAR(MAX)
DECLARE @SQL_ALTER VARCHAR(MAX) = @Param + ',KeepOld', @TempTableName VARCHAR(100)
DECLARE @Dot INT = CHARINDEX('.',@Main_Table_Name)
SELECT
@Schema = CASE WHEN @Dot = 0 THEN 'dbo' ELSE REPLACE(REPLACE(REPLACE(LEFT(@Main_Table_Name,@Dot),'[',''),']',''),'.','') END,
@Table = CASE WHEN @Dot = 0 THEN REPLACE(REPLACE(@Main_Table_Name,'[',''),']','') ELSE REPLACE(REPLACE(SUBSTRING(@Main_Table_Name,@Dot + 1,200),'[',''),']','') END
IF (@Source_Table_Name IS NULL) OR (LEN(@Source_Table_Name) = 0)
SET @Source_Table_Name = 'dbo.' + @Table + '_New'
SET @Dot = CHARINDEX('.',@Source_Table_Name)
SELECT
@New_Schema = CASE WHEN @Dot = 0 THEN 'dbo' ELSE REPLACE(REPLACE(REPLACE(LEFT(@Source_Table_Name,@Dot),'[',''),']',''),'.','') END,
@New_Table = CASE WHEN @Dot = 0 THEN REPLACE(REPLACE(@Source_Table_Name,'[',''),']','') ELSE REPLACE(REPLACE(SUBSTRING(@Source_Table_Name,@Dot + 1,200),'[',''),']','') END
SET @TempTableName = '[dbo].[' + @Table + '_Temp]'
EXEC dbo.Get_Distribution_String @Main_Table_Name, @Table_DISTRIBUTION OUTPUT
EXEC dbo.Get_Index_String @Main_Table_Name, @Table_INDEX OUTPUT
EXEC dbo.Get_Partition_String @Main_Table_Name, @Table_PARTITION OUTPUT
EXEC dbo.Get_Select_String @Main_Table_Name, @SQL_SELECT OUTPUT
EXEC dbo.Get_Where_String @Main_Table_Name, @IdentifyingColumns, @SQL_WHERE OUTPUT
EXEC dbo.Get_CreateStatistics_SQL @Main_Table_Name, @TempTableName, @CreateStatistics_SQL OUTPUT
EXEC dbo.Get_TransferObject_SQL @TempTableName, @Main_Table_Name, @SQL_ALTER OUTPUT
SELECT @Filter = CASE WHEN ISNULL(@Filter,'') = '' THEN '1 = 1' ELSE @Filter END
SET @sql = CHAR(13) + 'IF OBJECT_ID(''' + @TempTableName + ''') IS NOT NULL DROP TABLE ' + @TempTableName + ';' + CHAR(13)
SET @sql = @sql + 'CREATE TABLE ' + @TempTableName + ' WITH (' + @Table_INDEX + ', ' + @Table_DISTRIBUTION + @Table_PARTITION + ') AS' + CHAR(13) + 'SELECT ' + @SQL_SELECT + CHAR(13) + 'FROM ' + @Schema + '.[' + @Table + '] AS [' + @Table + ']' + CHAR(13)
SET @sql = @sql + 'WHERE NOT EXISTS (SELECT 1 FROM ' + @New_Schema + '.[' + @New_Table + '] AS NSET WHERE ' + @SQL_WHERE + ') ' + CHAR(13) + 'UNION ALL' + CHAR(13) + 'SELECT ' + REPLACE(@SQL_SELECT,'[' + @Table + '].','NSET.') + CHAR(13)
SET @sql = @sql + 'FROM ' + @New_Schema + '.[' + @New_Table + '] AS NSET WHERE ' + @FILTER + CHAR(13) + 'OPTION (LABEL = ''' + @TempTableName + ' LOAD: Get all changed rows'');'
IF @Trace_Flag = 1 EXEC dbo.LongPrint @sql
EXECUTE (@sql);
IF @Trace_Flag = 1 EXEC dbo.LongPrint @CreateStatistics_SQL
EXEC (@CreateStatistics_SQL)
IF @Trace_Flag = 1 EXEC dbo.LongPrint @SQL_ALTER
EXEC (@SQL_ALTER)
END
END