Trying to add/update salutations

Hey Clarke,

It looks like you are expecting row-by-row behavior from your IF EXISTS statement, but that's not what happens. IF EXISTS resolves to a boolean true or false based on whether the subquery returns ANY rows at all. Since you are just looking for any matching customers, and your local table always has some in your testing, your IF statement always runs and the else block never runs.

The way you've decided to write this query would actually be a pretty good candidate for a MERGE statement. Something like (untested, but this is what the syntax looks like):

MERGE dbo.TX_CUST_SAL s
  USING lt_salutation_import_test i
    ON s.signor = 63 AND s.customer_no = i.customer_no
  WHEN MATCHED THEN UPDATE
    SET -- column updates here
  WHEN NOT MATCHED THEN INSERT (columns) -- etc
;

The tricky part is going to get the numbering for the inserted salutations right. If ROWCOUNT doesn't work inside the insert statement, you could use a CTE in front of the MERGE to add a new_salutation_no column to your input table based on the rowcount added to the max value of salutation_no already in the TX_CUST_SAL table.

However, my personal preference when I have to deal with T_NEXT_ID is actually to use a cursor, and call dbo.AP_GET_NEXTID_function once per row only when I need to. Remember -- cursors aren't evil, they're just overused by programmers who don't understand how to write set-based code, and SQL Server used to not have the FAST_FORWARD option. For one-off updates like this, I think it's perfecty reasonable to trade off slightly on performance in favor of code comprehensibility.

Thanks, Nick.  I am trying this, and if it works I will post it all.

At last, it worked!  Thanks again, Nick!  There is stuff that I know is not done as slickly as it could be if I had more experience, but it worked in Test.  I am going to get a cup of coffee before trying it in Live.

Import the file containing customer_no, Salutation_1_Line1, and Salutation_1_Dear as lt_salutation_import

I am less trusting now, so first I test duplicates in the import file and deal with them

SELECT customer_no, count(*)
FROM dbo.lt_salutation_import
group by customer_no
having count(*)>1
GO

Then I rid the import file of any customer_no not existing in T_CUSTOMER

delete
FROM lt_salutation_import
where customer_no not in (SELECT customer_no from T_CUSTOMER)
GO

I seemed to need to pause after this next step of adding a column to the import file for the salutation_no

BEGIN TRANSACTION;
ALTER TABLE dbo.lt_salutation_import
ADD salutation_no int;
COMMIT TRANSACTION;
GO

Everything else can run.  This is for a specific salutation where signor=63.

declare @sal_no int
declare @number_rows int
SELECT @number_rows=count(1) FROM dbo.lt_salutation_import;
exec @sal_no = [dbo].ap_get_nextid_function @type = 'CS', @increment = @number_rows;

BEGIN TRANSACTION;
INSERT INTO lt_salutation_import (customer_no, Salutation_1_Line1, Salutation_1_Dear, salutation_no)
SELECT customer_no, Salutation_1_Line1, Salutation_1_Dear, @sal_no + row_number() over (order by (customer_no))
FROM lt_salutation_import
COMMIT TRANSACTION;

BEGIN TRANSACTION;
delete FROM dbo.lt_salutation_import
where salutation_no is null
COMMIT TRANSACTION;

BEGIN TRANSACTION;
MERGE dbo.TX_CUST_SAL s
USING lt_salutation_import i
ON s.signor = 63 AND s.customer_no = i.customer_no
WHEN MATCHED THEN UPDATE
SET s.esal1_desc = i.Salutation_1_Line1, s.lsal_desc = i.Salutation_1_Dear, s.default_ind = 'N', s.label = 'Y'
WHEN NOT MATCHED THEN
INSERT (signor, customer_no, esal1_desc, lsal_desc, default_ind, label, salutation_no)
VALUES (63, customer_no, Salutation_1_Line1, Salutation_1_Dear, 'N', 'Y', salutation_no);
COMMIT TRANSACTION;

At last, it worked!  Thanks again, Nick!  There is stuff that I know is not done as slickly as it could be if I had more experience, but it worked in Test.  I am going to get a cup of coffee before trying it in Live.

Import the file containing customer_no, Salutation_1_Line1, and Salutation_1_Dear as lt_salutation_import

I am less trusting now, so first I test duplicates in the import file and deal with them

SELECT customer_no, count(*)
FROM dbo.lt_salutation_import
group by customer_no
having count(*)>1
GO

Then I rid the import file of any customer_no not existing in T_CUSTOMER

delete
FROM lt_salutation_import
where customer_no not in (SELECT customer_no from T_CUSTOMER)
GO

I seemed to need to pause after this next step of adding a column to the import file for the salutation_no

BEGIN TRANSACTION;
ALTER TABLE dbo.lt_salutation_import
ADD salutation_no int;
COMMIT TRANSACTION;
GO

Everything else can run.  This is for a specific salutation where signor=63.

declare @sal_no int
declare @number_rows int
SELECT @number_rows=count(1) FROM dbo.lt_salutation_import;
exec @sal_no = [dbo].ap_get_nextid_function @type = 'CS', @increment = @number_rows;

BEGIN TRANSACTION;
INSERT INTO lt_salutation_import (customer_no, Salutation_1_Line1, Salutation_1_Dear, salutation_no)
SELECT customer_no, Salutation_1_Line1, Salutation_1_Dear, @sal_no + row_number() over (order by (customer_no))
FROM lt_salutation_import
COMMIT TRANSACTION;

BEGIN TRANSACTION;
delete FROM dbo.lt_salutation_import
where salutation_no is null
COMMIT TRANSACTION;

BEGIN TRANSACTION;
MERGE dbo.TX_CUST_SAL s
USING lt_salutation_import i
ON s.signor = 63 AND s.customer_no = i.customer_no
WHEN MATCHED THEN UPDATE
SET s.esal1_desc = i.Salutation_1_Line1, s.lsal_desc = i.Salutation_1_Dear, s.default_ind = 'N', s.label = 'Y'
WHEN NOT MATCHED THEN
INSERT (signor, customer_no, esal1_desc, lsal_desc, default_ind, label, salutation_no)
VALUES (63, customer_no, Salutation_1_Line1, Salutation_1_Dear, 'N', 'Y', salutation_no);
COMMIT TRANSACTION;