How to retrieve the IDENTITY of the ROW if the 'IF EXISTS' function return a TRUE

Question

Post reply

How to retrieve the IDENTITY of the ROW if the 'IF EXISTS' function return a TRUE

John Peter Fernandes

SSC Veteran

Points: 255
More actions
May 2, 2010 at 6:56 am

#234470

I need to INSERT data into a table only if the data does not already exist, but if it does i need to return the ID for that row of data.
I tried using the following code
cmdTag.CommandText = "IF EXISTS (SELECT ID FROM mt_tags WHERE tag=@tag) RETURN ELSE INSERT INTO mt_tags(tag,type) VALUES (@tag, @type); SELECT @NewTagID = ID FROM mt_tags WHERE tag=@tag AND type=@type;"
But i have had no luck what so ever.
My Main GOAL:
Check if a TAG exists in the table.
IF EXISTS = TRUE it should return the ID for that TAG << this Fails, always returns -1
IF EXISTS = FALSE it should INSERT it into the table << this work if i add the 'SELECT @NewTagID = SCOPE_IDENTITY();' a to the end of the abouve query.
Pease let me know if this is possible or not. I failed an achieving this with a stored procedure as well.
A SQL QUERY or a SQL PROCEDURE will help.
Thanks in advance.
John
John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

John, please post the table creation script.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

John Peter Fernandes SSC Veteran Points: 255 More actions · Answer 2

Here is the table.

CREATE TABLE [dbo].[mt_tags] (

[ID] [numeric] (18,0) IDENTITY (1,1) NOT NULL,

[tag] [varchar](100) NOT NULL

) ON [PRIMARY]

Very plain and simple.

Thanks.

John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC

Sergiy SSC Guru Points: 110208 More actions · Answer 3

INSERT INTO mt_tags(tag,type)

SELECT @tag, @type

WHERE NOT EXISTS (SELECT ID FROM mt_tags WHERE tag=@tag)

SELECT @TagID = ID

FROM mt_tags

WHERE tag=@tag

@TagID will contain correct ID regardless if it's new or old.

And you need to sort it out with Type.

You must check for it either everywhere or nowhere.

_____________
Code for TallyGenerator

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Ok, couple of things...

Your table creation script does not contain a column called "type"??

Do you have any indexes/constraints on the table?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

John Peter Fernandes SSC Veteran Points: 255 More actions · Answer 5

Thanks bro.

i'll test it and get back to you asap.

John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC

John Peter Fernandes SSC Veteran Points: 255 More actions · Answer 6

i apologize for that...

the script does contain the TYPE being defined.. i just forgot to write it here.. thanks though. 🙂

mister.magoo (5/3/2010)
Ok, couple of things...
Your table creation script does not contain a column called "type"??
Do you have any indexes/constraints on the table?

John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

so, can you repost the creation script with all the columns?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

also, can you clarify if tag+type is unique or is just tag unique?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

John Peter Fernandes SSC Veteran Points: 255 More actions · Answer 9

USE [mtdb]

GO

/****** Object: Table [dbo].[mt_tags] Script Date: 05/03/2010 12:46:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[mt_tags](

[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[tag] [varchar](100) NOT NULL,

[type] [numeric](1, 0) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

John Peter Fernandes (5/3/2010)
i apologize for that...
the script does contain the TYPE being defined.. i just forgot to write it here.. thanks though. 🙂
mister.magoo (5/3/2010)
Ok, couple of things...
Your table creation script does not contain a column called "type"??
Do you have any indexes/constraints on the table?

John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC

John Peter Fernandes SSC Veteran Points: 255 More actions · Answer 10

Tag is not unique.

I am just using it so that distinguish for which type of article the tag was added.

Thanks

John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC

John Peter Fernandes SSC Veteran Points: 255 More actions · Answer 11

Sergiy.. thanks for this, this returns the value of the new TagId if inserted and the TagId of the already existing Tag.

however if it exists i get the TagID and a '0' and if it does not exist i get the new TagId and a '1'.

is there some way i can output only the TagId and not the result of the IF EXISTS?

Sergiy (5/3/2010)
INSERT INTO mt_tags(tag,type) SELECT @tag, @type WHERE NOT EXISTS (SELECT ID FROM mt_tags WHERE tag=@tag) SELECT @TagID = ID FROM mt_tags WHERE tag=@tag
@TagID will contain correct ID regardless if it's new or old.
And you need to sort it out with Type.
You must check for it either everywhere or nowhere.

John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

SET NOCOUNT ON

SELECT @NewTagID=ID FROM mt_tags WHERE tag=@tag and type=@type

IF @NewTagId IS NULL

BEGIN

INSERT INTO mt_tags(tag,type)

SELECT @tag, @type

SET @NewTagId = SCOPE_IDENTITY()

END

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

John Peter Fernandes SSC Veteran Points: 255 More actions · Answer 13

thanks a lot will test this right away. 🙂

John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC

John Peter Fernandes SSC Veteran Points: 255 More actions · Answer 14

i tested this, it work fine but it returns the TagID and appeneds a '-1' to it always.

mister.magoo (5/3/2010)
SET NOCOUNT ON SELECT @NewTagID=ID FROM mt_tags WHERE tag=@tag and type=@type IF @NewTagId IS NULL BEGIN INSERT INTO mt_tags(tag,type) SELECT @tag, @type SET @NewTagId = SCOPE_IDENTITY() END

John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC