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
ASKER CERTIFIED SOLUTION
appari

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
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