SQL insert fails because the last row in the table is NULL even though no nulls is specified

redskye88
redskye88 used Ask the Experts™
on
I'm trying to insert values  into an existing table from a temporary  table that was created when  I did a Bulk insert from a text file.  When i try to update another table with values from that temp table I get the message below- How can I get around this with out changing the existing table design?  

Msg 515, Level 16, State 2, Line 17
Cannot insert the value NULL into column 'IDXID', table 'NewDocs0002.dbo.VTAB0025'; column does not allow nulls. INSERT fails.
- create temporary table from text file 
 
USE PDbase
GO
drop table CSVPD
CREATE TABLE CSVPD
(AccountNumber bigint,
 Typeid char,
PAIDDate char(6))
GO
 
-- Insert the data
BULK
INSERT CSVPD
FROM 'c:\\lnimfp.txt'
WITH
(
FIELDTERMINATOR = ','
 
)
GO
 
-- create new value for paid date where I get error message
 
insert into [NewDocs0002].[dbo].[VTAB0025] ([VAL])
 select CONVERT(varchar, CONVERT(datetime,STUFF(STUFF([PAIDDate], 3,0,'/'),6,0,'/') ,1),101)
 from [PDbase].[dbo].[CSVPD]  
    where exists
   (select a.[VAL],a.[docid]
    from [NewDocs0002].[dbo].[VTAB0018]as a
    cross apply(select [PDbase].[dbo].[CSVPD].[AccountNumber]
    from [PDbase].[dbo].[CSVPD] where [PDbase].[dbo].[CSVPD].[AccountNumber] = a.[VAL])as b)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Cannot insert the value NULL into column 'IDXID', table 'NewDocs0002.dbo.VTAB0025'; column does not allow nulls. INSERT fails.

Does the column count in the Flat file and the count of columns in the table matches.
And are you sure you pass Not Null value for that particular column for all records..
Kindly confirm
AneeshDatabase Consultant
Top Expert 2009

Commented:
insert into [NewDocs0002].[dbo].[VTAB0025] ([VAL])
 select CONVERT(varchar, CONVERT(datetime,STUFF(STUFF([PAIDDate], 3,0,'/'),6,0,'/') ,1),101)
 from [PDbase].[dbo].[CSVPD]  
    where exists
   (select a.[VAL],a.[docid]
    from [NewDocs0002].[dbo].[VTAB0018]as a
    cross apply(select [PDbase].[dbo].[CSVPD].[AccountNumber]
    from [PDbase].[dbo].[CSVPD] where [PDbase].[dbo].[CSVPD].[AccountNumber] = a.[VAL])as b)
AND PAIDDate is not null

Commented:
Insert an empty string in that column in stead? Apparently you have nothing to insert into that field, but the table design does not allow that. I guess an empty string ('') is nohing enough for you, and not null enough for the database design.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Author

Commented:
I'm sorry - I'm not a programmer so sometimes SQL gets me confused.  I figured out the problem and but a solution and I apologize for that.  IDXID needs to be populated as well, hence the null error.  The IDXID needs to be (int) 132 as the data.  It will always be 132 so how can I insert that too at the same time?  So I want to insert the int 132 in the IDXID field and the paiddate from the temp table.

Thanks!!!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Try this one..
insert into [NewDocs0002].[dbo].[VTAB0025] ([VAL], IDXID)
 select CONVERT(varchar, CONVERT(datetime,STUFF(STUFF([PAIDDate], 3,0,'/'),6,0,'/') ,1),101), 132
 from [PDbase].[dbo].[CSVPD]  
    where exists
   (select a.[VAL],a.[docid]
    from [NewDocs0002].[dbo].[VTAB0018]as a
    cross apply(select [PDbase].[dbo].[CSVPD].[AccountNumber]
    from [PDbase].[dbo].[CSVPD] where [PDbase].[dbo].[CSVPD].[AccountNumber] = a.[VAL])as b)

Open in new window

Author

Commented:
thanks rrjegan that worked but now there's a 3rd (and last) field that I'm getting the error on and I want populate [NewDocs0002].[dbo].[VTAB0025] .[docid] from [NewDocs0002].[dbo].[VTAB0018].[docid] that is in the query after "where exists".  Thank you so much!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly try this one out..
insert into [NewDocs0002].[dbo].[VTAB0025] ([VAL], IDXID, docid)
 select CONVERT(varchar, CONVERT(datetime,STUFF(STUFF([PAIDDate], 3,0,'/'),6,0,'/') ,1),101), 132, t2.docid
 from [PDbase].[dbo].[CSVPD]  t1 inner join 
   (select a.[VAL],a.[docid]
    from [NewDocs0002].[dbo].[VTAB0018]as a
    cross apply(select [PDbase].[dbo].[CSVPD].[AccountNumber]
    from [PDbase].[dbo].[CSVPD] where [PDbase].[dbo].[CSVPD].[AccountNumber] = a.[VAL])as b) t2 on t1.Val = t2.val

Open in new window

Author

Commented:
Thanks rrjegan17 I think we almost have it - The [PDbase].[dbo].[CSVPD]  t1 doesn't have a DOCID but [NewDocs0002].[dbo].[VTAB0018].[DOCID] does so I got this error when I tried the last code

  Msg 207, Level 16, State 1, Line 7
Invalid column name 'Val'.

Thanks!!!!
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Kindly try this one out..

Check whether the column names are placed correctly once..
insert into [NewDocs0002].[dbo].[VTAB0025] ([VAL], IDXID, docid)
 select CONVERT(varchar, CONVERT(datetime,STUFF(STUFF([PAIDDate], 3,0,'/'),6,0,'/') ,1),101), 132, t2.docid
 from [PDbase].[dbo].[CSVPD]  t1 
inner join [NewDocs0002].[dbo].[VTAB0018] t2 on t1.val = t2.val
inner join [PDbase].[dbo].[CSVPD] t3 on t3.[AccountNumber] = t2.[VAL]

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial