troubleshooting Question

SQL INSERT SELECT Error

Avatar of fsuedu
fsuedu asked on
DatabasesMicrosoft SQL Server 2005
1 Comment1 Solution2272 ViewsLast Modified:
With the statment below--I get a error message:

INSERT INTO [SPIResConv5].[dbo].[Transactions]
           ([TRXNO],
            [RESORT_ID]
           ,[TRANSTYPE_ID]
           ,[BILLCODE_ID]
           ,[MAINTENANCE_ID]
           ,[CONTACT_ID]
           ,[POSTED]
           ,[DATE]
           ,[USER_ID]
           ,[BATCH]
           ,[TYPE]
           ,[AMOUNT]
           ,[PAYMENTCODE_ID]
           ,[BANKCODE_ID]
           ,[DOCNO]
           ,[Shift4Trx]
           )
Select
  c.cnt + row_number() over (partition by t.resort_id order by t.resort_id, t.contact_id) as trxno,
       t.Resort_ID,
       TRANSTYPE_ID,      
       BILLCODE_ID,
       MAINTENANCE_ID,
       Contact_ID,
       Posted,
       Date,
       [USER_ID],
       BATCH,
       [Type],
       Amount,
       PAYMENTCODE_ID,
       BANKCODE_ID,
       DOCNO,
       [Shift4Trx]
from   TransactionsTemp t
  left join
  (select Resort_ID, count(*) as cnt from TransactionsTemp group by Resort_ID) c
  on t.Resort_ID = c.Resort_ID


Error Message:

Violation of PRIMARY KEY constraint 'PK_Transactions'. Cannot insert duplicate key in object 'dbo.Transactions'.
The statement has been terminated.


I'm trying to insert into table Transaction,
That has to Primary keys:
[TRXNO], [RESORT_ID]

The TRXNO is the first column in the table--but it has a wierd setup.  
For example

TRXNO  RESORT_ID
3                  ELL2
2                  FAC
3                  CSI
3                  ATR
4                  CSI
3                  FAC
4                  ELL2

It Creates a transaction code- based on the last resort it -transaction -instead just adding a number for the next transaction.

Thanks JK
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros