Скачать или смотреть How to Copy Values Between Records in SQL Server without Saving Data

Discover how to copy calculated field values between records in SQL Server using CTEs and CROSS APPLY, while displaying results without altering existing data.
---
This video is based on the question https://stackoverflow.com/q/63707570/ asked by the user 'Philip Noah' ( https://stackoverflow.com/u/5108290/ ) and on the answer https://stackoverflow.com/a/63708191/ provided by the user 'Sander' ( https://stackoverflow.com/u/1607951/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Sql Server how to store value of a field in another record

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Copy Values Between Records in SQL Server without Saving Data

When working with SQL Server, many users face the challenge of manipulating data for display purposes without altering the original database. One such scenario involves taking the value of a calculated field from one record and using it in another. This guide will explore how to solve this problem effectively, particularly for fields that are determined through complex calculations.

The Problem

Imagine you are pulling data from two different databases using a UNION statement and want to create a field termed Elmin, which contains the balance from another field based on the account number. You have a specific requirement: to take the Elmin value from one record (where Account = 1113) and copy it to another record (where Account = 3104) for display purposes, specifically to offset receivables from one company with payables from another.

Current Output

Here’s how the current output looks:

AccountAcctNameCcodeBalanceElmin1112Accounts Receivable - (U.S.)C1250923.9401112Accounts Receivable (U.S.)U001113Accounts Receivable - I/CU223780.55223780.553104Accounts Payable - I/CC-2540000The desired output should look like this, where the value 223780.55 is carried over to the 3104 record's Elmin field:

AccountAcctNameCcodeBalanceElmin1112Accounts Receivable - (U.S.)C1250923.9401112Accounts Receivable (U.S.)U001113Accounts Receivable - I/CU223780.55223780.553104Accounts Payable - I/CC-254000223780.55The Solution

Step 1: Use Common Table Expressions (CTEs)

To achieve this, you can leverage Common Table Expressions (CTEs) to isolate your UNION statements. This allows you to separate the results from different datasets before combining them in a final step.

Step 2: Employ CROSS APPLY

By employing the CROSS APPLY operator, you can fetch the required value from the calculation you need based on specific conditions.

Sample Implementation

Here’s how this can be structured in SQL:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the SQL Code

CTEs: Two CTEs (cte_d1 and cte_d2) are defined to summarize the Elmin values from two subsets of your data.

UNION ALL: Combines the result sets of the two CTEs.

CROSS APPLY: Fetches the Elmin value from the first CTE to fill the Elmin for the 3104 account when specified.

Resulting Output

Executing the above code will provide you the following output:

AccountCcodeElmin1112C0.001112U0.001113U223780.553104C223780.55Conclusion

By using CTEs and CROSS APPLY in SQL Server, it is possible to manipulate and display results effectively without altering the underlying data structure. This method not only solves the immediate problem but also allows for flexible data handling in future queries.

If you find yourself needing to conduct similar data manipulations, consider using this approach to enhance the utility of your SQL queries while maintaining the integrity of your data.