Insert row if not exists...still inserts? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Insert row if not exists...still inserts?

Author

Topic

DaveChapman
Starting Member

7 Posts

Posted - 2009-04-28 : 07:49:26

I have the following SQL in an SSIS Execute SQL Task.

INSERT INTO TMP_AREAS (AREA_CODE, AREA_NAME)
	SELECT AREA_CODE, AREA_NAME
	FROM TMP_EXCELIMPORT
	WHERE (NOT EXISTS (SELECT AREA_CODE FROM TMP_AREAS AS TMP_AREAS_CHECK WHERE AREA_CODE = TMP_EXCELIMPORT.AREA_CODE) )

For info, the data in TMP_EXCELIMPORT will be along the lines of:

AREA_CODE   AREA_NAME
10          SOUTH
20          NORTH
30          WEST
10          NORTH
10          NORTH
30          WEST

I only want to add each unique entry once into my resulting table, and only then if it doesn't already exist.

I thought the SQL would acheive this, but it is inserting every row, so for the above example I get North added 3 times. If I run it a second time, then it works as expected and only inserts "new" values.

Any help gratefully received.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-28 : 07:56:33

The INSERT is done as a single statement, and the NOT EXISTS check is done on data already in the table at the start, not as it progresses. For what you want to achieve add a DISTINCT:

INSERT INTO TMP_AREAS (AREA_CODE, AREA_NAME)
	SELECT DISTINCT AREA_CODE, AREA_NAME
	FROM TMP_EXCELIMPORT
	WHERE (NOT EXISTS (SELECT AREA_CODE FROM TMP_AREAS AS TMP_AREAS_CHECK WHERE AREA_CODE = TMP_EXCELIMPORT.AREA_CODE) )

DaveChapman
Starting Member

7 Posts

Posted - 2009-04-28 : 09:40:07

Thanks robvolk, works perfectly :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources