clean up data - when process runs next time

In a integration project I am moving data from A to B.

First time is fine - since table B is empty.

However next time I run the process, I would like to delete all records in B before I run the project again.

What is the best way to delete / clean up data when you re run the process ?

Cheers, T

Issue a DELETE or TRUNCATE from an Execute SQL Task.

-Jamie

Clarification on NULL values in the records.

Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>
>.
>|||Answered in .programming.
Please don't multi-post.
David Portas
SQL Server MVP
--

Clarification on NULL values in the records.

Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>> Hi All
>> Please write me the reason for
>> SQL:
>> --
>> select * from table1
>> where Required <> 'Y'
>> the result set is empty.
>> Table Schema is:
>> --
>> CREATE TABLE [dbo].[Table1] (
>> [Names] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Required] [nvarchar] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>> Insert Statement is is:
>> --
>> insert into table1 ([Names],Required)
>> values('Test1',null)
>> insert into table1 ([Names],Required)
>> values('Test2','Y')
>> insert into table1 ([Names],Required)
>> values('Test3',null)
>> insert into table1 ([Names],Required)
>> values('Test4','Y')
>> insert into table1 ([Names],Required)
>> values('Test5',null)
>> Is there any option I need to get the appropriate result
>> set?
>> B'coz my result set expected is
>> Test1 NULL
>> Test3 NULL
>> Test5 NULL
>
>.
>|||Answered in .programming.
Please don't multi-post.
--
David Portas
SQL Server MVP
--sqlsql

choosing a the column that is not null

I have two columns in a table. For every record, one column contains a value and the other one is null. Which one is filled and which one is empty differs per record. In my query I only want to retrieve the column that contains the value, but not the other one. Is it possible to do this with a standard tsql function? Other solutions are also welcome.REfer IS [NOT] NULL from BOL.|||I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.|||Originally posted by jora
I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.

You might want to take a look at the function COALESCE() which is designed to solve the problem you have.

The function is also part of the ANSI standard and is supported by many RDBMS.

Checking that an object is not empty

Hi,

Does anyone know the correct syntax to check the contents of an object variable. I want to do this as part of a constraint,

TIA

R

Variables of type Object are not support in expressions -

"The data type of variable "User::Variable" is not supported in an expression."

|||

Thanks for your reply.

I have a problem then. I want to pass in this object to use in an ExecuteSQL task, as my input parameter. However, because of some conditional execution, there may not be anything to update. This means that the object has nothing in it, and at the moment. This is causing my task to fail.

Any ideas?

|||

You could read the object variable inside a script task, and check for it equalling Nothing.

Then set a boolean variable indicating whether it is populated or not and put an expression on the precedence constraint that checks the boolean variable to see whether it should execute or not.

-Jamie

Checking if Value from a column is blank in SQL Server 2000

Hi

I want to check if the values of a column from a table is blank (i.e. empty but not null) . If it is blank, then I want to replace it with some other text. Is there some function like, Isnull or coalesce in SQL Server 2000, with which I can check if the value is blank and replace it. I want to use it as part of query so, I do not wish to use if trim(...) etc.

If any one has an solution, please do tell me.

Regards

Vineed

Here it is,

Code Snippet

Create table #Data(

ValueColumn varchar(100)

)

Insert Into #Data Values(' ');

Insert Into #Data Values('');

Insert Into #Data Values(' ');

Insert Into #Data Values(' ');

Insert Into #Data Values(NULL);

Insert Into #Data Values('Some value');

Select Case When ValueColumn='' Then '(Empty)' Else ValueColumn End From #Data

Checking if String is NULL or EMPTY in SQL

I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.

IFISNULL(@.param)OR @.param =''THEN doSomething...

(Note the two apostrophes ' and ', not a quote mark!)

|||

Books online (help files that comes with SQL) is an amzing little app. Below is an example and the Syntax.

USE pubsGOSELECTAVG(ISNULL(price, $10.00))FROM titlesGOISNULL ( check_expression , replacement_value )

|||

Create Procedure mySpNameHere

@.InputValueHere VARCHAR(50) = NULL

AS

IF @.InputValue IS NULL OR @.InputValue = ''

/*Keep the old value*/

ELSE

/*Run the update statement here with your new value*/

|||

cheetahtech:

ISNULL ( check_expression , replacement_value )

Sorry. I got a blackout. Of course, ISNULL syntax is to be used in a query where you want to specify an alternative value, if the expression is NULL.

The correct way to check for NULL in a condition is IF @.Param IS NULL as rich freeman points out.