StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPrepare and import data into existing database
text
Body
copied!<p>I maintain a PHP application with SQL Server backend. The DB structure is roughly this:</p> <pre><code>lot === lot_id (pk, identify) lot_code building ======== buildin_id (pk, identity) lot_id (fk) inspection ========== inspection_id (pk, identify) building_id (fk) date inspector result </code></pre> <p>The database already has lots and buildings and I need to import some inspections. Key points are:</p> <ul> <li>It's a one-time initial load.</li> <li>Data comes in an Excel file.</li> <li>The Excel data is unaware of DB autogenerated IDs: inspections must be linked to buildings through their lot_code</li> </ul> <p>What are my options to do such data load?</p> <pre><code>date inspector result lot_code ========== =========== ======== ======== 31/12/2009 John Smith Pass 987654X 28/02/2010 Bill Jones Fail 123456B </code></pre> <h2>Update: How I finally did it</h2> <p>In case someone else has to do a similar task, these are the steps the data load finally required:</p> <ol> <li><p>Prepare the Excel file: remove unwanted columns, give proper names to sheets and column headers, etc.</p></li> <li><p>With <code>SQL Server Import / Export Wizard</code> (32-bit version; the 64-bit version lacks this feature), load each sheet in the into a (new) database table. The wizard takes care of (most of) the dirty details, including creating the appropriate DB structure.</p></li> <li><p>Log into the database with your favourite client. To make SQL coding easier, I created some extra fields in the new tables.</p></li> <li><p>Start a transaction.</p> <p><code>BEGIN TRANSACTION;</code></p></li> <li><p>Update the auxiliary columns in the newly created tables:</p> <p><code>UPDATE excel_inspection$ SET building_id = bu.building_id FROM building bu INNER JOIN ....</code></p></li> <li><p>Insert data in the destination tables:</p> <p><code>INSERT INTO inspection (...) SELECT ... FROM excel_inspection$ WHERE ....</code></p></li> <li><p>Review the results and commit the transaction if everything's fine:</p> <p><code>COMMIT;</code></p></li> </ol> <p>In my case, SQL Server complained about collation conflicts when joining the new tables with the existing ones. It was fixed by setting an appropriate collation in the new tables but the method differs: in SQL Server 2005 I could simply change collation from the SQL Server Manager (Click, Click, Save and Done) but in SQL Server 2008 I had to set collation manually in the import Wizard ("Edit SQL" button).</p>

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