Skip to main content

Grailsify legacy ms sql database

I had a problem. I'm developing using grails but my constraint is that I have to use a legacy ms sql database that was created by importing data from excel to access to ms sql. Thus the tables do not have any id field or primary key. And not only that, once I've done my work on my pc, and I do a full dump onto the server using MS own "SQL Server Import and Export Wizard 2008" it does not retain the identity property of the id field (that's auto-increment for all you lucky mysqlers). So when grails try to create a new record it fails. So so sad. But after a few days of depressive wanderings I decided to be a man and solve this. So here's the script in full, of how you would automatically create an id if the table does not have it yet, and if already has, will do the whole recreate process to make the id an identity again. Hope it would benefit someone.

declare @tablename varchar(1000),@importnew nvarchar(1000),@addconstraint nvarchar(1000),@dropold nvarchar(1000),@dorename nvarchar(1000),@dname nvarchar(1000),@dquery nvarchar(1000)
declare tables cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES
open tables
fetch next from tables into @tablename
while @@FETCH_STATUS = 0
begin
print 'processing ' + @tablename
if exists(select column_name from INFORMATION_SCHEMA.columns where table_name = @tablename and column_name = 'idold')
begin
set @dropold = 'alter table '+ @tablename +' drop column idold'
exec sp_executesql @dropold
end
if not exists(select column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = @tablename)
begin
if exists(select column_name from INFORMATION_SCHEMA.columns where table_name = @tablename and column_name = 'id')
begin
set @dorename = 'exec sp_rename "' + @tablename + '.id", "idold", "column"'
exec sp_executesql @dorename
set @importnew = 'select identity(numeric(19,0)) as id,* into ' + @tablename +'_tmp from ' + @tablename + ' where 1=0'
exec sp_executesql @importnew
set @dname = null
select @dname = coalesce(@dname+',','') + column_name from information_schema.columns where table_name=@tablename
print @dname
set @dquery = 'set identity_insert '+ @tablename + '_tmp on;insert into ' + @tablename + '_tmp (id, '+ @dname +' ) select idold,* from ' + @tablename + ';set identity_insert '+ @tablename + '_tmp off'
exec sp_executesql @dquery
end
else
begin
set @importnew = 'select identity(numeric(19,0)) as id,* into ' + @tablename +'_tmp from ' + @tablename
exec sp_executesql @importnew
end
set @addconstraint = 'alter table ' + @tablename + '_tmp add constraint ' + @tablename + '_pk primary key (id)'
exec sp_executesql @addconstraint
set @dropold = 'drop table ' + @tablename
exec sp_executesql @dropold
set @dorename = 'exec sp_rename ' + @tablename + '_tmp, ' + @tablename
exec sp_executesql @dorename
if exists(select column_name from INFORMATION_SCHEMA.columns where table_name = @tablename and column_name = 'idold')
begin
set @dropold = 'alter table '+ @tablename +' drop column idold'
exec sp_executesql @dropold
end
end
fetch next from tables into @tablename
end

It took a lot of googling to get that much. In that tiny piece of accumulated wisdom is how you would create a cursor so that you can loop over the items, how you would check whether a field exist or not in a particular table, does the table already have a primary key, how to rename column, how to insert value into an identity field by setting identity_insert to on, how to get a list of table fields and turn it into a comma separated string so that you can put that into another query. Phew.. But most importantly it allows me to import and export my ms sql 2008 database and know that this time the id field would still be intact and functioning (after running the script of course).

/me misses mysql, postgres and zodb... :(

Comments

Popular posts from this blog

Food first post

My blogs' name is High-Tech Rojak but I don't recall ever talking about food. So here's a first. Just recently I got some free time and finally got to cook the pasta I've bought for ages. So here's an account of how it went down.. :) Okay. That's the thing I cooked. I have no idea what it's called. At first I was thinking of buying the ribbon ones, then my wife looked at the colorful spiral ones and said "why not get these? they're more colorful" and so we got them (yes, we know nothing about pasta.. :) So I boiled it, actually put some salt and oil into the water so that they won't stick, drained it and tadaaaaa, you'd get the above. I remember once I tried to cook macaroni and I didn't drain it after boiling it, it filled up the whole pot. LOL... Learned my lesson. Next up the sauce. Like I said we don't know anything about pasta so here's the ingredients we prepared. Yes ladies and gentleman. Instant pasta sauce all bottl

Documentation is a must... after this.

I've been thinking quite a bit about documentation and the 'cost' it involves. And when I say documentation, I mean documentation in general about anything. One obvious case with the industry I'm involved in is user documentation (a.k.a The Manual). Creating great features in software takes time and effort but if it is not documented then the user won't even know about it and finally it never gets used. But then while documenting it you just wish that you're working on the next cool thing rather than have to write this up. So finally you end up not doing the documentation or doing it rather badly. Same thing with this blog writing. I have been doing some pretty interesting things with my phone (rooting it and using cynogenmod and all), some pretty significant life changes (my grandmother passed away) and a lot of other things which I should probably like to remember better or reflect more on it but not documented (here or anywhere permanent) and it would probabl

The Future Of Gaming

I love playing computer games. It's what originally drove me to learn computer programming, I wanted to create my own games. Until now I still have very little success with that, but... I have learnt to program web applications quite well and earning my pay using those skills. And I love open source software. Ever since I started programming professionally, my main work OS has always been Linux (various distributions and all and currently on Arch Linux). I always install dual-boot because... hardware problems (some projectors and printers just couldn't be detected by Linux when I started out, that's mostly not a problem now) and mainly to play games (sure there was some open source games available, but apart from "Battle for Wesnoth" and "FreeCiv" I don't actually recall any games I've played extensively enough to be remembered). But recently the gaming scene in LinuxLand has improved tremendously, partly thanks to the Windows 8 app store like