My current project that I've mentioned from time-to-time is a Smart Client replacement for an existing web-based Intranet application. The web app has been online and changing to meet ongoing business demands for four years, which means the SQL database has undergone a LOT of changes. Tables and views have become orphaned along the way; fields which once had use in the application no longer do; more objects are in the database than I want to see, and so on. Every single change I made to the database since 2001 made perfect sense, but still we have to live with what our databases become.
But sometimes we get lucky. Sometimes we get the chance to take the database and recreate the schema. All of it. The chance to begin again in the Off-World Colony
Well, in the process of transitioning from the web to Windows (with schedule and business requirement enablers), I have been given that rare gift of being able to completely rebuild my four-year-old database environment. So I am jazzed.
I designed the new tables yesterday. Today I worked on the script to populate them. Should be able to finish up with the script tomorrow, at which point I can get back to some WilsonORMapper fun with a BRAND NEW DATABASE! Come on down to get your keys, Connie!
Identity was a big part of today's populating effort. It was important to temporarily turn off an identity to retain key values. Like so.
set identity_insert Users on
insert into Users (userid, username, firstname, lastname)
select uid, username, first_name, last_name from oldDatabase..oldUserTable order by uid
set identity_insert Users off
go
A little tip is that you have to use the "insert into table () values ()" syntax with Identity_Insert on.
And if you're doing a lot of testing in SQL MSQuery, don't forget you can execute code snippets by highlighting them. CTRL-SHIFT-C/CTRL-SHIFT-R to comment and uncomment SQL code in SQL MSQuery is another great tip to remember.