Copying Data between table without identity column

Posted by user668479 on Stack Overflow See other posts from Stack Overflow or by user668479
Published on 2011-03-20T19:02:13Z Indexed on 2011/03/20 19:21 UTC
Read the original article Hit count: 249

Filed under:
|

I have two table and I need to copy the data across from SRCServiceUsers to Clients

Everytime i run it I get the following: Violation of PRIMARY KEY constraint 'PK_Clients'. Cannot insert duplicate key in object 'dbo.Clients'. The statement has been terminated. The Primary key ClientId field is not an identity column and therefore requires filling

To date I have the following

insert into Clients(
ClientID,
Title,
Forenames,
FamilyName,
[Address],
Town,
County,
PostCode,
PhoneNumber,
StartDate)
SELECT 
(
Select Max(Clients.ClientID)+ 1,
SRCServiceUsers.Title,
SRCServiceUsers.[First Names],
SRCServiceUsers.Surname,
--BUILD UP MUITIPLE COLUMNS
SRCServiceUsers.[Property Name] + ', ' + SRCServiceUsers.Street + ', ' + SRCServiceUsers.Suburb as [Address],
SRCServiceUsers.Town,
SRCServiceUsers.County,
SRCServiceUsers.Postcode,
SRCServiceUsers.Telephone,
SRCServiceUsers.[Start Date]
From
srcsERVICEuSERS

How can i autoincrement the PK field - CLientID when inserting the data?

Many thanks

Andrew

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about tsql