CREATE TABLE dbo.CONTENT_TFS2 (
TFS_ID BIGINT IDENTITY(1, 1) NOT NULL,
TFS_CONTENT NVARCHAR(1024) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO CONTENT_TFS2 (TFS_CONTENT)
VALUES
('111'),
('222')
SET IDENTITY_INSERT CONTENT_TFS2 ON
INSERT INTO CONTENT_TFS2 (TFS_ID,TFS_CONTENT)
VALUES
(7,'333'),
(8,'555')
SET IDENTITY_INSERT CONTENT_TFS2 OFF
CREATE TABLE #Hours (FixHours INT, AddHours INT, CalCulatedValue INT)
INSERT INTO #Hours VALUES (10,0,14),(5,0,14),(5,1,16),(0,5,17)
SELECT * FROM #Hours h
SELECT FixHours, AddHours, CalCulatedValue FROM #Hours h
WHERE NOT (h.FixHours > 0 AND h.AddHours > 0)
UNION ALL
SELECT FixHours, 0, h.CalCulatedValue FROM #Hours h
WHERE h.FixHours > 0 AND h.AddHours > 0
UNION ALL
SELECT 0, AddHours, h.CalCulatedValue FROM #Hours h
WHERE h.FixHours > 0 AND h.AddHours > 0
--DROP TABLE #ProductModel
CREATE TABLE #ProductModel (ProductModelID INT IDENTITY(1,1) NOT NULL, ProductName NVARCHAR(50))
--DROP TABLE #SyncTable
CREATE TABLE #SyncTable (SyncId INT IDENTITY(1,1) NOT NULL, ProductName NVARCHAR(50), ProductDescription NVARCHAR(50) ) /*, тут другие поля ); */
INSERT INTO #SyncTable (ProductName, ProductDescription) -- таблица связей ProductName vs ProductDescription; общее SyncID
VALUES ('test2','Description2')
,('test5','Description5')
,('test3','Description3')
-- для первой таблицы:
DECLARE @tProductModel TABLE (ProductModelID int, SyncID int); -- табл. переменная
MERGE #ProductModel P
USING #SyncTable S ON (S.ProductName = P.ProductName)
WHEN NOT MATCHED BY TARGET -- не совпало по имени, вставляем:
THEN INSERT (ProductName)
VALUES (S.ProductName)
OUTPUT INSERTED.ProductModelID, S.SyncId INTO @tProductModel (ProductModelID, SyncID);
SELECT * FROM @tProductModel AS tpm -- тут связь полученного ProductModelID с известным SyncId
-- аналогично для второй таблы
-- DROP TABLE TestInsert
CREATE TABLE TestInsert (ID INT NOT NULL, DATA NVARCHAR(3) NOT NULL, CONSTRAINT [PK_TestInsert__ID_DATA] PRIMARY KEY CLUSTERED ([ID],[DATA]) );
INSERT INTO TestInsert
VALUES (1, '123')
,(2, '222');
-- DROP TABLE #InsertSource
CREATE TABLE #InsertSource (ID INT NOT NULL, DATA NVARCHAR(3) NOT NULL);
INSERT INTO #InsertSource
VALUES (1, '123') -- имеются в базе
,(2,'222') -- имеются в базе
,(3,'555')
,(3,'555')
-- 1) WHERE NOT EXISTS
INSERT INTO TestInsert (ID, DATA)
SELECT ID , DATA FROM #InsertSource tis WHERE NOT EXISTS(SELECT TOP 1 1 FROM TestInsert ti WHERE ti.ID = tis.ID AND ti.DATA = tis.DATA)
-- тут будет ошибка Cannot insert duplicate key на (3,'555')
-- 2) MERGE
MERGE INTO TestInsert ti
USING #InsertSource tis ON (ti.ID = tis.ID AND ti.DATA = tis.DATA)
WHEN not matched THEN
INSERT (ID, DATA) VALUES (tis.ID, tis.DATA);
-- тут будет ошибка Cannot insert duplicate key на (3,'555')
-- как пример избавление от дубликатов ключей:
;WITH NumValues AS (
SELECT tis.ID, tis.DATA, RN = ROW_NUMBER() OVER ( PARTITION BY tis.ID, tis.DATA ORDER BY NEWID() )
FROM #InsertSource AS tis
)
INSERT INTO TestInsert (ID, DATA)
SELECT ID , DATA FROM NumValues nv
WHERE NOT EXISTS(SELECT TOP 1 1 FROM TestInsert ti WHERE ti.ID = nv.ID AND ti.DATA = nv.DATA)
AND nv.RN = 1 -- только 1-е строки среди совпавших tis.ID, tis.DATA