I am trying to create an Migration Script but i need some help:, i had the very basic script to insert an item into a table but i am trying to do it in a way to check for the item first if it exists, then insert it else skip it
here is my code:
use mydatabase;
INSERT INTO dbo.mytable(col1,col2,col3)
SELECT '3','test1','test3/abc.html';
You don't need to repeat the expression just use value construct like that :
INSERT INTO dbo.mytable(col1,col2,col3)
SELECT t.col1, t.col2, t.col3
FROM (values (3, 'test1','test3/abc.html')) t (col1, col2, col3)
WHERE NOT EXISTS (
SELECT 1
FROM dbo.mytable m
WHERE m.col1 = t.col1
AND m.col2 = t.col2
AND m.col3 = t.col3
);
INSERT INTO dbo.mytable(col1,col2,col3)
SELECT '3','test1','test3/abc.html'
WHERE NOT EXISTS (
SELECT 1
FROM dbo.mytable
WHERE col1='3'
AND col2='test1'
AND col3='test3/abc.html'
)
You can change the where depending on what you consider already inserted.
Something like this (but you would need some kind of key to identify if the record exists or not) -
IF NOT EXISTS(SELECT 1 FROM dbo.mytable WHERE col1 = '3' AND col2 = 'test1' AND col3 = 'test3/abc.html')
BEGIN
insert into dbo.mytable(col1,col2,col3)
SELECT '3','test1','test3/abc.html'
END
Use a MERGE:
CREATE TABLE #mytable (col1 nvarchar(20), col2 nvarchar(20), col3 nvarchar(20))
INSERT INTO #mytable(col1,col2,col3)
SELECT '3','test1','test3/abc.html';
Merge #mytable t
USING (SELECT '3','test1','test3/abc.html') s (col1, col2, col3)
ON t.col1 = s.col1
AND t.col2 = s.col2
AND t.col3 = s.col3
when not matched by target then
INSERT (col1,col2,col3) VALUES (s.col1, s.col2, s.col3);
MergeorDML trigger