I did a quick search and did not see anything in this forum specific to this problem:

Problem: In one statement (or whatever is most efficient if not possible), how do I update the rows in table A (based on a where clause), and insert into table B, the data from those same number of rows.

For example,

Table A: columns: id , lastName, department, branch
Table B: columns: id, errorMessage, lastName

UPDATE table A by settting branch = 'ETC' where department IS NULL

INSERT INTO table b the rows found in the above UPDATE statement


note: 1.To find the rows UPDATED in the first statement, it is not sufficient to search by branch='ETC', as that will encompass more rows.
2. I do not want to use triggers as these statements will be executed once in the entire process

Thanks!

You'll want to look into the OUTPUT clause.

Thanks!
THat seems like it'll do the trick

Another question:
A new requirement is to conditionally Insert/output some of the records which were updated.

I had tried adding a where clause or a condition statement with no luck.

Something like:

UPDATE c
SET c.column = case when a.id is null then 1 else 2 end,
OUTPUT
CURRENT_TIMESTAMP, inserted.column
where inserted.column = 1
INTO Table b
FROM table c join table a on (a.id = c.id)


Thanks!

MERGE (Transact-SQL)

-PatP

Am I missing something? The set of rows in Table Alpha is:

CREATE VIEW Unknown_Department_Alphas
AS
SELECT *
FROM Alpha -- wish we had a valid table!
WHERE department_name IS NULL;

Why would you fake a deck of punch cards or mag tape by materializing it in a redundant second table? This looks like you are trying to do an audit trail in the same SQL as your schema. NEVER do that! You will go to jail and it will not work.

Auditing is always done at the boundary of the system, so that you can catch SELECT statements as requried by HIPPA, BASEL II, etc.

Also that UPDATE ..FROM .. syntax is 1970's Sybase dialect; good SQL programmers do not use and are aware the cardinality problems. Might want to Google that.

Thanks for the replies!

Before we get off topic, here are some more specific details:

Validation is performed on a column in Table A (against another lookup table)
- if it is valid, the record in Table A is marked as such
- if it is invalid, the record in Table B is marked as such and another record is inserted into Table B

I am unsuccessful in using a MERGE statement (or UPDATE) to be in line with the above behaviour because the OUTPUT will contain both the valid and invalid records.

>> Validation is performed on a column in Table A (against another look-up table) <<

In a well-designed schema that is done either by a CHECK() or a REFERENCES constraint; we would see that if you had posted DDL, as per Basic Netiquette. The goal of RM is that the tables are always in a valid state (i.e all constraints are TRUE) at the end of each transaction.

Rows are nothing like records. Would you like links to articles and books on this basic concept of RDBMS?

>> - if it is valid, the record [sic] in Table A is marked as such
if it is invalid, the record [sic] in Table B is marked as such and another record [sic] is inserted into Table B <<

This is a narrative description of a punch card edit program. The good cards go to card sorter pocket A (vague generic name); the bad cards go to card sorter pocket B (second vague generic name). You still have a workflow model of data! This is not how we program in SQL.

>> I am unsuccessful in using a MERGE statement (or UPDATE) to be in line with the above behavior because the OUTPUT will contain both the valid and invalid records [sic]. <<

We have no DDL, no specs and a vague narrative. Why did you think anyone can write code from this? You show code where a magical generic “id” is NULL! It is impossible for an identifier in RDBMS by definition. The NULL department_something was perhaps bad design, but this is just wrong.

You seem to have more problems than we can handle here in a forum.

This is business rule validation on a Staging table.

I see that you are the resident 'hall-monitor' and I respect your opinion; but to say rows are nothing like records is not completely true.. They are not the same thing; but they are related concepts.

To clarify:
All the records stay in Table A.
Table B has only a record for each validation error of a record in Table A.

Unfortunately, I am not in a position to re-design; but I will bring up your points to those who are.

Unfortunately, I do not post DDL (as do most ppl) as it would be overkill for my question.
I am not asking for a specific solution.

Currently, the question is: does anyone know if one can conditionally select the records from an OUTPUT clause in an UPDATE statement ?

Because you are dealing with two different tables, you can't do this in a MERGE statement, or any other single sql statement.

The only way to achieve this is a single database call would be to have a trigger on table A which inserts data into table B. This is a pretty standard structure for archive tables, which sounds like the scenario you are dealing with.
Explain in more detail why you can't use triggers? Triggers can (and should) handle multi-record transactions.

Thinking it over, a Trigger would solve this issue.
I was initially told to rule out Triggers as part of the design; but I believe this would be acceptable.

Thanks!

No doubt you were told this by someone who does not know how to properly write triggers....