constraining insert into - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

constraining insert into

Author

Topic

iainiow
Starting Member

2 Posts

Posted - 2010-01-19 : 06:27:16

Hi everyone,

Im having a bit of a mental block and a bit of noob logic.

I am trying to perform an insert into statement within a stored procedure. the result of my code is an error 515 "cannot insert null in to column..."

The basic logic I need to apply is:

Insert in to table "holding" one row where the sum of "amount" is greater than zero, the type is "CD" and the mac_code is in "ST1" or "ST2".

I also need to apply a value from another table to a field in the inserted row..

Not to difficult one would presume!

Here is my code:

INSERT INTO holding
(Import_Record_Type, Transaction_Date,Mac_Code, CAN, Ref_No, Ref_No2, amount, Fund, Type, User_Code, Source_Code,
Area_Code,location_code,narrative, pay_amt_base, VAT_code)

SELECT
60,MAX(transaction_date), 'SCSH1',
(select next_count from sequence where SEQUENCE_CODE = 'X0001'),
'91350943011','BANKING,Sum(tran_amt_base)*1,'05','CD','SV','XX','BTCC','SCALES1',
S BANKING ' ++ CONVERT(VARCHAR(19),DATEADD(S,max(transaction_date),'01-Jan-1970'),103) , Sum(tran_amt_base)*-1, 'AN'

FROM aim_t_holding

WHERE (tran_amt_base >0 AND mac_code in ('ST1','ST2') and Type = ('CD'))

If a matching combination of mac code, amount and type exists in the table the code performs the insert just fine. if it cant find an entry, i would expect it to not even attempt the insert. It seems to want to try the insert regardless but errors with: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Transaction_Date', table 'Aim_Test.dbo.HOLDING'; column does not allow nulls. INSERT fails.

Any ideas how i can straighten this out??

manythanks

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 06:34:42

Try running the SELECT (without the INSERT INTO) and see what data you get (and add more "debug" columns if you need to). That might shed some light on it.

Add

HAVING MAX(transaction_date) IS NOT NULL

after the WHERE to only get the "broken" ones.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources