SQL Server: INSERT WHERE NOT EXISTS

3 Feb 2010 16:32

Because whenever I’ve been away from SQL Server for more than a couple of weeks, I forget.

/* Some tables. */
CREATE TABLE X (
	Name VARCHAR(20)
)
GO

CREATE TABLE Y (
	Name VARCHAR(20)
)
GO

/* Some sample data. */
INSERT INTO X(Name) VALUES('Alice')

INSERT INTO Y(Name) VALUES('Alice')
INSERT INTO Y(Name) VALUES('Bob')
INSERT INTO Y(Name) VALUES('Bob')
INSERT INTO Y(Name) VALUES('Charles')

/* The interesting bit. */
INSERT INTO X(Name)
	SELECT DISTINCT Name FROM Y
	WHERE NOT EXISTS ( SELECT * FROM X WHERE X.Name = Y.Name )