February 11, 2019 at 6:55 pm
Hi Guys,
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.
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
(
IDINT IDENTITY(1,1)
,FNameVARCHAR(25)
,LNameVARCHAR(25)
,CityVARCHAR(15)
,StateVARCHAR(2)
,PhoneNumberVARCHAR(15)
,FilePrefixVARCHAR(4)
)
CREATE TABLE tbl_Dest
(
IDINT IDENTITY(1,1)
,FNameVARCHAR(25)
,LNameVARCHAR(25)
,CityVARCHAR(15)
,StateVARCHAR(2)
,PhoneNumberVARCHAR(15)
,FilePrefixVARCHAR(4)
)
CREATE TABLE tbl_Config
(
IDINT IDENTITY(1,1)
,FilePrefixVARCHAR(4)
,FileTypeVARCHAR(20)
,LinkingColumn1VARCHAR(15)
,LinkingColumn2VARCHAR(15)
,UpdatedColumn1VARCHAR(15)
,UpdatedColumn2VARCHAR(15)
,UpdatedColumn3VARCHAR(15)
,UpdatedColumn4VARCHAR(15)
,UpdatedColumn5VARCHAR(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
February 12, 2019 at 8:18 am
Hello,
I think your idea is great, but I have a problem with it. Your tbl_config table cannot to change dynamically. Maybe you should to store the config in row based or in xml / json format instead of column based.
February 12, 2019 at 12:15 pm
Hi Salliven,
Thank You for your reply. Do you have any example or anything that I can take a look?
February 13, 2019 at 6:45 am
rocky_498 - Tuesday, February 12, 2019 12:15 PMHi Salliven,Thank You for your reply. Do you have any example or anything that I can take a look?
Yes, here it is:create table tbl_Config_xml_based
(
id int not null identity(1,1),
FilePrefix VARCHAR(4) not null,
FileType VARCHAR(20) not null,
Colums xml null
)
insert into tbl_Config_xml_based (FilePrefix, FileType, Colums)
values ('CUNM', 'Update', N'<root><link><name><![CDATA[PhoneNumber]]></name></link><update><name><![CDATA[FName]]></name><name><![CDATA[LName]]></name></update></root>'),
('CLMA', 'INSERT/UPDATE', N'<root><link><name><![CDATA[LName]]></name><name><![CDATA[State]]></name></link><update><name><![CDATA[PhoneNumber]]></name></update></root>')
select * from tbl_Config_xml_based
create table tbl_Config_row_based
(
id int not null identity(1,1),
group_id int not null,
column_type sysname not null,
column_value sysname not null
)
insert into tbl_Config_row_based (group_id, column_type, column_value)
values
(1, 'FilePrefix', 'CUNM'),
(1, 'FileType', 'Update'),
(1, 'LinkingColumn', 'PhoneNumber'),
(1, 'UpdateColumn', 'FName'),
(1, 'UpdateColumn', 'LName')
insert into tbl_Config_row_based (group_id, column_type, column_value)
values
(2, 'FilePrefix', 'CLMA'),
(2, 'FileType', 'INSERT/UPDATE'),
(2, 'LinkingColumn', 'LName'),
(2, 'LinkingColumn', 'State'),
(2, 'UpdateColumn', 'PhoneNumber')
select * from tbl_Config_row_based
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply