If record doesn't exist,generate AnnouncementID,if record exists,don't generate AnnouncementID,how to do it?

Question

I have 2 tables like this:

CREATE TABLE t1
(
    [SupplyTitle] [NVARCHAR](50) NOT NULL,
    [EmployeeCode] INT NOT NULL,
    [registered] DATETIME,
    [modified]  DATETIME
) 

CREATE TABLE t2
(
    [SupplyTitle] [NVARCHAR](50) NOT NULL,
    [EmployeeCode] INT NOT NULL,
    [AnnouncementID] INT NOT NULL
    [registered] DATETIME,
    [modified]  DATETIME
)

The records in t1 likes this:

SupplyTitle  EmployeeCode  registered                 modified
-----------------------------------------------------------------------------
aaa          9001         2018-11-15 15:41:36.613     2018-11-15 15:41:36.613
bbb          9002         2018-11-15 15:42:36.613     2018-11-15 15:42:36.613
ccc          9003         2018-11-15 15:43:36.613     2018-11-15 15:43:36.613

The AnnouncementID in t2 is generated by this procedure R_GetManageID:

DECLARE @ManageID TABLE(ManageID INT)   
DECLARE @AnnouncementID int         

INSERT INTO @ManageID           
EXEC  R_GetManageID         

SET @AnnouncementID = (SELECT TOP 1 * FROM @ManageID)       
SELECT @AnnouncementID

I want to insert data from t1 to t2.

The insertion rule are：

If there is no t1's record in t2, insert into t2, the correlation condition is t1.SupplyTitle = t2.SupplyTitle and t1.EmployeeCode = t2.EmployeeCode.
If exists t1' record in t2, just update t2.modified = getdate().

That means t2's AnnouncementID is only when the correlation condition is not reached, then generate @AnnouncementID.

For example, at the beginning, T2 was empty.

The records in t1 likes this:

SupplyTitle  EmployeeCode  registered                 modified
    aaa          9001         2018-11-15 15:41:36.613     2018-11-15 15:41:36.613
    bbb          9002         2018-11-15 15:42:36.613     2018-11-15 15:42:36.613
    ccc          9003         2018-11-15 15:43:36.613     2018-11-15 15:43:36.613

If the initial value of @AnnouncementID is 1001,if every time this value adds one.

Then the generated records in t2 likes this:

SupplyTitle  EmployeeCode AnnouncementID     registered                 modified
aaa          9001         1001       2018-11-19 12:00:00.000     2018-11-19 12:00:00.000
bbb          9002         1002       2018-11-19 12:00:00.000     2018-11-19 12:00:00.000
ccc          9003         1003       2018-11-19 12:00:00.000     2018-11-19 12:00:00.000

Then the records in t1 is changed to this:

SupplyTitle  EmployeeCode  registered                 modified
aaa          9001         2018-11-15 15:41:36.613     2018-11-15 15:41:36.613
bbb          9002         2018-11-15 15:42:36.613     2018-11-15 15:42:36.613
www          9008         2018-11-15 15:43:36.613     2018-11-15 15:43:36.613

So the records in t2 should add one row likes this:

SupplyTitle  EmployeeCode AnnouncementID     registered                 modified
aaa          9001         1001       2018-11-19 12:00:00.000     2018-11-19 12:00:00.000
bbb          9002         1002       2018-11-19 12:00:00.000     2018-11-19 12:30:00.000
ccc          9003         1003       2018-11-19 12:00:00.000     2018-11-19 12:30:00.000
www          9008         1004       2018-11-19 12:30:00.000     2018-11-19 12:30:00.000

And the first 2 records' modified changed.

So how can I insert(update) t2 likes this?

The solution in your case is `MERGE`. Take a look at the following link: [Using MERGE in SQL Server to insert, update and delete at the same time](https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/) Using `MERGE` helps you the **sync** two tables. — mohabbati, Nov 19 '18 at 05:30
@Mohammad Mohabbati I don't know how to deal it, so I haven't got it yet. — , Nov 19 '18 at 05:33
You need a `unique key` to compare data in tow tables. Is there any unique key in your tables? — mohabbati, Nov 19 '18 at 05:45
or combination of some fields that make a unique key in your tables? — mohabbati, Nov 19 '18 at 06:07

score 0 · Answer 1 · answered Nov 19 '18 at 07:11

Using MERGE helps you the sync two tables as follow:

merge t2 as t
using t1 as s
on
(
    t.SupplyTitle = s.SupplyTitle
    and
        t.EmployeeCode = s.EmployeeCode
)
when matched then
    update
    set
        t.registered = s.registered, --any other data
        t.modified = s.modified,
        t.AnnouncementID = 0--'your_data'
when not matched by target then
    insert
        (
            SupplyTitle,
            EmployeeCode,
            AnnouncementID,
            registered,
            modified
        )
    values
        (
            s.SupplyTitle,
            s.EmployeeCode,
            0,--'your_data',
            s.registered,
            s.modified
        )

output $action, 
       deleted.SupplyTitle      as target_SupplyTitle, 
       deleted.EmployeeCode     as target_EmployeeCode, 
       deleted.registered       as target_registered,
       deleted.modified         as target_modified,
       inserted.SupplyTitle     as source_SupplyTitle, 
       inserted.EmployeeCode    as source_EmployeeCode, 
       inserted.registered      as source_registered,
       inserted.modified        as source_modified;

Take a look at the Reference Link to understand how it works.

If record doesn't exist,generate AnnouncementID,if record exists,don't generate AnnouncementID,how to do it?

1 Answers1