Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 3rd, 2007, 02:19 PM
Friend of Wrox
Points: 718, Level: 10
Points: 718, Level: 10 Points: 718, Level: 10 Points: 718, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: Ennis, Clare, Ireland.
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default Inserting additional records to table

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
__________________
Neal

A Northern Soul
 
Old October 3rd, 2007, 03:38 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
 
Old October 4th, 2007, 06:08 AM
Friend of Wrox
Points: 718, Level: 10
Points: 718, Level: 10 Points: 718, Level: 10 Points: 718, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: Ennis, Clare, Ireland.
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default

Many thanks, worked a treat and thanks for the tips

Neal

A Northern Soul




Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting unique records from a table to another elygp SQL Server 2000 0 September 10th, 2007 01:58 AM
Inserting Records Question? ersp ADO.NET 1 May 12th, 2004 09:55 AM
Inserting Returned Records hugh@kmcnetwork.com SQL Language 2 November 4th, 2003 01:05 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.