Conditional 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

General SQL Server Forums

New to SQL Server Programming

Conditional insert

Author

Topic

vafo
Starting Member

5 Posts

Posted - 2010-07-14 : 06:58:47

ok this may be just ridiculous, but I'm struggling with a type of conditional insert that is probably simple but I could use some help with.

SCENARIO:
two tables:
oportunities (ID_COMPANY,TEXT)
companies (ID_COMPANY,COMPANY_NAME)

when I insert an oportunity I input the text and company name, and what I want sql to do is:
-generate a new company ID on the companies table if it doesnt exist yet and then create the oportunity with the just created id
OR
-match the company name with the company ID on the companies table and insert the oportunity text and the matching ID

I do not want/not allowed to change the table definition;

The query would look something like this (you can be shocked but please help!)

BEGIN
	SET NOCOUNT ON;
	IF (SELECT * FROM dbo.EMPRESAS WHERE NM_EMPRESA = @empresa) = NULL
	BEGIN
		INSERT INTO	dbo.EMPRESAS VALUES (@empresa)
	END
	DECLARE @id_empresa int;
	SET @id_empresa = (SELECT ID_EMPRESA FROM dbo.EMPRESAS WHERE NM_EMPRESA = @empresa)

	INSERT INTO dbo.OPORTUNIDADES 
	VALUES	(@titulo,
			@id_empresa,
			@ocultarEmp,
			@texto,
			@vagas,
			'0'
			)
	
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-07-14 : 10:39:15

Here's one way assuming that id_empresas is an identity column:


declare @id_empresa int

select @id_empresa = id_empresa from dbo.empresas where nm_empresa = @empresa
if @id_empresa is null
begin
       insert dbo.empresas (nm_empresa)
       values (@empresa)
       set @id_empresa = scope_identity()
end

insert dbo.OPORTUNIDADES 
       (titulo,
	id_empresa,
	ocultarEmp,
	texto,
	vagas,
	<correct_column_name>)
VALUES	
       (@titulo,
	@id_empresa,
	@ocultarEmp,
	@texto,
	@vagas,
	'0')

Be One with the Optimizer
TG

vafo
Starting Member

5 Posts

Posted - 2010-07-15 : 04:34:19

Thank you, that worked just perfectly and I learned a bit more of coding sql =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources