How do I insert into multiple tables bases on conditions?

I have a record that I want to insert into (2) tables. The first thing I want to do is see if a record already exists in the table for the user, if it does - I just want to skip over the insert.

next I want to do the same thing in the SW_REQUEST table. If there is a record in there for the member, I want to just skip the insert.

My code works as long as there isn't an existing record in the tables. Can someone give me a hand?

Here's what I have (and it doesn't work)

CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0'
 AS
IF EXISTS
(SELECT FName, LName, Address1, Zip from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Zip = @.Zip)
BEGIN
RETURN
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
Values
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
END
IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.@.Identity)
BEGIN
RETURN
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.@.Identity, @.Send_Updates)
END
GO

It looks like you are doing a RETURN if the record is not found in the MEMBERS table, so that the check of the SW_REQUESTS table never happens.

You might need this instead:

IF NOT EXISTS (SELECT FName, LName, Address1, Zip from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Zip = @.Zip)
 BEGIN
 INSERT INTO MEMBERS
 (FName, LName, Address1, Address2, City, State, Zip, Email)
 Values
 (@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
 END
IF NOT EXISTS (SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.@.Identity)
 BEGIN
 INSERT INTO SW_REQUESTS
 (MEMBER_ID, Send_Updates)
 Values
 (@.@.Identity, @.Send_Updates)
 END

Terri|||Thanks Terri.

I was thinking that the return stopped the logic and jumped to the next IF statement. I didn't realize it put the breaks on everything ;-)

How do i implement If Then Else Conditions

Hi All,

I'm very new to Integration Services. Self Learned this ETL tool based on my prior ETL tool knowledge.

Can you tell me how do write IF Then Else conditions for every column in my source and redirect to a single output?

Hey correction here ... i'm using flat file as my source

Thanks in Advance,

Suresh N

It would help if you could provide a more explicit example of the type of logic you're hoping to implement, but here is a response based on my best guess at what you're trying to accomplish.

FIrst, start with the Derived Column transformation in your data flow: http://msdn2.microsoft.com/en-us/library/ms141069.aspx. This transform is the tool of choice for adding new columns - including new columns based on conditional logic and the values in existing columns - to your data flow.

Next, build an expression using the SSIS conditional operator to implement the If Then Else logic you need: http://msdn2.microsoft.com/en-us/library/ms141680.aspx.

You can also use the Script Transformation to perform more complex If Then Else logic in the data flow, but I personally try to avoid this if I can, as it makes your packages more difficult to maintain.

Please let us know if this gives you what you need.

|||

Thanks for reply Matthew.

I'm not able to do. Still needed help.

I have a flat file as a source, for each column i need to detect for NULL values. If it is NULL then i need to populate "99" Else Value.

I'm trying the same in Derived column transformation, not succeeded.

Do SSIS have something where i could write If Then Else ?

thanks..

Suresh N

|||

Suresh,

You can use a derived column transformation in the data flow with an conditional expression like

ISNULL(column1) ? 99 : column1

http://msdn2.microsoft.com/en-us/library/ms137538.aspx

|||

Suresh N wrote:

Thanks for reply Matthew.

I'm not able to do. Still needed help.

I have a flat file as a source, for each column i need to detect for NULL values. If it is NULL then i need to populate "99" Else Value.

I'm trying the same in Derived column transformation, not succeeded.

Do SSIS have something where i could write If Then Else ?

thanks..

Suresh N

This is exactly what the Derived Column transform is designed to do. Can you please post some specific information about what you are doing and what results you're seeing. There's not a lot of helpful information in your post to assist in troubleshooting.

|||

Hi Rafael,

Thanks much for your answer.

This has made my job done.

Regards,

Suresh N

|||

If you have to do this for several files accross a couple of packages then this when I think you should consider a custom transform. Faster development and maintenance as well as the ability to control custom business rules in a single (external to packages) assembly. The use of 99 in a local business rule I believe.

how do I get the name of the table that fired a trigger?

I have a trigger set for INSERT conditions on a table and I want to use the table name in an argument within the trigger. Does anyone know of a way of obtaining that tablename that fired the trigger without having to hardcode it? (I want to be able to re-use the trigger code on various tables without having to alter it for each table)

Thanks

Colin

Here it is,

Code Snippet

Create table Main(i int)

Go

Create trigger main_trg on Main for insert

as

Declare @.TableName NVarchar(300)

Select

@.TableName = object_Name(parent_obj)

from

sysobjects where id=@.@.PROCID

Select @.TableName

Go

Insert Into main values(1)

|||Perfect - works like a charm.
2 questions though -
1. is there any significance to you using NVarchar(300) as the datatype?
2. Do you know the syntax needed to be able to use a variable like @.TableName in a select statement instead of hardcoding the table name?

Thanks again for your help.

Colin|||

1. Is there any significance to you using NVarchar(300) as the datatype?

Since the sql server objects are Unicode values, it always better to use the NVarchar datatype. Regarding Length I recommend to use 128. (sysname = Nvarchar(128)

2. Do you know the syntax needed to be able to use a variable like @.TableName in a select statement instead of hard coding the

You have to use the dynamic sql here,

Code Snippet

Exec(N'Select * From ' + @.TableName);

--or

Declare @.SQL as Nvarchar(4000);

Set @.SQL = N'Select * From ' + @.TableName

Exec sp_executesql @.SQL

|||OK, one last question!
What's the significance of the 'N' in EXEC(N'Select...... ?

Thanks|||

I think N come from Unicode

|||

Yes, exactly the regular ascii string will be enclosed in the single quote ‘.

To identify the Unicode values databases uses the N prefix.