This is the best I could do.

It's still hard to read and understand.

Can somebody make it more readable and easier to follow?

SELECT DISTINCT

b.boxid,

b.boxid,

b.dispenserid,

b.boxqty,

b.formqty,

b.datestamp,

b.[timestamp],

b.linenum,

b.operator,

b.shipperid,

b.wholesalerid,

0.00 as shipcost,

b.crxflag,

b.actualqty,

b.status,

b.complaint,

b.condition,

b.weight,

b.exception,

b.receipt,

b.creditqty,

b.perc_notified,

b.perc_invoiced,

b.is_collect,

b.waybill,

b.clin_trial,

b.totalboxes,

b.OpenBy

FROM

USFRET..box b

INNER JOIN USFRET..dispenser...

Sorry guys.

I posted the wrong query.

I extracted it from DTS and wasn't realizing that

it was transformation from UFPIMS to USFRET.

Here is the correct code:

select distinct b.boxid,b.boxid,b.dispenserid,b.boxqty,b.formqty,b.datestamp,b.[timestamp],b.linenum,b.operator,b.shipperid,

b.wholesalerid,0.00 as shipcost,b.crxflag,b.actualqty,b.status,b.complaint,b.condition,b.weight,b.exception,b.receipt,

b.creditqty,b.perc_notified,b.perc_invoiced,b.is_collect,b.waybill,b.clin_trial,b.totalboxes,b.OpenBy

from box...

Let's say you have 7GB backup file.

With LiteSpeed how small it could be?

And haw fast I can decompress it and start my RESTORE?

Pyotr,

You are absolutely right!

This stupid query will always return no records

.....

FROM

USFPIMS..box b

INNER JOIN USFPIMS..dispenser d on b.dispenserid = d.dispenserid

INNER JOIN USFPIMS..scan s on b.boxid = s.boxid

WHERE ...

Grasshopper,

That's not my goal.

My goal is to understand if I can handle "Object does not exist" type of errors within transaction

and rollback it in case of error.

Rob

Copy the backup files to local first

and then run RESTORE looks like a good style, I agree.

But we only have 21 GB space on this DEV VMWare box.

BEGIN TRY

set nocount on

BEGIN TRAN

print 'BEGIN TRAN'

....other code

INSERT INTO

SELECT ...

FROM

USFPIMS..prodextend mpe

INNER JOIN USFPIMS..product mp ON ISNULL(mpe.din,'') = ISNULL(mp.din,'') AND ISNULL(mpe.pkgsize,0) = ISNULL(mp.pkgsize,0)

INNER JOIN USFPIMS..client mc ON ISNULL(mp.client,'') =...

We don't have "Common" database.

Should I create one to store common objects?

User defined proc in master

so that you can call it from any database.

In this case I don't have to create the same error_handing proc in each database.

Makes sense?

GSquared,

But I do that? I call error handling stored procedure?

Don't I?

BEGIN CATCH

ROLLBACK TRAN

print 'ROLLBACK...

I used this schema:

BEGIN TRY

/*1*/DELETE FROM client

/*2*/INSERT INTO client

....

...

I love SQLServerCentral!

You can talk live to some big guys here! .. )

Thank you for advice Grant.

Rob

Guys,

Actually this statement below with UNION does the job:

select #md.dispencerid from #md inner join #mr mr1 on #md.dispencerid = mr1.dispencerid

UNION

select #md.dispencerid from #md left outer join #mr on #md.dispencerid =...

Guys,

Here is what I'm trying to get.

Table #md

dispencerid wholesaler

-----------------------

144 N

145 N

160 ...

I did a few tests

and finally decided to go with this version.

It now returns the same number of records as

the old "WHERE IN / NOT IN" code:

SELECT mpe.din, mpe.pkgsize

FROM

USFPIMS..prodextend...