Prefix A_I field with number in sql server 2008?

[samvb]

Is it possible to prefix A_I field with a number like 301? My initial approach was to check if data exists in table. If no data exists, i would turn on auto increment insertion, insert 3011 then turn off insertion. Would this approach make the operation slow since everytime a row is added, it will do the check, on n off then insert the record?

2ND approach is to fetch the numbers (which could be stored in a table) and have them appended to the A_I automatically during insertion? That is something in my head but i donno how to do it. Is it even possible?

How about using default value? Like we can do convert(date,getdate()) to insert current date to a date field. Would it be possible to do something like this to the default value of the A_I? The 3 attempts i tried failed with errors.

Any idea would be helpful.

[Rabbit]

I have no idea what an A_I field is, it sounds like you're talking about an auto increment?

Why would you need to prefix it with 301? If everything's going to have 301 in front of it, what's the point?

I don't understand why you would need to turn on and off the auto increment. There's no reason to do that.

I'm not sure how you're checking the table for existing data.

All in all, I'm very confused about what you're actually trying to do.

[samvb]

The cause is this basically:

a single design db will be used by offices there n there. Each office got a code like 301 which is unique and already built in in the db when distribution. The offices will export their work/data to us and the problem is that the offices can produce the same A_I values [auto increment]. I just want to b able to identify each row since they all will be merged into the master office's database [which is an exact db the offices use]. Hope i am clear on that area.

From the Stored Procedure that adds rows, i wanted to do something like this:

IF EXISTS (SELECT * FROM tbl)
or SELECT @b=isnull(colname,0) from tbl

that will be done each time a row is going to be added. It works well expect am concerned about the speed and all.

In short, i just want to be able to merge all the data from 11 offices to the master database without a problem. the db does have A_I fields in 4 tables.

[ArchibaldTuttle]

Auto increment values cannot be prefixed. Maybe you have to think about a unique location specific id. Together with the A_I value it could be used to form your unique key value. Otherwise how will you keep apart the data from all the different offices if they will have the same A_I value?
What is your deployment architecture? Have you considered to use replication?

[samvb]

The major tables have unique location ids. E.g. order tables have orderid [AI],locationid while orderdetails just refers to orders table and contains primary key without location id.

Even with that, hw i merge the datas in master table? In one blog, i saw away to add an alpahebet but i couldn't find anywhere. Anyway, thanks for ur time guyz.

[Rabbit]

Just store location ID as a separate field in the table. There's no need to store it separately, in fact, it would be poor design and break the rules of normalization to store it in one field. You can always display it together for reporting purposes but you should store it separately.