If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > One statement to do an Insert and Update

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 102
One statement to do an Insert and Update

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!

Last edited by ontheDB; 01-23-13 at 13:49.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,459
You'll want to look into the OUTPUT clause.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 102
Quote:
Originally Posted by MCrowley View Post
You'll want to look into the OUTPUT clause.
Thanks!
THat seems like it'll do the trick
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 102
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!
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,791
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 298
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 102
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 298
>> 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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 102
Quote:
Originally Posted by Celko View Post
>> 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?
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.

Quote:
Originally Posted by Celko View Post
>> - 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.
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.

Quote:
Originally Posted by Celko View Post
>> 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.
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 ?
Reply With Quote
  #10 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
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.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 102
Quote:
Originally Posted by blindman View Post
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!
Reply With Quote
  #12 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
Quote:
Originally Posted by ontheDB View Post
I was initially told to rule out Triggers as part of the design; but I believe this would be acceptable.
No doubt you were told this by someone who does not know how to properly write triggers....
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On