Insert Select

Hello all,

Im trying to insert into a table

INSERT INTO [SPIResConv5].[dbo].[TransactionsTemp]

([RESORT_ID]

,[TRANSTYPE_ID]

,[BILLCODE_ID]

,[MAINTENANCE_ID]

,[CONTACT_ID]

,[POSTED]

,[DATE]

,[USER_ID]

,[BATCH]

,[TYPE]

,[AMOUNT]

,[PAYMENTCODE_ID]

,[BANKCODE_ID]

,[DOCNO]

,[Shift4Trx]

)

Select Resort_ID.

'' as TRANSTYPE_ID,

'' as BILLCODE_ID,

'' as MAINTENANCE_ID,

Contact_ID,

'False' as Posted,

Date = getdate(),

'Hwells' as [USER_ID],

3000 as BATCH,

2 as Type,

Amount,

'LockBox' as PAYMENTCODE_ID,

'Conv' as BANKCODE_ID,

DOCNO,

'' as [Shift4Trx]

fromTransactionTempToTransaction

but I get a --

Cannot insert the value NULL into column 'TRXNO', table 'SPIResConv5.dbo.TransactionsTemp'; column does not allow nulls. INSERT fails.

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

For example

TRXNORESORT_ID

3ELL2

3FAC

3CSI

3ATR

4CSI

4FAC

4ELL2

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

How would I know the last tranasaction for the resort to insert the data from the other table?

Thanks for your help

Based on the information supplied I can't tell if any of the following will work for you or not.

1. Add TRXNO to your insert and calculate its value in a troed procedure or user-defined function before the insert. This might require another temp table.

2. ALTER TABLE before and after the transactionto DROP then ADD the NOT NULL constraint.

3. Remove the NOT NULL constraint.