How to find out what rows are not in a table

Hi

I have a problem where I must compair an import table with a local datatable and import rows that are missing and correct the rows that are different.

How to best do this?

I was hoping to avoid cursors

thanks

Walter

hi

what is simple is just refer to the database

and get the rows from current db and insert the sam in target database

use not in clause in source Db so u can take out the duplicates

I hope u ll be geting it right

TechiTawa

|||

walter_verhoeven wrote:

Hi

I have a problem where I must compair an import table with a local datatable and import rows that are missing and correct the rows that are different.

How to best do this?

I was hoping to avoid cursors

thanks

Walter

you need an upsert (update /insert) statement

unfortunately upsert is not a supported keyowrd in sql server right now but

there are work around.

see this links

http://sudheerpalyam.spaces.live.com/Blog/cns!1pKCMhBsSwPMevqFfdi-3JgQ!198.entry

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58353

How to find nth lowest value in a row

Morning all,
I have a table with 500+ fields of numeric data (a time series of values) I
import from an external system. I need to find the 5th and 6th lowest values
in a given row to perform some calculations.
I currently do this by transposing the data row ie turning the row into a
column sorting it and then using row_num to get the 5th and 6th lowest
values. This transpose is a real bottleneck on my process.
Does anyone have any alternatives to my method ? All suggestions gratefully
received.
Thanks
WOn Thu, 16 Aug 2007 17:56:35 -0700, willcas wrote:
>Morning all,
>I have a table with 500+ fields of numeric data (a time series of values) I
>import from an external system. I need to find the 5th and 6th lowest values
>in a given row to perform some calculations.
>I currently do this by transposing the data row ie turning the row into a
>column sorting it and then using row_num to get the 5th and 6th lowest
>values. This transpose is a real bottleneck on my process.
>Does anyone have any alternatives to my method ? All suggestions gratefully
>received.
Hi W,
Perhaps you could transpose the data during the import and store it in a
more relational way?
The only other alternative would be a really very nasty (and long!) CASE
epxression that I won't even begin to think about <shudder>.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W
I would treat this as a staging table and transform the data to a properly
normalized model. Then write your query against that new model.
Perhaps better still, do the transformation before the load (using
Integration Services for example).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can't you use unpivot for the transposition?
"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W