INSERT INTO

Question

INSERT INTO

2Tall

SSChampion

Points: 14644
More actions
November 18, 2008 at 5:22 am

#193874

Hi. I have been asked to insert all Products from table Products into ProductProductCategories where table ProductProductCategories does not have an entry .
Currently table ProductProductCategories contains 47 records (manually entered via front end application).
I have been asked to insert the remaining 300000 product records into ProductProductCategories then set the ProductProductCategories.ProductCategory equal to '5'.
Initially I thought I would do the insert then run an UPDATE to set the value to 5.
However I am unable to get my INSERT to run. I receive:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ProductProductCategories.Product" could not be bound.
INSERT INTO [Training].[dbo].[ProductProductCategories]
([Product])
SELECT
Product
FROM [Training].[dbo].[Products]
WHERE Products.Product <> ProductProductCategories.Product
Where am I going wrong?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Phil

When performing an UPDATE ... FROM or an INSERT ... FROM, it will save you heaps of time to check the SELECT separately:

SELECT

Product

FROM [Training].[dbo].[Products]

WHERE Products.Product <> ProductProductCategories.Product

I reckon this won't run as it stands.

A properly-specified join will make it easier to read:

SELECT p.Product

FROM [Training].[dbo].[Products] p

LEFT JOIN [Training].[dbo].[ProductProductCategories] c ON c.Product = p.Product

WHERE c.Product IS NULL

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks Chris. You were correct.

I ran the following after confirming your code executed OK:

INSERT INTO [Training].[dbo].[ProductProductCategories]

([Product])

SELECT p.Product

FROM [Training].[dbo].[Products] p

LEFT JOIN [Training].[dbo].[ProductProductCategories] c ON c.Product = p.Product

WHERE c.Product IS NULL

This returned error:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProductProductCategories_ProductCategory". The conflict occurred in database "DB1", table "dbo.ProductCategories", column 'ProductCategory'.
The statement has been terminated.

Should I remove the constraint, run the insert, run the update then put the constraint back on or is their a cleaner way of doing it?

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Phil

This should do the trick, because it supplies the value of ProductCategory:

INSERT INTO [Training].[dbo].[ProductProductCategories]

([Product], ProductCategory)

SELECT p.Product, 5 AS ProductCategory

FROM [Training].[dbo].[Products] p

LEFT JOIN [Training].[dbo].[ProductProductCategories] c ON c.Product = p.Product

WHERE c.Product IS NULL

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

2Tall SSChampion Points: 14644 More actions · Answer 4

Chris many thanks, exactly what I was after.

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper