unique ID in MSSQL

S

snooper

Perch
Hi all

i have built a little DB in Access and have used the import/export wizard from MSSQL to import the table structures into the existing empty SQL database on the server.

thru Enterprise manager, i notice that the primary key autonumber from Access, did not make it thru the conversion.

Is there a way to do the export so that the autoNumber is conserved in the SQL?

thanks!
 
the unique ID is the identity field. after its been upsized/imported go into design view and change the identiy field (bottom section), to yes no duplicates

then increment by 1 and start at x

x = Plus one to existing.
 
Thanks E.

I couldnt really find the 'start at' thing, but i did find the incrment.

do i use this on an INT datatype? i cant seem to convert it to a UNIQUEIDENTIFIER - i get an error in EM.

and... in my scripts - do i just ignore this field, as i would normally for Access records?
Thanks!
 
yes you can ignore as it acts as a Autonumber in Access.

you can use int or bigint for the type.

what error do you get in EM, is it a error says fields may be changed or un read able, if so i think its ok to continue
 
Emagine said:
yes you can ignore as it acts as a Autonumber in Access.

you can use int or bigint for the type.

what error do you get in EM, is it a error says fields may be changed or un read able, if so i think its ok to continue
Click to expand...

See here, after trying to change the "SimchaTypeID" field - from int to uniqueidetifier:

Untitled-1.gif
 
is the id a number, i belive you can have a int or big int field for that. i have used nvarchar also as a ID field.

ive not used that field type,
 
Emagine said:
is the id a number, i belive you can have a int or big int field for that. i have used nvarchar also as a ID field.

ive not used that field type,
Click to expand...
Yes, "simchaTypeID" field is currently an int and set as a non-duplicate identity, as you suggested.

the question is maybe more directed to the export from Access stage - how to conserve the "auto number" status , instead of going thru EM and changing the identity field definition in every single table. is there a way to do this?
 
When exporting though EM,

click perservie Identity, its on the section where it lists all the tables you are tranferring and the names they wil be on the export. on the end of each line there is a button called tranform

click that and in the dialouge select keep identies (or on those lines)

that will then keep them and carry them on

i belive thats right , i haven't had to do so when upsizing WWF databases, they have been quite straight forward, well after the amount and different ways lol.
 
Thanks. i couldnt find it in EM, but thru the import/export tool i did it, adding
"IDENTITY (1, 1) PRIMARY KEY CLUSTERED"​

to each ID field (which i got from the WWFscripts ;) )

the only thing is that i see it didnt make the ID fields "yes - no duplicates". do you think this matters?

thanks again!
 
i think that line you quoted above should set the identity values, you are referring to.

'IDENTITY (1, 1)'
 
i think so, i think thats what perserves the identity fields.

hwn ive upgraded WWF databases, i have checked that and not had to reset the identity (Author_ID etc), it carryies on from the last ID.
 
You must log in or register to reply here.
Back
Top