Access auto identifier lost during import to SQL

L

LinuxGold

Guppy
I'm pretty familiar with MySQL, now trying to learn MS SQL.

I imported the Access database (Access 97) into MS SQL server and auto increment seems to be lost.

QA.ID is supposed to be auto_incremented, how do I alter that table?


When I programmed ASP, I get the error as follows:

Cannot insert the value NULL into column 'ID', table 'QcData.dbo.QA'; column does not allow nulls. INSERT fails.

Where ASP string is as follows:

cmd.CommandText="INSERT INTO QA (LocationID,OperatorID,QAID,NTID,BrandID,Batch,KeyDate,ReceiveDate,Training) VALUES ('" & objLoc & "','" & objOPER & "','" & objQADE & "','" & objNTID & "','" & objBrand & "','" & objBatch & "','" & objKeyDate & "',current_timestamp,'" & objTrain & "');"

Anyone know how I can do it?
 
the field needs to be set to int, small int or bigint.

then where it says identuity. set it to non replicate.. then below that it says add x ... yet x to 1

and around the same place i belive it says start from 1 .. change that to the current ID

so if your current is 136 set it to 137 to be safe.

im not 100% sure but think that willl do it
 
Adding an identity column is a bit of a nightmare from the analyser. It is very simple to do in the Enterprise manager in the table designer.
 
Hmmm, can not get enterprise manager... (admins you know..) how about if I drop that ID, and add a new column starting at 7097 as a primary key that increments automatically, how do I do about that?
 
Finally found a way, after working with DBA, here is the SQL statement produced...


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_QA
(
ID int NOT NULL IDENTITY (1, 1),
LocationID int NULL,
OperatorID int NULL,
QAID int NULL,
NTID nvarchar(10) NULL,
BrandID int NULL,
OrdersReviewed smallint NULL,
CorrectOrders smallint NULL,
Batch nvarchar(10) NULL,
KeyDate smalldatetime NULL,
ReceiveDate smalldatetime NULL,
Training bit NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_QA ON
GO
IF EXISTS(SELECT * FROM dbo.QA)
EXEC('INSERT INTO dbo.Tmp_QA (ID, LocationID, OperatorID, QAID, NTID, BrandID, OrdersReviewed, CorrectOrders, Batch, KeyDate, ReceiveDate, Training)
SELECT ID, LocationID, OperatorID, QAID, NTID, BrandID, OrdersReviewed, CorrectOrders, Batch, KeyDate, ReceiveDate, Training FROM dbo.QA TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_QA OFF
GO
DROP TABLE dbo.QA
GO
EXECUTE sp_rename N'dbo.Tmp_QA', N'QA', 'OBJECT'
GO
COMMIT


Ran tests and it finally worked!


Scott
 
You must log in or register to reply here.
Back
Top