Solved

SQL check if ID exists and process different insert statement

Posted on 2014-10-13
4
201 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
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:ScottPletcher
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL:  Embedding a CTE 5 31
Export import database 4 40
Generate Weekly Schedule 15 13
Error running stored procedure 11 10
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 article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now