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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 constraining insert into
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 01/19/2010 :  06:27:16  Show Profile  Reply with Quote
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:

(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)

60,MAX(transaction_date), 'SCSH1',
(select next_count from sequence where SEQUENCE_CODE = 'X0001'),
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??



United Kingdom
22859 Posts

Posted - 01/19/2010 :  06:34:42  Show Profile  Reply with Quote
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.


HAVING MAX(transaction_date) IS NOT NULL

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

Edited by - Kristen on 01/19/2010 06:35:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000