I would stage the new data into a 'termporary' table that you truncate before each load. Once loaded into that table, then you just run set-based SQL operations between this...

Example:

UPDATE

    contact2

    SET udefcon =

    CASE

        WHEN key1 IN('APP', 'ADM', 'CNF', 'DNY', 'ENR')

        THEN 100 --Add 100 points if key1 has a VALUE in the...

Use of a UDF in the SET portion of an Update is highly inefficient. It becomes almost like a cursor-based operation, because the function and the joins within it get...

Start Menu->Programs->Microsoft SQL Server->Books Online

Read the BOL section titled "BEGIN DISTRIBUTED TRANSACTION"

And also the section titled "SET REMOTE_PROC_TRANSACTIONS"

Use PatIndex() to look for start of numerics, and subsequent start of non numerics.

Select PatIndex('%[0-9]%', YourText)   -- Get the 1st numeric character

Select PatIndex('%[^0-9]%', SubString(YourText, PatIndex('%[0-9]%', YourText) + 1, 999   ) ...

You're embedding the literal string @String into your SQL string, so the error is entirely as expected. @String won't be resolved to what it contains if it's inside the single...

Are these questions from a homework assignment ? Use of Northwind and basic nature of the questions certainly looks that way ...

You aren't initializing @To.

That means it is NULL.

NULL concatenated with anything is always NULL

DECLARE @To nvarchar(1000)

SELECT @To = ''  -- Initialize to empty string

See BOL under INSERT. If you aren't inserting ALL columns, you need to specify a column list:

INSERT

 dbo.Diagnosis_Billing_Codes

  (BillingCode, Diagnosis_Code_ID, Profile_ID)

SELECT

 Billing_Code,

 Diagnosis_Code_ID,

 Profile_ID

FROM

 #temp

From looking at the exact same topic on the sqlteam forums, the fundamental problem with this is that the poster thinks the tables are "sorted" and doesn't realise that SQL...

You'd need to use dynamic SQL to achieve this - build the SQL for the 'SELECT INTO ...' into a string and EXEC the resulting string.

Alternatively, build into a staticly...

>>Is there better logic to compare the dates?

Depends on keys and data. That's why I asked about OwnerID as the key. Does OwnerID only exist once in each table as...

The issue is the not equal to <>

This is an expensive join operation and causes a table scan.

What is the primary key of both tables. Is it just OwnerID...

Does that even compile ?

The table you're updating is not in the FROM anywhere ...