quote:

---

Originally posted by kory27

And should that start the inserted record # at the correct place? i don't want to overwrite existing data b/c the id 1, for example, will exist in both b/c the indentity column was autogenerated on both tables.

thanks again.

---

DECLARE @MaxId int

SELECT @MaxID=MAX(PRODUCT_ID)
FROM tblProduct

SELECT IDENTITY(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
(PRODUCT_ID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + 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
FROM #Temp

quote:

---

I have set the identity to on, b/c i currently have data in the table (tblProduct) and I am inserting all the records not in that field from a table named Complete_Products.

---

quote:

---

Originally posted by kory27

Ok. So i tried the following, which gave me the following error;

Set Identity_Insert tblProduct on
INSERT INTO tblProduct
(productId,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT Complete_products.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
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL

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

The way that is written, I believe it is only trying to insert records in Complete_products and not in the tblProducts. So I am thinking that it is trying to insert the ID from Complete_Products and not inserting the new record with the next available ID(that is my PK and it is Identity) That said, I then tried the later post of visakh16 and tried this query;

DECLARE @MaxId int

SELECT @MaxID=MAX(PRODUCT_ID)
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
(PRODUCT_ID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + 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
FROM #Temp

That gave this error;

Msg 102, Level 15, State 1, Line 6
Incorrect Syntax near '1'.

Sorry for all the questions, but each time i try to run this, the initial failure can take a while to tell me. Thanks again all.

---

quote:

---

Originally posted by kory27

No it is saying all the fields that come from Complete_Products can not be bound. That is a first for me in my learning curve here.

---

INSERT INTO tblProduct
(PRODUCT_ID,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