Inserting additional records to table

Neal · October 3rd, 2007, 02:19 PM

Hi,

I have created the following SQL query. This is fine on initialising the table but for succesive INSERTS, I need to only add records where the combined PostCode fields do NOT already exist in the dbo.MER_Consignee_PostCodes table.
(PostCode_1 and PostCode_2 are the Primary Key)

Code:

INSERT INTO dbo.MER_Consignee_PostCodes
(
PostCode_1,
PostCode_2,
Previously_Checked,
Active_Last_3_Years
)
SELECT DISTINCT PostCode_1,
PostCode_2,
'1',
'1'
FROM dbo.MER_Consignee_Master
GROUP BY 
PostCode_1,
PostCode_2
ORDER BY
PostCode_1,
PostCode_2

Succesive entries must have the Previously_Checked field set to '0'.

How do I do this within a query?

Thanks in advance,

Neal

A Northern Soul

joefawcett · October 3rd, 2007, 03:38 PM

You don't need DISTINCT and GROUP BY, one or the other, and ORDER BY has no effect when inserting as the rows can still emerge in any order.

Code:

INSERT INTO dbo.MER_Consignee_PostCodes
(
PostCode_1,
PostCode_2,
Previously_Checked,
Active_Last_3_Years
)
SELECT DISTINCT
MCM.PostCode_1,
MCM.PostCode_2,
'0',
'1'
FROM dbo.MER_Consignee_Master MCM
 LEFT OUTER JOIN dbo.MER_Consignee_PostCodes MCP
 ON MCM.PostCode_1 = MCP.PostCode_1
 AND MCM.PostCode_2 = MCP.PostCode_2
 WHERE MCP.PostCode_1 IS NULL AND MCP.PostCode_2 IS NULL;

--

Joe (Microsoft MVP - XML)

Neal · October 4th, 2007, 06:08 AM

Many thanks, worked a treat and thanks for the tips

Neal

A Northern Soul