Inserting a unique ID into a new record

[bvega]

I know SQL supports an automatic Identity data type. The problem is I have to export my tables from Access to an SQL server on my ISP through MDAC and ODBC. When I do this, the autonumber type in Access is changed to just number on the SQL server. Is there a way I can insert a unique Identity code into new records using SQL statements rather than data types? One solution I thought of is to do something rouqhly along these lines:<br>
&quot;insert into tablename select max(id)+1 from tablename&quot;<br>
<br>
Then I store the ID of the new record in a variable and do an update:<br>
update tablename set a= b= where id=<br>
<br>
Does anybody know if there is a better way to do this? Or if this will work at all?

 

[calahans]

HI,<br>
<br>
I just trying to work out the problem. You are trying to insert records into a table but hold there unique statis. If you set the field to be UNIQUE and then insert it should work , providing that the records in the source column where unique in the first place.<br>
<br>
The SQL you include lead me to think that you are trying to insert records twice (the second time you are changing the record's unique id to let it get past the unique restraint). If so I would agree that something like your SQL should work. I would make a slight change:<br>
<br>
1) Copy the data in your access table into another access table.<br>
<br>
2) update unique field to max(unique field)+1<br>
<br>
3) insert into sqlservertable select * from accesstable<br>
<br>
If you need more info let me know.<br>
<br>
C

 

[bvega]

I guess my real question is how do I set a column to be a unique identity code? What is the command line syntax in Microsoft SQL?

 

[DarkMan]

The identity column in SQL Server will either be an &quot;Int&quot; or &quot;SmallInt&quot; type, but set to be an identity column. If you are using the SQL Enterprise Manager, right click on a table and choose edit and the column under &quot;Identity&quot; will be checked for autonumbered tables.<br>
<br>
If you use are using the Microsoft Upsizing Tools, all of this should happen automatically. Look at microsoft.com under Access or SQL Server for the free download. All you have to do is start a new database, then tell Access to upsize it's database into the new SQL database.. Doing this will preserve all your relationships and your identity columns.... <p>Doug Trocino<br><a href=mailto:dtrocino@tecumsehgroup.com>dtrocino@tecumsehgroup.com</a><br><a href=

http://www.tek-tips.com>Tek-Tips

Forums</a><br>Technical Director<br>
Tecumseh Group, Inc.<br>
Sponsors of Tek-Tips Forums<br>

 

[RMcCookMan]

You can also access the @@IDENTITY column to retrieve the value of the last insert, say inside a stored procedure. <br>
<br>

 

[Guest_imported]

You may be able define the column as a plain int column, then copy the data, then make the col an identity later.<br>