Access to SQL Server table copy

Topic

New Reply

I have a table in Access with an autonumber field and a table in SQL Server with exactly the same fields although the autonumber field in SQL Server is an Identity field.

When i empty the SQL Server table then append all fields from the Access table i get errors saying there are duplicated entries, is this because i am trying to copy the autonumber field from Access to an Identity field in SQL Server.

How can i overcome this problem?

Reply | Quote

Replies

Patt,

I would guess you do not want to lose the ID from Access because other data is linked to it. You may need to add an extra column to your SQL Server table for ‘old ID’, and import the Autonumber data into this field. Then, run update queries on your linked tables to replace the old ID with the new ID. A query that includes the two tables, with a link between the foreign key field in your ‘slave’ table and the ‘old ID’ field in the master table, is one approach. Have the query update the foreign key field to the new SQL Server Identity value.

A bit longwinded, and must be repeated for every linked table, but it works.

Reply | Quote

Patt,

I would guess you do not want to lose the ID from Access because other data is linked to it. You may need to add an extra column to your SQL Server table for ‘old ID’, and import the Autonumber data into this field. Then, run update queries on your linked tables to replace the old ID with the new ID. A query that includes the two tables, with a link between the foreign key field in your ‘slave’ table and the ‘old ID’ field in the master table, is one approach. Have the query update the foreign key field to the new SQL Server Identity value.

A bit longwinded, and must be repeated for every linked table, but it works.

Thanks Jules, I have done that kind of thing before, marrying up an old ID to the new one created by either a Autonumber field or an IDentity field.

I was wondering though if you cannot enter a value in the IDentity field of a SQL Server table. I seem to recall you can do it in Access, i may be wrong there though.

Reply | Quote

Actually you can do what you describe Pat, but it requires that either the table be a new table in SQL Server (i.e. the Identity property still thinks there are no rows in the table), or you have to take the Identity property off (the field type in SQL Server needs to be Integer), run the append, and then put the Identity property back. As long as the Identity property doesn’t think that a value being entered hasn’t been used already, and it is greater than the largest value, then you can append with the property set. I’ve not tested that with SQL Server 2008, but I know it works with 2000 and 2005.

Reply | Quote

Actually you can do what you describe Pat, but it requires that either the table be a new table in SQL Server (i.e. the Identity property still thinks there are no rows in the table), or you have to take the Identity property off (the field type in SQL Server needs to be Integer), run the append, and then put the Identity property back. As long as the Identity property doesn’t think that a value being entered hasn’t been used already, and it is greater than the largest value, then you can append with the property set. I’ve not tested that with SQL Server 2008, but I know it works with 2000 and 2005.

Thanks Wendell, i will do that to preserve my autonumber fields from access. This is a one off job so that is the way to go.

Is there any way to kid SQL Server 2005 to think it never had rows in that particular table?

Reply | Quote

… Is there any way to kid SQL Server 2005 to think it never had rows in that particular table?

Not that I’ve been able to find. In fact, if the identity property is turned off, the table saved, then the rows appended, and then the Identity property turned back on for the field in question, new rows will (try) to be appended starting with the next value that would have been used before the identity property was turned off. That works in many cases, but I have seen it cause problems as well. So I usually delete the field and add a new one.

Reply | Quote

Not that I’ve been able to find. In fact, if the identity property is turned off, the table saved, then the rows appended, and then the Identity property turned back on for the field in question, new rows will (try) to be appended starting with the next value that would have been used before the identity property was turned off. That works in many cases, but I have seen it cause problems as well. So I usually delete the field and add a new one.

I can see i have some testing to do.

I will let you know how i go.

Thank you.

Reply | Quote

Not that I’ve been able to find. In fact, if the identity property is turned off, the table saved, then the rows appended, and then the Identity property turned back on for the field in question, new rows will (try) to be appended starting with the next value that would have been used before the identity property was turned off. That works in many cases, but I have seen it cause problems as well. So I usually delete the field and add a new one.

Hey Wendell, thanks for your help,

I think I found a command i can use in this case, it is:

SET IDENTITY_INSERT tablename ON
…..do my inserts into the table tablename
SET IDENTITY_INSERT tablename OFF

So i dont need to take off the identity from a table’s field, do my inserts then put it on again afterwards.

I hope i am right.

Reply | Quote