Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

To insert data only if it doesnot exist if exist show the table info and print a message Expand / Collapse
Author
Message
Posted Tuesday, September 14, 2010 4:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 158, Visits: 492
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


Post #985934
Posted Tuesday, September 14, 2010 4:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 7,019, Visits: 12,915
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #985940
Posted Tuesday, September 14, 2010 4:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 158, Visits: 492
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
Post #985942
Posted Tuesday, September 14, 2010 4:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:02 PM
Points: 33,062, Visits: 15,176
Can you post exactly what you run. The syntax Lutz showed should work.

It's

IF ()
begin
end
else
begin
end








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #985944
Posted Tuesday, September 14, 2010 4:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 158, Visits: 492
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
Post #985951
Posted Tuesday, September 14, 2010 5:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 7,019, Visits: 12,915
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #985955
Posted Tuesday, September 14, 2010 5:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 158, Visits: 492
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
Post #985956
Posted Wednesday, September 15, 2010 10:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
Google up the MERGE INTO statement. It is a combination of an UPDATE and INSERT using a target table and a source table.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #986479
Posted Wednesday, September 15, 2010 10:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
Try this article:

http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #986487
Posted Wednesday, September 15, 2010 11:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 158, Visits: 492
Thanks the Merge was really helpful, will use it
thanks again
Post #986533
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse