Çağatay Ay Çağatay Ay - 2 months ago 9
SQL Question

SQL Server : IF EXIST update or insert between similar database tables

I would like to use a simple code to update or insert a record beetween two similar databases.

In A database there is a table like this:

CREATE TABLE [dbo].A.[tblDuyurular]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[SiteID] [int] NOT NULL,
[Dil] [varchar](7) NULL,
[DuyuruBaslik] [varchar](250) NOT NULL,
[DuyuruIcerik] [varchar](max) NOT NULL
)


In B database:

CREATE TABLE [dbo].B.[TBLDuyurular]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[fakulteID] [int] NOT NULL,
[DuyuruBaslik] [varchar](250) NOT NULL,
[DuyuruIcerik] [varchar](max) NOT NULL,
[dil] [varchar](7) NULL
)


Matched columns are:

ID = ID,
SiteID = fakulteID,
Dil = dil,
DuyuruBaslik = DuyuruBaslik,
DuyuruIcerik = DuyuruIcerik,


I want to check dbo.B.TBLDuyurular records. If records exist in dbo.A.tblDuyurular then UPDATE dbo.A.tblDuyurular with dbo.B.TBLDuyurular's results. If records not exist in dbo.A.tblDuyurular, INSERT dbo.B.TBLDuyurular's results to dbo.A.tblDuyurular. Table columns are minimized. There are too many columns in these tables.

What is the best way for this situation?

Help please. Thanks.

Answer

Try MREGE;

MERGE [dbo].A.[tblDuyuyular] AS TARGET
USING [dbo].B.[tblDuyuyular] AS SOURCE
ON TARGET.ID = SOURCE.ID 
AND TARGET.SiteID  = SOURCE.fakulteID 
AND TARGET.Dil  = SOURCE.dil
AND TARGET.DuyuruBaslik  = SOURCE.DuyuruBaslik 
AND TARGET.DuyuruIcerik  = SOURCE.DuyuruIcerik 
WHEN MATCHED
THEN UPDATE TARGET.SiteID  = SOURCE.fakulteID, TARGET.Dil  = SOURCE.dil, TARGET.DuyuruBaslik  = SOURCE.DuyuruBaslik, TARGET.DuyuruIcerik  = SOURCE.DuyuruIcerik -- Add Other Columns
WHEN NOT MATCHED
THEN INSERT(TARGET.SiteID, TARGET.Dil, TARGET.DuyuruBaslik, TARGET.DuyuruIcerik) VALUES(SOURCE.fakulteID, SOURCE.dil, SOURCE.DuyuruBaslik, SOURCE.DuyuruIcerik) -- Add Other Columns

If you want to keep your mismatched IDs' same in SOURCE as TARGET table then follow this;

SET IDENTITY_INSERT [dbo].B.[tblDuyuyular] ON
GO
MERGE [dbo].A.[tblDuyuyular] AS TARGET
USING [dbo].B.[tblDuyuyular] AS SOURCE
ON TARGET.ID = SOURCE.ID 
AND TARGET.SiteID  = SOURCE.fakulteID 
AND TARGET.Dil  = SOURCE.dil
AND TARGET.DuyuruBaslik  = SOURCE.DuyuruBaslik 
AND TARGET.DuyuruIcerik  = SOURCE.DuyuruIcerik 
WHEN MATCHED
THEN UPDATE TARGET.SiteID  = SOURCE.fakulteID, TARGET.Dil  = SOURCE.dil, TARGET.DuyuruBaslik  = SOURCE.DuyuruBaslik, TARGET.DuyuruIcerik  = SOURCE.DuyuruIcerik -- Add Other Columns
WHEN NOT MATCHED
THEN INSERT(TARGET.ID, TARGET.SiteID, TARGET.Dil, TARGET.DuyuruBaslik, TARGET.DuyuruIcerik) VALUES(SOURCE.ID, SOURCE.fakulteID, SOURCE.dil, SOURCE.DuyuruBaslik, SOURCE.DuyuruIcerik) -- Add Other Columns
GO
SET IDENTITY_INSERT [dbo].B.[tblDuyuyular] OFF
GO