帮助Merge Sql语句 [英] Help with Merge Sql Statement
本文介绍了帮助Merge Sql语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Hi Guys，
Hi Guys,
需要大帮助。在我可以使用Merge Statement的项目上工作。我们正在处理很多文件。所有文件都是相同的表结构。但是，每个文件的逻辑是不同的。
我正在尝试自动化该过程，因此用户将配置"Tbl_Config"。表格和流程将处理所有事情。
Need big help. Working on a project where I can use Merge Statement. We are processing a lot of files. All files are the same table structure. However, the logic of each file is different.
I am trying to automate the process, so the user will configure the "Tbl_Config" table and process will handle everything.
- 步骤。将源文件加载到临时表中
- 获取现有文件的前缀和tbl_Config表中的数据（通过前缀链接）以找出tbl_Source和tbl_Dest表之间的链接类型以及哪个表我们要
应该更新，等等。
下面是我为测试创建的测试表/数据。是否有人帮助我如何完成它或更好的方法来做到这一点。非常感谢它。
Below is the test tables/data that I created for testing. Is anyone help me to how to accomplish it or a better way to do this. That's really appreciated it.
谢谢。
Thank You.
CREATE TABLE tbl_Source
(
ID INT IDENTITY(1,1)
,FName VARCHAR(25)
,LName VARCHAR(25)
,City VARCHAR(15)
,State VARCHAR(2)
,PhoneNumber VARCHAR(15)
,FilePrefix VARCHAR(4)
)
CREATE TABLE tbl_Dest
(
ID INT IDENTITY(1,1)
,FName VARCHAR(25)
,LName VARCHAR(25)
,City VARCHAR(15)
,State VARCHAR(2)
,PhoneNumber VARCHAR(15)
,FilePrefix VARCHAR(4)
)
CREATE TABLE tbl_Config
(
ID INT IDENTITY(1,1)
,FilePrefix VARCHAR(4)
,FileType VARCHAR(20)
,LinkingColumn1 VARCHAR(15)
,LinkingColumn2 VARCHAR(15)
,UpdatedColumn1 VARCHAR(15)
,UpdatedColumn2 VARCHAR(15)
,UpdatedColumn3 VARCHAR(15)
,UpdatedColumn4 VARCHAR(15)
,UpdatedColumn5 VARCHAR(15)
)
INSERT INTO dbo.tbl_Dest
( FName ,
LName ,
City ,
State ,
PhoneNumber ,
FilePrefix
)
SELECT 'Smith','Jon','SACRAMENTO','CA','8443039333','CUNM'
UNION ALL
SELECT 'Kivell','Gill','RIO LINDA','CA','8443933311','CUNM'
UNION ALL
SELECT 'Morgan','Andrews','CITRUS HEIGHTS','CA','8553132555','CUNM'
UNION ALL
SELECT 'Smith','Jardine','NORTH HIGHLANDS','CA','8447611424','CLMA'
UNION ALL
SELECT 'Howard','Jones','ELK GROVE','CA','8449393339','CLMA'
UNION ALL
SELECT 'Jones','Parent','WEST SACRAMENTO','CA','8553132555','CLMA'
INSERT INTO dbo.tbl_Source
( FName ,
LName ,
City ,
State ,
PhoneNumber ,
FilePrefix
)
SELECT 'Smith','Kim','SACRAMENTO','CA','8443039333','CUNM'
UNION ALL
SELECT 'Kivell','Gil','RIO LINDA','CA','8443933311','CUNM'
UNION ALL
SELECT 'Morgan','Andy','CITRUS HEIGHTS','CA','8553132555','CUNM'
UNION ALL
SELECT 'Smith','Jardine','NORTH HIGHLANDS','CA','8447611426','CLMA'
UNION ALL
SELECT 'Howard','Jones','ELK GROVE','CA','8449393340','CLMA'
UNION ALL
SELECT 'Jones','Pack','WEST SACRAMENTO','CA','8553132555','CLMA'
INSERT INTO dbo.tbl_Config
( FilePrefix ,
FileType ,
LinkingColumn1 ,
LinkingColumn2 ,
UpdatedColumn1 ,
UpdatedColumn2 ,
UpdatedColumn3 ,
UpdatedColumn4 ,
UpdatedColumn5
)
SELECT 'CUNM','Update','PhoneNumber',NULL,'FName','LName',NULL,NULL,NULL
UNION ALL
SELECT 'CLMA','INSERT/UPDATE','LName','State','PhoneNumber',NULL,NULL,NULL,NULL
SELECT * FROM dbo.tbl_Config
SELECT * FROM dbo.tbl_Dest
SELECT * FROM dbo.tbl_Source
推荐答案
对于INSERT / UPDATE，你给出了只有单列。为了更新它的罚款，但INSERT怎么办？在那种情况下你会插入什么作为其他列的值？如果他们有一些默认定义然后它的罚款，否则他们将最终得到NULL值
For INSERT/UPDATE you have given only single column. For update its fine but what about INSERT? In that case what would you insert as values for other columns? If they have some defaults defined then its fine, otherwise they will end up with NULL values
您的解决方案将如下所示
Your solution will look like this
CREATE TABLE tbl_Source
(
ID INT IDENTITY(1,1)
,FName VARCHAR(25)
,LName VARCHAR(25)
,City VARCHAR(15)
,State VARCHAR(2)
,PhoneNumber VARCHAR(15)
,FilePrefix VARCHAR(4)
)
CREATE TABLE tbl_Dest
(
ID INT IDENTITY(1,1)
,FName VARCHAR(25)
,LName VARCHAR(25)
,City VARCHAR(15)
,State VARCHAR(2)
,PhoneNumber VARCHAR(15)
,FilePrefix VARCHAR(4)
)
CREATE TABLE tbl_Config
(
ID INT IDENTITY(1,1)
,FilePrefix VARCHAR(4)
,FileType VARCHAR(20)
,LinkingColumn1 VARCHAR(15)
,LinkingColumn2 VARCHAR(15)
,UpdatedColumn1 VARCHAR(15)
,UpdatedColumn2 VARCHAR(15)
,UpdatedColumn3 VARCHAR(15)
,UpdatedColumn4 VARCHAR(15)
,UpdatedColumn5 VARCHAR(15)
)
INSERT INTO dbo.tbl_Dest
( FName ,
LName ,
City ,
State ,
PhoneNumber ,
FilePrefix
)
SELECT 'Smith','Jon','SACRAMENTO','CA','8443039333','CUNM'
UNION ALL
SELECT 'Kivell','Gill','RIO LINDA','CA','8443933311','CUNM'
UNION ALL
SELECT 'Morgan','Andrews','CITRUS HEIGHTS','CA','8553132555','CUNM'
UNION ALL
SELECT 'Smith','Jardine','NORTH HIGHLANDS','CA','8447611424','CLMA'
UNION ALL
SELECT 'Howard','Jones','ELK GROVE','CA','8449393339','CLMA'
UNION ALL
SELECT 'Jones','Parent','WEST SACRAMENTO','CA','8553132556','CLMA'
INSERT INTO dbo.tbl_Source
( FName ,
LName ,
City ,
State ,
PhoneNumber ,
FilePrefix
)
SELECT 'Smith','Kim','SACRAMENTO','CA','8443039333','CUNM'
UNION ALL
SELECT 'Kivell','Gil','RIO LINDA','CA','8443933311','CUNM'
UNION ALL
SELECT 'Morgan','Andy','CITRUS HEIGHTS','CA','8553132555','CUNM'
UNION ALL
SELECT 'Smith','Jardine','NORTH HIGHLANDS','CA','8447611426','CLMA'
UNION ALL
SELECT 'Howard','Jones','ELK GROVE','CA','8449393340','CLMA'
UNION ALL
SELECT 'Jones','Pack','WEST SACRAMENTO','CA','8553132556','CLMA'
INSERT INTO dbo.tbl_Config
( FilePrefix ,
FileType ,
LinkingColumn1 ,
LinkingColumn2 ,
UpdatedColumn1 ,
UpdatedColumn2 ,
UpdatedColumn3 ,
UpdatedColumn4 ,
UpdatedColumn5
)
SELECT 'CUNM','Update','PhoneNumber',NULL,'FName','LName',NULL,NULL,NULL
UNION ALL
SELECT 'CLMA','INSERT/UPDATE','LName','State','PhoneNumber',NULL,NULL,NULL,NULL
declare @SQL varchar(max)
SET @SQL = STUFF((SELECT CHAR(13) + CHAR(10) + ';With DestCTE AS (SELECT * FROM dbo.tbl_Dest WHERE FilePrefix = ''' + FilePrefix + '''),SrcCTE AS (SELECT * FROM dbo.tbl_Source WHERE FilePrefix = ''' + FilePrefix + ''')
MERGE DestCTE AS d
USING SrcCTE AS s
ON s.' + LinkingColumn1 + ' = d.' + LinkingColumn1
+ IIF(LinkingColumn2 > '', ' AND s.' + LinkingColumn2 + ' = d.' + LinkingColumn2,'')
+ CASE WHEN FileType IN ('Update','INSERT/UPDATE') THEN ' WHEN MATCHED THEN UPDATE SET ' + UpdatedColumn1 + ' = s.' + UpdatedColumn1
+ IIF(UpdatedColumn2 > '', ' ,' + UpdatedColumn2 + ' = s.' + UpdatedColumn2,'')
+ IIF(UpdatedColumn3 > '', ' ,' + UpdatedColumn3 + ' = s.' + UpdatedColumn3,'')
+ IIF(UpdatedColumn4 > '', ' ,' + UpdatedColumn4 + ' = s.' + UpdatedColumn4,'')
+ IIF(UpdatedColumn5 > '', ' ,' + UpdatedColumn5 + ' = s.' + UpdatedColumn5,'') ELSE '' END
+ CASE WHEN FileType IN ('INSERT/UPDATE','INSERT' ) THEN ' WHEN NOT MATCHED THEN INSERT (' + UpdatedColumn1 + IIF(UpdatedColumn2 > '',',' + UpdatedColumn2,'')
+ IIF(UpdatedColumn3 > '',',' + UpdatedColumn3,'')
+ IIF(UpdatedColumn4 > '',',' + UpdatedColumn4,'')
+ IIF(UpdatedColumn5 > '',',' + UpdatedColumn5,'') + ') VALUES(s.' + UpdatedColumn1 + IIF(UpdatedColumn2 > '',',s.' + UpdatedColumn2,'')
+ IIF(UpdatedColumn3 > '',',s.' + UpdatedColumn3,'')
+ IIF(UpdatedColumn4 > '',',s.' + UpdatedColumn4,'')
+ IIF(UpdatedColumn5 > '',',s.' + UpdatedColumn5,'') + ')' ELSE '' END + ';'
FROM dbo.tbl_Config
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'')
EXEC(@SQL)
SELECT * FROM dbo.tbl_Dest
SELECT * FROM dbo.tbl_Source
/*
Output
----------------------------------------
ID FName LName City State PhoneNumber FilePrefix
---------------------------------------------------------------------------
1 Smith Kim SACRAMENTO CA 8443039333 CUNM
2 Kivell Gil RIO LINDA CA 8443933311 CUNM
3 Morgan Andy CITRUS HEIGHTS CA 8553132555 CUNM
4 Smith Jardine NORTH HIGHLANDS CA 8447611426 CLMA
5 Howard Jones ELK GROVE CA 8449393340 CLMA
6 Jones Parent WEST SACRAMENTO CA 8553132556 CLMA
7 NULL NULL NULL NULL 8553132556 NULL
ID FName LName City State PhoneNumber FilePrefix
------------------------------------------------------------------------
1 Smith Kim SACRAMENTO CA 8443039333 CUNM
2 Kivell Gil RIO LINDA CA 8443933311 CUNM
3 Morgan Andy CITRUS HEIGHTS CA 8553132555 CUNM
4 Smith Jardine NORTH HIGHLANDS CA 8447611426 CLMA
5 Howard Jones ELK GROVE CA 8449393340 CLMA
6 Jones Pack WEST SACRAMENTO CA 8553132556 CLMA
*/具有NULL值的行就是我上面解释的
The row with NULL values is what I explained above
这篇关于帮助Merge Sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
