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

I don't know SQL that well but when I had to do this in an mdb, I changed
the Autonumber to a long integer then changed it back later.

The other way is - assuming the primary key is a foreign key in some child
table(s) and you need that one to many relationship, is to write the new PK
for the FK field in the child tables - PITA I know since you probably can't
do that with queries and it needs to be coded.

Hth

Rocky


-----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 11:23 AM
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