-- вариант 1
SELECT s.id, s.Column1, s.Column2
FROM (
SELECT t.id, t.Column1, t.Column2, RN = ROW_NUMBER() OVER (PARTITION BY t.Column2 ORDER BY id ASC)
FROM test1 AS t
) S
WHERE S.RN = 1
-- вариант 2 с CTE
;WITH S AS (
SELECT t.id, t.Column1, t.Column2, RN = ROW_NUMBER() OVER (PARTITION BY t.Column2 ORDER BY id ASC)
FROM test1 AS t
)
SELECT s.id, s.Column1, s.Column2
FROM s
WHERE S.RN = 1CREATE 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-- drop table test
CREATE TABLE test (id INT IDENTITY(1,1) NOT NULL, t1 INT, t2 INT, t3 INT);
GO
INSERT INTO test (t1, t2, t3)
VALUES (1,2,0),(3,4,0),(5,6,1)
GO
CREATE TRIGGER TG_testModify ON test AFTER INSERT,UPDATE
AS
BEGIN
IF UPDATE([t1])
SELECT d.t1 AS t1_old, i.t1 AS t1_new, 'Column_t1'
FROM INSERTED AS i
LEFT JOIN DELETED AS d ON i.id = d.id
WHERE ( NULLIF(d.t1, i.t1) IS NOT NULL AND d.t1 IS NOT NULL )
OR ( NULLIF(i.t1, d.t1) IS NOT NULL AND d.t1 IS NULL )
END
GO
UPDATE test SET t1 = 4 WHERE t3 = 0;
UPDATE test SET t1 = NULL WHERE t3 = 1;
UPDATE test SET t1 = 2 WHERE t3 = 1;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 ');-- 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