StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To make very sure that you are not losing data when you alter a database, you can create scripts to confim the new structure and the old one contains the same logical data. For example, say version 1 of database looks like this (pseudo code)</p> <pre><code>CREATE TABLE Customer CustomerID INT, FirstName string, Surname string, AddressLine1 string, AddressLine2 string, AddressLine3 string, AddressLine4 string </code></pre> <p>In version 2, you want to be able to allow customers to have more than one adress so you move the address fields into a new table:</p> <pre><code>CREATE TABLE Address AddressID INT, CustomerID INT, AddressLine1 string, AddressLine2 string, AddressLine3 string, AddressLine4 string </code></pre> <p>You move the addresses from Customer table into the new address table like this:</p> <pre><code>INSERT Address CustomerID , AddressLine1 , AddressLine2 , AddressLine3 , AddressLine4 SELECT * FROM Customer </code></pre> <p>Then you remove the redundant address fields from Customer:</p> <pre><code>ALTER TABLE Customer DROP COLUMNS AddressLine1 , AddressLine2 , AddressLine3 , AddressLine4 </code></pre> <p>So far so good. But how do I know that the new Address table contains the exact same addresses as the old Customer table. It would be very easy for the whole process to run and somehow scramble the address so that Customers effectively changed adresses with each other. The code could pass all tests, but we will have destroyed our clients data as they no longer know where their customers live.</p> <p>We can confirm the move of address fields works by running</p> <p>If this returns any records, the upgrade failed because some customers didn't get their address moved:</p> <pre><code>SELECT * FROM OldCustomerTable OCT LEFT JOIN Address A ON OCT.CustomerID = A.CustomerID WHERE A.CustomerID IS NULL </code></pre> <p>If this returns any record, the upgrade failed because addresses were scrambled </p> <pre><code>SELECT * FROM OldCustomerTable OCT INNER JOIN Address A ON OCT.CustomerID = A.CustomerID WHERE OCT.Address1 != A.Address1 OR OCT.Address2 != A.Address2 OR OCT.Address3 != A.Address3 </code></pre> <p>OR OCT.Address4 != A.Address4</p> <p>You can additionally check that the new address table only contains 1 address for each customer</p> <pre><code>SELECT CustomerID , COUNT(AddressID) FROM Address GROUP BY CustomerID HAVING COUNT(AddressID) >1 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload