Based on the doc: Schema mapping in copy activity, merging columns is supported by schema mapping.
As workaround , I suggest configure sql server stored procedure
in your sql server sink. It can merge the data being copied with existing data.
Please follow the steps from this doc:
Step 1: Configure your Output dataset:
Step 2: Configure Sink section in copy activity as follows:
Step 3: In your database, define the table type with the same name as sqlWriterTableType. Notice that the schema of the table type should be same as the schema returned by your input data.
CREATE TYPE [dbo].[MarketingType] AS TABLE(
[FirstName] [varchar](256) NOT NULL,
[LastName] [varchar](256) NOT NULL,
[Gender] [varchar](256) NOT NULL
)
Step 4: In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. It handles input data from your specified source, and merge into the output table. Notice that the parameter name of the stored procedure should be the same as the "tableName" defined in dataset.
Create PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY
AS
BEGIN
MERGE [dbo].[jay] AS target
USING @Marketing AS source
ON (1=1)
WHEN NOT MATCHED THEN
INSERT (name, gender)
VALUES (source.FirstName + ' ' + source.LastName, UPPER(left(source.Gender,1)));
END
Output Screenshot:
Hope it helps you.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…