I have a table in a remote server that I want to update/delete based on the data I already have in a local server. My thought is to use MERGE for this. but SQL give me Invalid object name error if I use any remote server name. So is this possible? any advice is welcomed!
1 Answers
You can create a dataset to your local DB via self-host intergration runtime。
Then create the table,table type parameter and a procudure in Azure SQL.
CREATE TABLE [dbo].[emp](
[id] [int] NOT NULL,
[name] [varchar](20) NULL,
[age] [int] NULL
)
CREATE TYPE [dbo].[EmpType] AS TABLE(
[id] [int] NOT NULL,
[name] [nvarchar](max) NOT NULL,
[age] [nvarchar](max) NOT NULL
)
GO
CREATE PROCEDURE [dbo].[uspEmp]
@emp [dbo].[EmpType] READONLY
AS
MERGE [dbo].[emp] AS target_sqldb
USING @emp AS source_tblstg
ON target_sqldb.id = source_tblstg.id
WHEN MATCHED THEN
UPDATE SET
target_sqldb.name = source_tblstg.name,
target_sqldb.age = source_tblstg.age
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (
source_tblstg.id,
source_tblstg.name,
source_tblstg.age
);
--WHEN NOT MATCHED BY SOURCE THEN
--DELETE;
- Select stored procedure and import table tpye parameter at sink setting.
Related