Solved

SQL check if ID exists and process different insert statement

Posted on 2014-10-13
4
207 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 48

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 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