Thanks.

It worked.

But now stuck with another derived column problem for date

how to write select col from table 1 in derived column editor?

Hi,

Is there any other way I can validate my column if it contains valid date.

I have been using ISDATE() but my column has data like dmy (19031986)

so for this value...

Yes, have done the same way right after posting the query.

But somehow I find it tedious and incorrect.

Is there any other way I can acheive this.

what should use to replace "-" with NULL value.

I tried

REPLACE(col1, "-", " ") : col1

and

ISNULL(col) ? " " : col1

but no luck..

Col1 Col2 ...

sample data :

col1 col2

1 1

1 ...

well I can do that but then it involves billions of rows.. which will surely affect performance.

In addition, if I am able to accomplish this it will become more easier...

sorry for confusing..

but there are 6/7 tables involved and query is too complex.

May be i can have my question like this.

Can I use replace command (to add leading zeros)...

I would always prefer backup from SQL native tools unless there is business case/utmost priority to use 3rd party tools.

Though we have started using RedGate but it was after thorough...

Chandrakant_gaurav, yes, all the other tools options have been explored.

Tom, its a good idea and try my hand on this. will surely keep you posted for the results

we cant use reporting services.. 🙁

the destination server will have only sql engine and ssis

so i think it has to be done using TSQL or some other way

(and frankly...

requirements change ahhh!!!

I have disid and patid and need to generate mycol. when i use dense rank function it gives incorrect value for mycol = 3 (while it shud be...

would probably candidate for update.. but since it has to deal with billions records I was just thinking of doing it with select with some other fields being populated.

this gives result as 1,2,3, for same patid and disid while I need 1,1,1 for same patid and disid.

If disid changes for same patid it should increement by 1

Patid...