Help with Merge Statement in SQL

Transact-SQL
1

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.

  1. Step. Load source file into a Staging table
  2. Get the Prefix of an existing file and the data from the tbl_Config table (link via Prefix) to find out what would be the Linking type between tbl_Source and tbl_Dest table and which table we
    should update and so on.

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

2

Couple of questions for you

  • All files are the same table structure.
    What kind of files are these? csv, tab delimited?
    Please post sample file?
  • The user will configure the "Tbl_Config" table and process will handle everything.
    How and why will you allow the user to configure a sql table?

Thanks

3

Here it is.

  • All files are the same table structure.
    What kind of files are these? csv, tab delimited? = .csv
    Please post sample file? = I will create one. (Do you have something different than "Merge Statement"?
  • The user will configure the "Tbl_Config" table and process will handle everything.
    How and why will you allow the user to configure a sql table? = I am using SSRS to configure that table. I don't want to involve in configuration.
4

I would change the config table to be more scalable. Something like

CREATE TABLE tbl_Config
(
ID INT IDENTITY(1,1)
,FilePrefix VARCHAR(4)
,RecordType tinyint
,FromColumn VARCHAR(15)
,ToColumn VARCHAR(15)
)

Then the RecordType could be 1 = JoinColumn, 2 = Update, 3 = Insert/Update, etc..
so your joins would use RecordType 1, the update would use 2 or 3, the insert would use 2. and then dynamically generate your merge statement. The other issue you could run into is if the merge attempts to update more than 1 record at a time. It will return an error