Solved

SQL check if ID exists and process different insert statement

Posted on 2014-10-13
4
211 Views
Last Modified: 2014-10-15
The below statement should determine which part is called dependent on if the ProductID exists or not,
so if the ProductID does not exists i want query 1 to call, otherwise if it does exist i want query 2
to call, my issue lies within the SELECT statement at the top, what am I doing wrong

IF NOT EXISTS(SELECT TOP 1 ProductID FROM ProductTable)
BEGIN
--QUERY 1
INSERT
(ProductName, UnitPrice)
SELECT
ProductView.ProductName,
ProductView.UnitPrice
FROM ProductView
INNER JOIN ProductTable ON ProductView.ProductID = ProductTable. ProductID
WHERE ProductID = (SELECT TOP 1 ProductID FROM ProductTable ORDER BY changedate DESC)
AND NOT EXISTS(SELECT 1 FROM ProductTable WHERE ProductView.ProductID = ProductTable.ProductID)
END
ELSE
--QUERY 2
INSERT
(ProductName, UnitPrice)
SELECT
ProductView.ProductName,
UnitPrice = 0.00
FROM ProductView
INNER JOIN ProductTable ON ProductView.ProductID = ProductTable. ProductID
WHERE ProductID = (SELECT TOP 1 ProductID FROM ProductTable ORDER BY changedate DESC)
0
Comment
Question by:wint100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 40377446
Don't you need a where clause on that select?  This will never fire Query 1 if there are any items at all in ProductTable.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40377504
You must provide a table name to INSERT to:

--QUERY 1
INSERT <table_name_must_go_here>
(ProductName, UnitPrice)
0
 
LVL 1

Author Comment

by:wint100
ID: 40379492
Thanks for you help, my modified code is as follows, but an error still exists. The If statement just doesn't get called:

IF NOT EXISTS(SELECT TOP 1 PartReference FROM ProductTable)
BEGIN
--QUERY 1
INSERT INTO ProductTable
(ProductName, UnitPrice)
SELECT
ProductView.ProductName,
ProductView.UnitPrice
FROM ProductView
INNER JOIN ProductTable ON ProductView.ProductID = ProductTable. ProductID
WHERE ProductID = (SELECT TOP 1 ProductID FROM ProductTable ORDER BY changedate DESC)
AND NOT EXISTS(SELECT 1 FROM ProductTable WHERE ProductView.ProductID = ProductTable.ProductID)
END
ELSE
--QUERY 2
INSERT INTO ProductTable
(ProductName, UnitPrice)
SELECT
ProductView.ProductName,
UnitPrice = 0.00
FROM ProductView
INNER JOIN ProductTable ON ProductView.ProductID = ProductTable. ProductID
WHERE ProductID = (SELECT TOP 1 ProductID FROM ProductTable ORDER BY changedate DESC)

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40381434
try this as a test:

select case when NOT EXISTS(SELECT TOP 1 PartReference FROM ProductTable) then 'not exists' else 'exists' end

Query 1 cannot be executed unless you have deleted all records from [ProductTable]

Please reconsider lludden's point: You need a where clause in the very first part of our query

IF NOT EXISTS(SELECT TOP 1 PartReference FROM ProductTable WHERE SOME CONDITION IS HERE)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question