Post reply

Unable to Insert Data

  • April 11, 2005 at 12:20 pm

    #66185

    I have three tables Orders, Employees and TempOrders. Orders and Employees are linked by EmployeeID. Orders and Employees are in database A and TempOrders is in database B.

    I want to extract information from Orders and Employees in order to populate TempOrders. Here is an example of what I'm doing:

    INSERT INTO B.dbo.TempOrders
    (
    [dbo].[OrderID],
    [dbo].[EmployeeID],
    [dbo].[OrderDate],
    [dbo].[EmployeeDept]
    )
     
    SELECT DISTINCT
    a.OrderID, a.EmployeeID, a.OrderDate, b.EmployeeDept
    FROM A.dbo.Orders As a
    INNER JOIN A.dbo.Employees As b ON a.EmployeeID = b.EmployeeID,
    LEFT OUTER JOIN B.dbo.TempOrders ON OrderID = a.OrderID
    WHERE B.dbo.TempOrders.OrderID = NULL

    When I run the sp I get a message saying that it completed but when I check TempOrders there are no records. If I run the SELECT statement on its own the expected records are returned.

    Basically, the idea is to retrieve all orders from the Orders table that haven't already been retrieved. I know that there will probably be easier ways of achieving this but the method that the compant has selected is this.

    Hope this makes sense!

  • April 11, 2005 at 12:31 pm

    #551925

    Try this :

    WHERE B.dbo.TempOrders.OrderID IS NULL

  • April 11, 2005 at 12:48 pm

    #551931

    Hmmmm....  By process of elimination, if the "select" portion of the code works, then there must be some problem with the "insert" portion.  Try removing [dbo]. from the column names in the insert statement.

    Next, as a test case, try the following insert, substituting your own values for these test values.

    INSERT INTO B.dbo.TempOrders
    (
    [OrderID],
    [EmployeeID],
    [OrderDate],
    [EmployeeDept]
    )
    values ('1', '1', GetUTCDate(), 'x')

    If the insert test works, and the select works, then the problem is with the data being returned by the select.  For example, you may be attempting to insert a null value into a "not null" column.

    If you still have difficulties, please post the DDL for the TempOrders table.  This will help with troubleshooting.

    Hope this helps

    Wayne

  • April 11, 2005 at 1:24 pm

    #551944

    Hi Wayne,

    Oddly enough, I restricted the SELECT statement to one row and attempted to manually add the data that was returned. I found that manually entering the data into the table or by passing the values (as you suggested) to an INSERT statement works perfectly?!

    Unfortunately, I don't have access to the database at the moment so I can't provide the table info you asked for. I'll post it as soon as I have access.

     

    Thanks for your help,

    David

  • April 11, 2005 at 2:18 pm

    #551955

    Ok.  Next test to see what happens:

    INSERT INTO B.dbo.TempOrders
    (
    [OrderID],
    [EmployeeID],
    [OrderDate],
    [EmployeeDept]
    )
    select '1', '1', GetUTCDate(), 'x'

    Replace the sample values with valid ones and run the insert.  Assuming that this works, and it probably will, then the issue is almost certainly with the data returned from the full select. 

    Good luck

    Wayne

  • April 12, 2005 at 3:40 am

    #552078

    I reversed the procedure so that I returned one row of data and read the values into variables that I declared using the exact datatypes and lengths of the columns within the TempOrders table.

    I then used the INSERT statement and passed the values that were stored within the variables. When I ran the procedure it updated the table correctly?!

    E.g.

    declare @a as char(10)
    declare @b-2 as char(10)
    SELECT DISTINCT TOP 1
     @A = .....
     @B = .....
    INSERT INTO ....
    (A,B)
    VALUES
    (@A, @b-2)

    Very odd!

    Regards
    David

  • April 12, 2005 at 3:52 am

    #552081

    Probably the first row is correct and one of the other rows is causing problems - either because of duplicity, or because of data type or some constraint violation.

    Extract the entire procedure (i.e. the real code in it, not the procedure definition) into QA window, if there are any parameters, declare them, set their values and run it as SQL script. In QA you should receive error message that could show you where to look ... although often it is just a hint and you still have some work to find out where the problem really is.

    BTW, did you change the = NULL to IS NULL?

    /edit/

    One more thing...

    LEFT OUTER JOIN B.dbo.TempOrders ON OrderID = a.OrderID
    should be LEFT OUTER JOIN B.dbo.TempOrders b ON b.OrderID = a.OrderID. I'm not sure what happens if you omit the alias for b... but I'd guess that it would fail with "Ambiguous column name 'OrderID'".

  • April 12, 2005 at 4:18 am

    #552084

    I am really confused now! When I copied the code to QA and executed it the table was populated? So for whatever reason, the code does not work when executed as part of the stored procedure but does work if executed from QA? Any ideas on why this may be?

    Thanks for your help,

    David

  • April 12, 2005 at 4:50 am

    #552086

    David,

    you still didn't answer whether you changed the = NULL to IS NULL. If not, and your procedure is using different settings of ANSI NULLS than QA, then this is the problem.

    From BOL:

    When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name.

    When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<&gt comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name.

  • April 12, 2005 at 4:53 am

    #552088

    Apologies, the code is 'IS NULL' not '= NULL'.

    David

  • April 12, 2005 at 5:05 am

    #552090

    Hmm... there are still other settings that could influence it, like QUOTED IDENTIFIER, DATEFORMAT etc., but I'm afraid I have no ideas until I know DDL for both tables, and some sample data. If there are some implicit conversions involved, this could be the issue - otherwise, I'm at a loss. At least for now :-).

  • April 12, 2005 at 5:28 am

    #552094

    Having successfully run the code in QA, I tried to run the unamended stored procedure and it too works now! Could it have been some sort of security issue on the table or stored procedure?

     

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply