data import question

Hi,
I am writing a script for a client to update TableA using the data in
TableB. I know how to write an UPDATE query to do this. But the client wants
an ability to add or remove fields that they update using this script. I
know I can do this by letting the client enter the names of the source and
destination fields in a custom table and generate a dynamic query to update
the provided columns. But the problem is there are large number of fields
and sql has the limit of 8000 characters on variables write a dynamic query.
So writing a dynamic query is not an easy option either. So my question is
how do I solve this problem. Please not that we need to update 150+ fields
in 500,000+ records using this script. So it also needs to be as fast as
possible.
Thanks in advance.helpful sql wrote:

> Hi,
> I am writing a script for a client to update TableA using the data in
> TableB. I know how to write an UPDATE query to do this. But the client wan
ts
> an ability to add or remove fields that they update using this script. I
> know I can do this by letting the client enter the names of the source and
> destination fields in a custom table and generate a dynamic query to updat
e
> the provided columns. But the problem is there are large number of fields
> and sql has the limit of 8000 characters on variables write a dynamic quer
y.
> So writing a dynamic query is not an easy option either. So my question is
> how do I solve this problem. Please not that we need to update 150+ fields
> in 500,000+ records using this script. So it also needs to be as fast as
> possible.
> Thanks in advance.
Here's one possibility:
UPDATE A
SET
col1 = CASE WHEN @.col1 = 'Y' THEN B.col1 ELSE A.col1 END,
col2 = CASE WHEN @.col2 = 'Y' THEN B.col2 ELSE A.col2 END,
col3 = CASE WHEN @.col3 = 'Y' THEN B.col3 ELSE A.col3 END
..
WHERE ...?
David Portas
SQL Server MVP
--|||Dynamic SQL can take several variables to exceed the 8000 byte limit.
DECLARE @.X1 VARCHAR(8000), @.X2 VARCHAR(8000)
EXEC(@.X1 + @.X2)
Andrew J. Kelly SQL MVP
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eC2Vg$IDGHA.3748@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing a script for a client to update TableA using the data in
> TableB. I know how to write an UPDATE query to do this. But the client
> wants an ability to add or remove fields that they update using this
> script. I know I can do this by letting the client enter the names of the
> source and destination fields in a custom table and generate a dynamic
> query to update the provided columns. But the problem is there are large
> number of fields and sql has the limit of 8000 characters on variables
> write a dynamic query. So writing a dynamic query is not an easy option
> either. So my question is how do I solve this problem. Please not that we
> need to update 150+ fields in 500,000+ records using this script. So it
> also needs to be as fast as possible.
> Thanks in advance.
>