To insert data only if it doesnot exist if exist show the table info and print a message

Question

To insert data only if it doesnot exist if exist show the table info and print a message

SQLTestUser

Hall of Fame

Points: 3997
More actions
September 14, 2010 at 4:12 pm

#229293

Basically i am trying to accomplish
Inserting data into a table only if it doesnot exist and if does to show me the content of the table and print a simple message in either case, but i can seem to accomplish this
If exists
(Select * from TableA where PKEY = '1' )
---Display the content of the table
Select * FROM TableA
Print 'Table A info'
Else
Insert Into TableA
(X,Y,Z)
Values(1,'testLink', TestValue)
Print 'Table has been inserted with the specified data'
GO
tHanks for the help

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

avi-631555 (9/14/2010)
Basically i am trying to accomplish
Inserting data into a table only if it doesnot exist and if does to show me the content of the table and print a simple message in either case, but i can seem to accomplish this
If exists
(Select * from TableA where PKEY = '1' )
---Display the content of the table
Select * FROM TableA
Print 'Table A info'
Else
Insert Into TableA
(X,Y,Z)
Values(1,'testLink', TestValue)
Print 'Table has been inserted with the specified data'
GO
tHanks for the help

I guess all that's missing are the BEGIN..END statements to define the IF..ELSE blocks:

If exists

(Select * from TableA where PKEY = '1' )

BEGIN

---Display the content of the table

Select * FROM TableA

Print 'Table A info'

END

Else

BEGIN

Insert Into TableA

(X,Y,Z)

Values(1,'testLink', TestValue)

Print 'Table has been inserted with the specified data'

END

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 2

I get an error with the begin end as well

Msg 156 level 15 state 1 line 7

Incorrect syntax near the keyword Else

Msg 102 level 15 state 1 line 12

Incorrect syntax near END

Is there any other way to do this

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

Can you post exactly what you run. The syntax Lutz showed should work.

It's

IF ()

begin

end

else

begin

end

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 4

I think i got it the problem was that

on the else statement where the insert is specified i had to have [] specified for every column instead of simply having () i have no idea why

but once i added [] the table will update only if the the values are non existent if not the table infor is provided

If exists

(Select * from TableA where PKEY = '1' )

BEGIN

---Display the content of the table

Select * FROM TableA

Print 'Table A info'

END

Else

BEGIN

Insert Into TableA

([X],[Y],[Z])

Values(1,'testLink', TestValue)

Print 'Table has been inserted with the specified data'

END

thanks for the help

LutzM SSC Guru Points: 107049 More actions · Answer 5

You should still get the inserted message when successful.

The square bracket issue is weird. Possible any "non-standard" column names? (e.g. including space, comma or any other fancy stuff?)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 6

no there are no fancy columns, and i do get the inserted message, May be i should show the table info after the insert to show me the insert results. I will let u know what comes out

thanks

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 7

Thanks the Merge was really helpful, will use it

thanks again