SQL Statement to Insert from view

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 2000 Forums

SQL Server Development (2000)

SQL Statement to Insert from view

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2006-04-17 : 15:36:24

hi all, not sure if this is the right forum, but i just need a little help with an SQL statement.

I want to insert values from a view back to the table on which the view is based.

But I dont want to insert records if the SupplierCode is the same between the view and table. Then I would end up with duplicate records because SupplierCode is not a primary key. ProductID is the primary key.

So I figured out something like this

INSERT INTO dbo.Products
(SupplierCode, ProductName, Price)

SELECT
SupplierCode, ProductName, Price

FROM
dbo.ProductView

WHERE
dbo.ProductView.SupplierCode != dbo.Products.SupplierCode

But the above does not work. It says something like the multi-part identifier "dbo.Products.SupplierCode" cannot be bound.

I need to ensure that only records from the ProductView whose SupplierCode does not match any record in the Products table should be inserted. Because if the SupplierCode already exists, then I only need to perform an update not insert a duplicate record.

Any help would be massively appreciated! BTW I tried the same method above using a Staging Table instead, but the same error comes up.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-17 : 15:49:57

You can't reference dbo.Products in your WHERE clause if it is not included in your SELECT clause.

Here are two methods to do what you are trying to do:

INSERT INTO dbo.products
	(suppliercode,
	productname,
	price)
SELECT	suppliercode,
	productname,
	price
FROM	dbo.productview
WHERE	not exists (select * from dbo.products where products.suppliercode = productview.suppliercode)

--or--

INSERT INTO dbo.products
	(suppliercode,
	productname,
	price)
SELECT	suppliercode,
	productname,
	price
FROM	dbo.productview
	left outer join dbo.products on productview.suppliercode = products.suppliercode
WHERE	dbo.products.suppliercode is null

token
Posting Yak Master

133 Posts

Posted - 2006-04-18 : 17:01:45

Absolutely outstanding! Thank you ever so much for the help blindman. I can't understand how you guys come up with stuff like this. I've been reading through a book called SQL Cookbook and nowhere does it mention anything like this.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-18 : 23:46:03

To attain this secret DBA knowledge, you must attend and pay for a series of purification ceremonies.

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-04-19 : 05:56:48

quote:
Originally posted by token

Absolutely outstanding! Thank you ever so much for the help blindman. I can't understand how you guys come up with stuff like this. I've been reading through a book called SQL Cookbook and nowhere does it mention anything like this.

practice practice practice

when life hands you lemons, ask for tequila and salt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources