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
 Transact-SQL (2005)
 Left Join Insert Query Error

Author  Topic 

kory27
Starting Member

35 Posts
Posted - 2008-05-08 : 09:09:40
I have been working on this for a little bit and have gotten to this point. Below is the query in blue, the error in red. Now, from what I gather the error is telling me I can't insert a duplicate product code into tblProduct. Isn't that what the left join is exactly not doing, as in, the left join is inserting all records from Complete_records into tblProduct where the code is null(does not exist) in tblProduct? Or, is this an issue where the identity number, productID in tblProduct, isn't starting at the next number in turn?

Thanks.

Set Identity_Insert tblProduct on
DECLARE @MaxId int

SELECT @MaxID=MAX(productID)
FROM tblProduct

SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL

INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp

(236752 row(s) affected)
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.tblProduct' with unique index 'IX_tblProductt_productCode'.
The statement has been terminated.

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2008-05-08 : 09:17:55
Yeah, but the results may come out with multiple prooductcodes.

Run this before your insert to find out where you are getting duplicates

select productcode, count(1)
from #Temp
group by productcode
having count(1) > 1
Go to Top of Page

kory27
Starting Member

35 Posts
Posted - 2008-05-08 : 10:12:31
yes there is indeed multiple records for about 6K rows in the Complete_Products. I am looking up ways to handle that now, but can anyone suggest a step to add to what i ran above that could handle inserting only 1 record during the insert?
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2008-05-08 : 10:20:37
That would depend on if the rest of the information for each of the lines was different (obviously except the IDENTITY. If they are all the same, first select them DISTINCT and then add the identity, this should give you unique record by productcode. If they are not distinct then you will need to figure out how to make them distinct (MAX(),MIN() etc).
Go to Top of Page

kory27
Starting Member

35 Posts
Posted - 2008-05-08 : 10:32:04
yeah, Complete_Products was a temp table, so i did not apply an identity to any column b/c the productId column is the identity and i can control that insert by turning it on and it will start at the last number used currently in tblProduct. The dupes are indeed different data besides the code, and the column i would use to key on would be the CategoryID and I would select the minimum.

I could change my schema to allow for duplicate product codes, but i just don't think that is a good idea moving forward. with 1 million product records, i don't think 3K more is really going to make it worth the potential headache down the road.

Did i make sense with all that?
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2008-05-08 : 11:03:41
Yes, but if you have different prices for the same productcode, then you may need to think about your schema, or at least think about what you are trying to insert.

Without knowing all your business rules, there is not a hell of a lot I can suggest.
Go to Top of Page

kory27
Starting Member

35 Posts
Posted - 2008-05-08 : 11:09:07
nah, the only difference is the title may be a little different, and the category. It really wouldn't hurt to only insert 1 of them.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2008-05-08 : 11:12:53
So use Max(Complete_products.Title) and see if that gives you unique records.
Go to Top of Page

kory27
Starting Member

35 Posts
Posted - 2008-05-08 : 11:17:31
so something like this (it is actually on the CategoryID and it is minimum). I am still learning a lot here so I really appreciate your input. I put the min around the Complete_Products.CategoryID in the first select. Do i also need to put it in the insert area?

Set Identity_Insert tblProduct on
DECLARE @MaxId int

SELECT @MaxID=MAX(productID)
FROM tblProduct

SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, min(Complete_products.CategoryID), Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL

INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2008-05-08 : 11:19:34
If the title is different, you will need to put either a Max or a Min on that (depending on your needs). When you have done this, you should have distinct records.
Go to Top of Page

kory27
Starting Member

35 Posts
Posted - 2008-05-09 : 14:02:49
Ok, i am going to get this, i really am. the reason i am trying to get it in one compact query step is i am trying to make a step by step import that anyone can run and it won't be a hassle.

anyway, i ran the query below in blue and got the errors in red. Would it be better for me to go back a step and take the 6K records that are doubles and insert them into a temp table using recordset=1 so it only inserts 1 record? i am just researching diff ways to handle this and that was one i read on. i'd really like to keep it in 1 step, and frankly, i don't care which of the dup records goes in b/c they are the same price and size and everything but each has a slightly different title, category, and rank.

thanks again in advance from all with insight.

Set Identity_Insert tblProduct on
DECLARE @MaxId int

SELECT @MaxID=MAX(productID)
FROM tblProduct

SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, min(Complete_products.Title), min(Complete_products.CategoryID), Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, min(Complete_products.IRank), Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL

INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp

Msg 8120, Level 16, State 1, Line 7
Column 'Complete_products.APNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 1038, Level 15, State 5, Line 7
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 1038, Level 15, State 5, Line 7
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 1038, Level 15, State 5, Line 7
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2008-05-12 : 01:35:50
No, when you use Min or Max, you have to group by all the other fields to make it unique. Also, as you are adding an identity, it would be better to do this:

SELECT 
	IDENTITY(int,1,1) AS ID, 
	a.APNum, 
	a.Title, 
	a.CategoryID, 
	a.Mountable, 
	a.price, 
	a.Height, 
	a.Width, 
	a.IRank, 
	a.frameable, 
	a.Typ 
INTO #Temp
from
(select 
	Complete_products.APNum, 
	min(Complete_products.Title) Title, 
	min(Complete_products.CategoryID) CategoryID, 
	Complete_products.Mountable, 
	Complete_products.price, 
	Complete_products.Height, 
	Complete_products.Width, 
	min(Complete_products.IRank) IRank, 
	Complete_products.frameable, 
	Complete_products.Typ 
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL
group by 
	Complete_products.APNum, 
	Complete_products.Mountable, 
	Complete_products.price, 
	Complete_products.Height, 
	Complete_products.Width, 
	Complete_products.frameable, 
	Complete_products.Typ) a
Go to Top of Page

kory27
Starting Member

35 Posts
Posted - 2008-05-12 : 11:38:42
ok, that worked without error, but my question now is does this insert these records into the #temp or actually into tblProducts? I opened tblProducts after it ran without error, affecting 235945 records which seems like the right number, but the number of records in the table was still exactly the same.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-05-12 : 11:45:03
quote:
Originally posted by kory27

ok, that worked without error, but my question now is does this insert these records into the #temp or actually into tblProducts? I opened tblProducts after it ran without error, affecting 235945 records which seems like the right number, but the number of records in the table was still exactly the same.

As you can see from query. Its putting the records into #temp temporary table. You need to populate your tblProduct table with the data fom #Temp.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-05-12 : 11:47:55
Try
INSERT	tblProduct
	(
		productCode,
		productName,
		productNavID,
		CanBeMounted,
		productRetailPrice,
		productHeight,
		productWidth,
		Rank,
		CanBeFramed,
		ProductType
	)
SELECT	cp.APNum,
	cp.Title,
	cp.CategoryID,
	cp.Mountable,
	cp.price,
	cp.Height,
	cp.Width,
	cp.IRank,
	cp.frameable,
	cp.Typ
FROM	Complete_products AS cp
WHERE	NOT EXISTS (SELECT * FROM tblProduct AS x WHERE x.productCode = cp.APNum)


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kory27
Starting Member

35 Posts
Posted - 2008-05-12 : 11:51:27
yeah, i was just disecting that a bit more and noticed it is only a select. Now I am really getting into uncharted territory. I have done inserts and updates but the temp table thing is something I am just starting to get my head around. I understand why you use them, they are a staging area of sorts, but a couple questions;

1. where are they stored?
2. can i view what is in the temp table before doing the insert?

Thanks.
Go to Top of Page

kory27
Starting Member

35 Posts
Posted - 2008-05-12 : 11:55:55
peso,

thanks for the post. we have the insert covered, but then i realized the feed, Complete_Products) has duplicate product codes which my schema does not allow and i do not have an interest in changing. i don't think your insert handles that though. Any other ideas besides how we are handling it so far to get those duplicates down to just 1 record per product code then insert? Thanks again.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-05-12 : 13:13:29
You think?
Based on experience or trial and error?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2008-05-13 : 01:34:01
quote:
Originally posted by Peso

You think?
Based on experience or trial and error?


E 12°55'05.25"
N 56°04'39.16"

Unfortunately, fromt he first post, it seems the OP is not getting unique values from Complete_products, it seems the title and CategoryID are changeable.

kory27 - You can use my query to replace the first query in your original post. You still need to run the final query (the Insert into Product).
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site