title: Create Duplicate records with autonumber foreign keys
body: <p>(using SS2008)  Within a sql server database, I'd like to copy all the data of one client for a new client.  In other words, generate exact duplicates of all records that pertain to client #1, except now the client ID field of the new records refer to client #2.  This is all inside the same database.  </p>
  
  <p>Normally I would do this with a series of INSERT commands on the relevant tables selecting the client #1 records.  However, some of the tables have autonumber ID columns, and these IDs are referenced as foreign keys in child tables.  So when generating the child table records, I'd need to know and refer to the newly created autonumber Ids.  </p>
  
  <p>What is the cleanest way to go about this?  Can it be done with SQL Server replication?  My knowledge of SQL Server is pretty moderate.</p>