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
-- аналогично для второй таблыDECLARE @tableName NVARCHAR(20);
SELECT @tableName = dictionary_link FROM table1;
EXEC(' SELECT * FROM '+@tableName+' WHERE 1 = 1 ');CREATE TABLE #test (id INT);
INSERT INTO #test EXEC(' SELECT id FROM '+@tableName+' WHERE 1 = 1 ');
-- или так:
EXEC(' SELECT id INTO #test FROM '+@tableName+' WHERE 1 = 1 ');SELECT
UserId, [Resource] = ResourceId, table_num,
[Action] = CASE WHEN table_num = 1 THEN ... -- table_num тут индикатор из какого массива запись.
FROM (
SELECT UserId, ResourceId, AssignmentId,table_num = min(table_num)
FROM (
SELECT DISTINCT UserId = M2.UserId
, ResourceId = ISNULL(M2.ResourceId,'00000000-0000-0000-0000-000000000000')
, AssignmentId = ISNULL(M2.AssignmentId, '00000000-0000-0000-0000-000000000000')
,1 [table_num] from #newMatrix M2
UNION
SELECT DISTINCT UserId = M2.UserId
, ResourceId = ISNULL(M2.ResourceId,'00000000-0000-0000-0000-000000000000')
, AssignmentId = ISNULL(M2.AssignmentId, '00000000-0000-0000-0000-000000000000')
,2 from #oldMatrix M2
) a
GROUP BY UserId, ResourceId, AssignmentId
HAVING COUNT(*) <> 2 -- одинаковые записи в обеих таблицах, останется только разница.
) S
<code>
</code>-- 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