Deleting rows

What tool are you using?

>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>
When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?
sql

Deleting rows

What tool are you using?

>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
>
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?

Delete records that don exist in the destination

Hi all,

I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.

The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?

Am I correct in the way I am doing or there is something better to handle this?

Thanks in advance

MShetty wrote:

Hi all,

I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.

The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?

Am I correct in the way I am doing or there is something better to handle this?

Thanks in advance

That sounds like it will work. You basically need to compare the source and destination. Any records that are in the destination but not the source have to be removed - it sounds as if that is what you are attempting.

They can be deleted using an OLE DB Command. or you can push the records to be deleted into a temporary table and delete them using an Execute SQL Task.

-Jamie

|||

Hi Jamie,

Thx for the comments, I started working exactly the same way, but now am into an issue here. I am starting with two small tables as the first step. The table has this schema.

UserId (int) (PK)

UserName (varchar)

IsActive (bit) i am taking two DFT's one each to the two Databases and the selected records are passed onto a Merge Join. UserId is the join key here. Now the records that are having a match already are returned from this component right ? If I make the join type as a Ful Outer JOin and pass on to conditional split transform, how do I get the records that are in Destination DB but were deleted from the Source, I am just getting the newly added records and the updated one's. Please guide..

Thanks a lot

|||

I don't think you need a fullt outer join, just a left join will do it (with the incoming data on the left input).

Described here:

Get all from Table A that isn't in Table B
(http://www.sqlis.com/default.aspx?311)

-Jamie

|||

Thx for the inputs Jamie.

My problem of deleting the records in the destination that were removed from the Source DB was solved by using an IsNull (SrcTable.PK) in a conditional split and then direct those rows to an OLE DB Command Component.