January 14, 2016 at 11:59 am
I'm trying to Load records in a Table called Postal from a table called Postal_Codes_Import
This is so easy. I have done it numerous time.
When I do an INNER JOIN I only get 3 records:
SELECT Post.ID AS ZipCode, Post.CityTownshipID, Post.Latitude, Post.Longitude
FROM Postal AS Post
INNER JOIN Postal_Codes_Import AS PostImp ON Post.ID = PostImp.ZipCode
What am I doing wrong on the LEFT JOIN Statement?
I get all the records in the Import table but when I do an INNER JOIN I GET 3 Recrds.
Here is the Code:
SELECT Postal.ID,PostImp.ZipCode, PostImp.Latitude, PostImp.Longitude,PostImp.CityTownshipID ,PostImp.CountyRegionID, PostImp.StateProvinceID, PostImp.CountryID
FROM Postal_Codes_Import AS PostImp
LEFT JOIN Postal AS Postal ON PostImp.ZipCode = Postal.ID
WHERE ID IS NULL
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2016 at 12:04 pm
January 14, 2016 at 12:09 pm
That would help if I included the error. :blush:
Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (B5A 5B4).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2016 at 12:14 pm
Why would the Id be the same as the ZipCode?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 14, 2016 at 12:16 pm
The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.
You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 14, 2016 at 12:17 pm
Like what it says: "Cannot insert duplicate key in object 'dbo.Postal'."
Perhaps you're attempting to insert a row that already exists in the target table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 14, 2016 at 12:20 pm
Phil Parkin (1/14/2016)
Why would the Id be the same as the ZipCode?
The ZipCode is named ID.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2016 at 12:21 pm
Phil Parkin (1/14/2016)
The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.
So what is the syntax?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2016 at 12:23 pm
Eric M Russell (1/14/2016)
Like what it says: "Cannot insert duplicate key in object 'dbo.Postal'."Perhaps you're attempting to insert a row that already exists in the target table.
That is correct.
I'm trying to exclude them.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2016 at 12:28 pm
Welsh Corgi (1/14/2016)
Phil Parkin (1/14/2016)
The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.
So what is the syntax?
Thanks.
INSERT [target] (columns)
SELECT (columns)
FROM [source]
WHERE NOT EXISTS (select 1 from [target] where target.(PK) = source.(PK))
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 14, 2016 at 12:29 pm
Welsh Corgi (1/14/2016)
Phil Parkin (1/14/2016)
The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.
So what is the syntax?
Thanks.
Here you go: http://bfy.tw/3jNm
January 14, 2016 at 12:30 pm
Perhaps a Data Type issue?
Table Structure:
CREATE TABLE [dbo].[Postal](
[ID] [varchar](16) NOT NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[CityTownshipID] [int] NOT NULL,
[CountyRegionID] [int] NULL,
[StateProvinceID] [int] NOT NULL,
[CountryID] [int] NOT NULL,
CONSTRAINT [PK_Postal] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Postal_Codes_Import](
[ZipCode] [nvarchar](255) NULL,
[CityName] [nvarchar](255) NULL,
[Longitude] [float] NULL,
[Latitude] [float] NULL,
[CityTownshipID] [int] NULL,
[CountyRegionID] [int] NULL,
[StateProvinceID] [int] NULL,
[CountryID] [int] NULL
) ON [PRIMARY]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2016 at 12:43 pm
Welsh Corgi (1/14/2016)
That would help if I included the error. :blush:Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (B5A 5B4).
I'm assuming that you've checked that there aren't duplicates in table that your importing.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 14, 2016 at 12:48 pm
Yes there are 3 duplicates when I do a INNER JOIN but I exclude them in the Insert and I keep getting PK Violations. :unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2016 at 12:50 pm
Phil Parkin (1/14/2016)
The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.
I execute the following code:
SELECT *
FROM Postal_Codes_Import
WHERE NOT EXISTS (SELECT *
FROM Postal
WHERE Postal.ID = Postal_Codes_Import.ZipCode);
And it excludes the 3 records.
Weird.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply