asked on

Simple insert where not exists statement assistance

I want to insert data from table1 into table2, where both contain the same fields, and table1 contains many duplicates. (This table is cleared and re-populated daily). I want to insert records from table1 into table2 and not allow a duplicate ID in table2. For the purpose of this question, the fields in the tables are ID (int), FName varchar(20), LName varchar(20), State char(2), RecordID (int). The query that I am trying to use is:

INSERT INTO [dbo].[table2] ([ID], [FName], [LName], [RecordID]) SELECT [ID], [FName], [LName], [RecordID] FROM [dbo].[table1] WHERE [ID] NOT EXISTS (SELECT [ID] FROM [dbo].[table2])

The result records in table2 contain duplicate ID values.

Any assistance would be appreciated.

Guy Hengel [angelIII / a3]

you don't tell a join in the EXISTS part:

INSERT INTO [dbo].[table2] ([ID], [FName], [LName], [RecordID]) SELECT [ID], [FName], [LName], [RecordID] FROM [dbo].[table1] WHERE [ID] NOT EXISTS (SELECT [ID] FROM [dbo].[table2] where dbo.table1.id = dbo.table2.id)

Open in new window

blberger

ASKER

Thank you for your quick response, but I still receive an error in trying to execute the statement. I have included the exact statement and the error that I received below:

INSERT INTO [dbo].[Admissions_OB_Header] ([chain_id], [StudentFirstName], [StudentLastName], [LeadCreateDate], [State]) SELECT [chain_id], [StudentFirstName], [StudentLastName], [LeadCreateDate], [State] FROM [dbo].[admissions_ob_temp] WHERE [chain_id] NOT EXISTS (SELECT [chain_id] FROM [dbo].[Admissions_OB_Header] where dbo.[admissions_ob_temp].[chain_id] = dbo.[Admissions_OB_Header].[chain_id])

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXISTS'. (MSSQL 2000)

I cannot see the syntax error?

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

blberger

ASKER

I have tried both provided solutions, even thought the one from Angellll is the version that I am looking for, neither one of them work as expected. I have it as below:

INSERT INTO [dbo].[Admissions_OB_Header] ([chain_id], [StudentFirstName], [StudentLastName], [LeadCreateDate], [State]) SELECT [chain_id], RTrim([StudentFirstName]), RTrim([StudentLastName]), [LeadCreateDate], [State] FROM [dbo].[admissions_ob_temp] WHERE NOT EXISTS (SELECT [chain_id] FROM [dbo].[Admissions_OB_Header] WHERE [dbo].[Admissions_OB_Header].[chain_id] =
[dbo].[admissions_ob_temp].[chain_id])

This runs in 30 seconds and processes 1.4 million records, but does not only allow unique [chain_id] values into the header table. I am stumped, because the version you provided should work perfect. Do you suggest another possible course?

Thank you for your time.

ASKER CERTIFIED SOLUTION

blberger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial