[dba-SQLServer] Appending Data with AutoNumber in SQL Server

Hi Jeff,

What I've done in Access is:

Create a blank new table same as original.
In the new table remove primary key and autonumber functionality from the PK
field.
Copy the data.
Now reset the PK field primary key and autonumber functionality.

This does work in Access IF the PK numbers don't have any gaps.
I would try this using an Access file with tables linked to the SQL Server
tables (I haven't done this with SQL Server but I think it's worth a try).

Good Luck!
Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jeff
Sent: Thursday, June 05, 2014 13:23 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Appending Data with AutoNumber in SQL Server

I NEED to append the Primary Key

Sent from my iPhone

> On Jun 5, 2014, at 11:21 AM, "Doug Murphy" <dw-murphy at cox.net> wrote:
> 
> Are you appending the Primary Key or letting SQL server fill? In 
> Access you can append a PK value into a table with a query. I am not 
> sure that is the case with SQL Server.
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jeff 
> B
> Sent: Thursday, June 05, 2014 8:56 AM
> To: Dba-SQL
> Subject: [dba-SQLServer] Appending Data with AutoNumber in SQL Server
> 
> I am going to post this in the Access group as well, as a separate email.
> 
> 
> 
> I am trying to use MS Access queries to append data from a table in 
> one SQL database into a table in a second SQL database.  The first 
> contains live data that needs to be moved to a test database.  I have 
> deleted all of the data from the tables in the second database, and 
> have reset the auto number (or identity in SQL) back to 0.  However, 
> when I try and use an Access append query, it fails and tells me there 
> are key violations.  I thought that be deleting all data and reseeding 
> the identity, I would avoid these problems.  Anyone know what I need to do
in order to make this work?
> 
> 
> 
> Jeff Barrows
> 
> MCP, MCAD, MCSD
> 
> 
> 
> Racine, WI
> 
> jeff.developer at gmail.com
> 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com