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
sql-server
|tsql
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