none
Help with Not Equal or Is Null

    Question

  • Two tables that I am not at liberty to change:

    CREATE TABLE [dbo].[CRIS_USER_LOCATION] (

    [id] numeric(10, 0) NOT NULL,

    [cris_user_id] numeric(10, 0) NOT NULL,

    [location_id] numeric(10, 0) NOT NULL,

    CONSTRAINT [PK_CRIS_USER_LOCATION]

    PRIMARY KEY CLUSTERED ([id] ASC)

    WITH ( PAD_INDEX = OFF,

    FILLFACTOR = 100,

    IGNORE_DUP_KEY = OFF,

    STATISTICS_NORECOMPUTE = OFF,

    ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON,

    DATA_COMPRESSION = NONE )

     ON [PRIMARY],

    CONSTRAINT [FK1_CRIS_USER]

    FOREIGN KEY ([cris_user_id])

    REFERENCES [dbo].[CRIS_USER] ( [id] ),

    CONSTRAINT [FK1_CRIS_LOCATION]

    FOREIGN KEY ([location_id])

    REFERENCES [dbo].[CRIS_LOCATION] ( [id] )

    )

    CREATE TABLE [dbo].[CRIS_USER] (

    [id] numeric(10, 0) NOT NULL,

    [login_id] varchar(20) NOT NULL,

    [active] numeric(1, 0) NOT NULL,

    [password] varchar(50) NULL,

    [PW_DATE] datetime NULL,

    [DEFAULT_PASSWORD] varchar(50) NULL,

    [FIRST_NAME] varchar(50) NULL,

    [LAST_NAME] varchar(50) NULL,

    [EMERGENCY_USER] numeric(10, 0) NULL,

    CONSTRAINT [PK_CRIS_USER]

    PRIMARY KEY CLUSTERED ([id] ASC)

    WITH ( PAD_INDEX = OFF,

    FILLFACTOR = 100,

    IGNORE_DUP_KEY = OFF,

    STATISTICS_NORECOMPUTE = OFF,

    ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON,

    DATA_COMPRESSION = NONE )

     ON [PRIMARY]

    )

    Tabbed text file:

    login      121         155         063         071         750         126

    bbbbby                x                                                                             

    ttttty     x                                                                             

    misery  x              X             X             X             X            

    ohmyohmy         x                                                                             

    whaty   X                                                                            

    jeezzy

    If column 126 in the text file does not have an 'X', my stored procedure is suppposed to delete the row in CRIS_USER_LOCATION for that cris_user_id if its location_id has a 126 value.

    Here is the SP:

    IF OBJECT_ID('TEMPDB..#LocationTemp') IS NULL BEGIN --DROP TABLE #LocationTemp CREATE TABLE #LocationTemp(UserLogin varchar(20),Loc_1 varchar(1),Loc_2 varchar(1),Loc_3 varchar(1),Loc_4 varchar(1), Loc_5 varchar(1), Loc_6 varchar(1)) BULK INSERT #LocationTemp FROM '\\path\UserLocation.txt' -- Path to the file.If this file is on server,be sure this file exists on the server. WITH (ROWTERMINATOR ='\n' -- New Line Feed (\n) automatically adds Carrige Return (\r) ,FIELDTERMINATOR = '\t' --delimiter ,FIRSTROW=2) PRINT 'File data copied to Temp table' END IF OBJECT_ID('TEMPDB..#LocationTemp3') IS NULL BEGIN CREATE TABLE #LocationTemp3(cris_user_id numeric(10, 0),location_id numeric(10,0) ) INSERT INTO #LocationTemp3(cris_user_id, location_id) SELECT CRIS_USER.id, + 126 FROM CRIS_USER JOIN #LocationTemp ON (CRIS_USER.login_id = #LocationTemp.UserLogin) WHERE #LocationTemp.Loc_6 IS NULL END

    BEGIN TRANSACTION

    DELETE CRIS_USER_LOCATION FROM CRIS_USER_LOCATION a INNER JOIN #LocationTemp3 b on b.cris_user_id = a.cris_user_id WHERE a.location_id = 126 COMMIT TRANSACTION END

    I need help with this part:

    IF OBJECT_ID('TEMPDB..#LocationTemp3') IS NULL
      BEGIN
        CREATE TABLE #LocationTemp3(cris_user_id numeric(10, 0),location_id numeric(10,0) )
        INSERT INTO #LocationTemp3(cris_user_id, location_id)
        SELECT CRIS_USER.id, + 126
        FROM   CRIS_USER JOIN #LocationTemp
           ON (CRIS_USER.login_id = #LocationTemp.UserLogin) 
           WHERE #LocationTemp.Loc_6 IS NULL
       END

    It doesn't seem to load #LocationTemp3. I know I can't use WHERE #LocationTemp.Loc_6 <> 'X'. If I place a 'D' in column 126 of the tabbed file for all of the ones that should be deleted it works with WHERE #LocationTemp.Loc_6 = 'D'. So I know that the last query works. How can I get #LocationTemp3 to load if the 126 column is blank?

    Thanks for any help.


    • Edited by DavidWIII Wednesday, June 26, 2013 5:44 PM
    Wednesday, June 26, 2013 5:36 PM

Answers

All replies