IFEXIST and INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

IFEXIST and INSERT

Author

Topic

domo
Starting Member

23 Posts

Posted - 2009-06-16 : 06:54:10

We have an SQL address book bolted onto a CRM solution.

Id, Username, Name, Email, Company, DateTime

I need to insert a record where a company does not already have the Username 'EC'. I also need to auto increment the Id by 1 as each record is added (the app manages this as opposed to SQL - nothing I can do to change it).

Any help would be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 06:59:58

Something like this

-- Code starts here
IF EXISTS (SELECT * FROM Table1 WHERE UserName = 'EC')
	RETURN

DECLARE	@ID INT

SET TRANSACTION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT	@ID = MAX(ID)
FROM	Table1

SET	@ID = COALESCE(@ID + 1, 1)

INSERT	Table1
	(ID, UserName, Name, eMail, Company, DateTime)
VALUES	(
		@ID,
		'EC',
		'New name',
		'New email',
		'New company',
		GETDATE()
	)

COMMIT TRAN

E 12°55'05.63"
N 56°04'39.26"

domo
Starting Member

23 Posts

Posted - 2009-06-16 : 07:26:50

Thanks Peso,
Can you explain some of the code to me please.
SET TRANSACTION LEVEL SERIALIZABLE
BEGIN/COMMIT TRAN

I've worked with SQL for a little while but not used these statements before.

Thanks!

Domo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 07:29:48

The serializable transaction level is for making sure all transaction are handled "first in, first out".
This is to avoid some other user trying to insert a record in same table, and where you might compete for same ID.

Then I start the explicit transaction, do the insert and then commit the transaction.
Some things may be overkill, but this should be safe.

E 12°55'05.63"
N 56°04'39.26"

domo
Starting Member

23 Posts

Posted - 2009-06-16 : 07:40:51

Thanks for your help. I'll post back with the outcome.
Domo.

domo
Starting Member

23 Posts

Posted - 2009-06-16 : 08:47:06

Okay - I got that script working and its great.
However, I need to check the Username 'EC' does not exist against each Company.
When a company is located which does not have the EC Username - the Company value is concatenated with a text string to make a unique email address.

E.g.
Company = 123
Email Value = EC@123.com so this would simply be 'EC@' + Company + '.com'.

I'm struggling with the logic on how to accomplish this - though I imagine its something to do with the select statement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources