Add Auto Increment type to a column

Question

Post reply

Add Auto Increment type to a column

chereku

SSC Enthusiast

Points: 156
More actions
August 24, 2010 at 2:11 pm

#237089

Hi,
I am migrating from MS access to sql server 2005, and I am facing a problem,
In access there is a column called "Recid" which is a auto increment column and it has a primary key,
when I am migrating the records from access to sql server, is it possible to keep the existing "Recid" column values like the one in access and if we add a new record to the table in sql server the new record should be assigned the auto incremental number which preceeds the existing "Recid" number
My Access Table
RecID Name Dist
1 sri MD
2 jim NJ
3 Lan NC
after migrating this table to SQL SERVER 2005 if I am inserting another record(Name: tony, Dist: NY) I should get the below result or the table should look like this.
RecID Name Dist
1 sri MD
2 jim NJ
3 Lan NC
4 tony NY
Please let me know As I am struck with this or let me know what is the best replacement for this problems.
Srini

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

You can create a column with the IDENTITY attribute for DECIMAL(x, 0), SMALLINT, TINYINT, INT and BIGINT columns.

N 56°04'39.16"
E 12°55'05.25"

WayneS SSC Guru Points: 95461 More actions · Answer 2

And to insert the existing values into the identity column, you will need to use SET IDENTITY_INSERT ON/OFF.

i.e. (using your test data):

if OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test

CREATE TABLE #test (

RowID int identity primary key clustered,

Name varchar(10),

Dist char(2));

SET IDENTITY_INSERT #test ON;

INSERT INTO #test (RowID, Name, Dist)

SELECT 1, 'sri', 'MD' UNION ALL

SELECT 2, 'jim', 'NJ' UNION ALL

SELECT 3, 'Lan', 'NC';

-- I'd recommend doing:

-- INSERT INTO #test (RowID, Name, Dist)

-- SELECT RowID, Name, Dist FROM OPENROWSET(... your access db connection here)

SET IDENTITY_INSERT #test OFF;

INSERT INTO #test (Name, Dist)

values ('tony','NY');

select * from #test;

Edit: added sample code

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2