Bigint to int problems!

cotton31 · 19 May 2011 at 11:21

I probably know the answer to this already, but I thought I'd ask.

We have a database with an ID field that has to have a datatype of int. I need to import data into this field, but some of the data I'm importing overflows the datatype (i.e. it needs to be bigint).

Is there anyway I can force this data into the field without changing the datatype, whilst also keeping each ID unique?

Thanks!

Edit: Probably should say this is SQL Server 2005!

GravyMonster · 19 May 2011 at 11:50

Nope. All you can do is convert the ID to bigint.

cotton31 · 19 May 2011 at 12:44

Thought so! Cheers.

GravyMonster · 19 May 2011 at 12:46

Remember to backup the current database first!

There shouldn't be any issues in making a field bigger, but you can never be too careful.

cotton31 · 19 May 2011 at 13:47

There's nothing in the database. I've already changed the datatype and the import was successful, but I'm told that the app which uses the database doesn't like the change. (I've got no control over the app)

lokkers · 20 May 2011 at 07:38

You could reseed the identity field, but you'll have to be very careful to restore any foreign key relationships.

If you've got that much data in the DB though, this problem isn't going to go away (i.e. you'll overflow the int eventually).

EDIT: Ignore that - I've just reread your post and it's a new database, so presumably your identities start from 1 anyway.

cotton31 · 20 May 2011 at 11:34

It's a new application that we need to interface with an existing one. Basically the app developers provide a table for me to import the data to from our existing app, but the ID field is unhelpfully set to int, so the ID's in our existing system overflow!

lokkers · 21 May 2011 at 09:08

Then you don't have much choice but to change the datatype I'm afraid, with corresponding changes to the app.