Chronicals of… a developer in Johannesburg

Yesterday we had to migrate a couple of thousand rows from some excel documents (from different departments) into one table. All was running smoothly, but I found duplicates and part of the duplicate removal process was the following.

Select distinct variables into a #temp table.
Truncate the main table
Select into the main table from the #temp table and then all is well.

Query editor window 1: Created #temp table, selected distinct data into it, checked distinct data.

Query editor window 2: Began transaction, created #temp table, truncated table, inserted into new table <error occurred> tried to rollback.

End result. The table was truncated and I lost the data. Luckily I went back to the Query editor window 1 and found the temp table, only problem was – it didn’t have the rights to insert into the real table.

Now the point of the story – copied the #temp data into a ##temp data. Used that data from another query window to save the day.

A note to all of you, never truncate a table within a transaction and try to rollback. This is what we did….

Ever had an excel document with all the data that you want to import into a SQL database table? Bulk insert is your friend.

I know some of us (me included) are sometimes scared of handling external files in SQL. But this is really easy.

Best thing to do is get your excel document, export it as tab delimited, and import that into a table, temp table, or variable table within your sql query.

The main issue to note is that the path in the bulk insert is from the sql server’s “point of view”. So if you connect to a server through the network and need to access a file on you PC, then share it and grant the correct security permissions.

I am getting loads of spam comments… Better find one of those spam filter plugins.

— edit: I have one, yet it still lets some russian spam through!

Funny email I got – very true

take your time to read it

Dear Mr. Architect:

Please design and build me a house. I am not quite sure of what I need, so you should use your discretion. My house should have somewhere between two and forty-five bedrooms. Just make sure the plans are such that the bedrooms can be easily added or deleted. When you bring the blueprints to me, I will make the final decision of what I want. Also, bring me the cost breakdown for each configuration so that I can arbitrarily pick one.

Keep in mind that the house I ultimately choose must cost less than the one I am currently living in. Make sure, however, that you correct all the deficiencies that exist in my current house (the floor of my kitchen vibrates when I walk across it, and the walls don’t have nearly enough insulation in them).

As you design, also keep in mind that I want to keep yearly maintenance costs as low as possible. This should mean the incorporation of extra-cost features like aluminum, vinyl, or composite siding. (If you choose not to specify aluminum, be prepared to explain your decision in detail.)

Please take care that modern design practices and the latest materials are used in construction of the house, as I want it to be a showplace for the most up-to-date ideas and methods. Be alerted, however, that kitchen should be designed to accommodate, among other things, my 1952 Gibson refrigerator.

To insure that you are building the correct house for our entire family, make certain that you contact each of our children, and also our in-laws. My mother-in-law will have very strong feelings about how the house should be designed, since she visits us at least once a year. Make sure that you weigh all of these options carefully and come to the right decision. I, however, retain the right to overrule any choices that you make.

Please don’t bother me with small details right now. Your job is to develop the overall plans for the house: get the big picture. At this time, for example, it is not appropriate to be choosing the color of the carpet.
However, keep in mind that my wife likes blue.

Also, do not worry at this time about acquiring the resources to build the house itself. Your first priority is to develop detailed plans and specifications. Once I approve these plans, however, I would expect the house to be under roof within 48 hours.

While you are designing this house specifically for me, keep in mind that sooner or later I will have to sell it to someone else. It therefore should have appeal to a wide variety of potential buyers. Please make sure before you finalize the plans that there is a consensus of the population in my area that they like the features this house has. I advise you to run up and look at my neighbor’s house he constructed last year. We like it a great deal. It has many features that we would also like in our new home, particularly the 75-foot swimming pool. With careful engineering, I believe that you can design this into our new house without impacting the final cost.

Please prepare a complete set of blueprints. It is not necessary at this time to do the real design, since they will be used only for construction bids. Be advised, however, that you will be held accountable for any increase of construction costs as a result of later design changes.

You must be thrilled to be working on as an interesting project as this! To be able to use the latest techniques and materials and to be given such freedom in your designs is something that can’t happen very often. Contact me as soon as possible with your complete ideas and plans.

PS: My wife has just told me that she disagrees with many of the instructions I’ve given you in this letter. As architect, it is your responsibility to resolve these differences. I have tried in the past and have been unable to accomplish this. If you can’t handle this responsibility, I will have to find another architect.

PPS: Perhaps what I need is not a house at all, but a travel trailer. Please advise me as soon as possible if this is the case..