• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Select NULL into table

I have a table with 2 columns that allow NULL.  I am attempting to create a SP that will populate this table.  It is as follows:

INSERT INTO TitleApproval

SELECT     GETDATE() AS FaxDate, Stock.StockID

FROM         Stock INNER JOIN
                      AssignorStock ON Stock.AssignmentID = AssignorStock.AssignmentID INNER JOIN
                      Assignor ON AssignorStock.AssignorID = Assignor.AssignorID INNER JOIN
                      RFStockStatus ON Stock.StockStatusInd = RFStockStatus.StockStatusID
WHERE     (Assignor.ParentCompanyID = 7812) AND (Stock.StockStatusInd = 64)
GROUP BY Stock.StockID
HAVING      (NOT (Stock.StockID IN
                          (SELECT     TitleApproval.StockID
                            FROM          Stock AS Stock_1 INNER JOIN
                                                   TitleApproval ON Stock_1.StockID = TitleApproval.StockID
                            WHERE      (Stock_1.StockStatusInd = 64) AND (TitleApproval.Approve IS NULL) OR
                                                   (Stock_1.StockStatusInd = 64) AND (TitleApproval.Approve = '1'))))

Yet, when I attempt to create the SP, I get this error "Insert Error: Column name or number of supplied values does not match table definition."  I need to insert NULL values into these columns, but I am brain dead today and cannot figure out how to.
0
mdward4
Asked:
mdward4
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
two options

1--- mention the nonnollable columns in the column list

INSERT INTO TitleApproval (FaxDate, StockID ) --------------------------------here

SELECT     GETDATE() AS FaxDate, Stock.StockID

FROM         Stock INNER JOIN
                      AssignorStock ON Stock.AssignmentID = AssignorStock.AssignmentID INNER JOIN
                      Assignor ON AssignorStock.AssignorID = Assignor.AssignorID INNER JOIN
                      RFStockStatus ON Stock.StockStatusInd = RFStockStatus.StockStatusID
WHERE     (Assignor.ParentCompanyID = 7812) AND (Stock.StockStatusInd = 64)
GROUP BY Stock.StockID
HAVING      (NOT (Stock.StockID IN
                          (SELECT     TitleApproval.StockID
                            FROM          Stock AS Stock_1 INNER JOIN
                                                   TitleApproval ON Stock_1.StockID = TitleApproval.StockID
                            WHERE      (Stock_1.StockStatusInd = 64) AND (TitleApproval.Approve IS NULL) OR
                                                   (Stock_1.StockStatusInd = 64) AND (TitleApproval.Approve = '1'))))


---option 2
assuming that the nullable columns are at the end

INSERT INTO TitleApproval
SELECT     GETDATE() AS FaxDate, Stock.StockID, null, null
FROM         Stock INNER JOIN
0
 
mdward4Author Commented:
I had tried option 2 already with no success, but option 1 worked.  Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now