SQL Server Performance

Insert Fails

Discussion in 'General Developer Questions' started by pametnjakovic, Sep 23, 2003.

  1. pametnjakovic New Member

    My first post, so forgive me if I'm out of line...

    I have a DB where I load user data using the following code:

    SET NOCOUNT ON
    DECLARE @GetDate Datetime
    SET @GetDate = Getdate()

    -- ... various other steps...
    ------------------------------------------------------------
    -- Step 7b. Add Records Required
    ------------------------------------------------------------
    INSERT INTO tblAgent
    SELECT 7074 AS Board_Code, *, @GetDate AS Date_Last_Update
    FROM tblAgent_New_Data New
    WHERE (New.[User_ID] IS NOT NULL)
    AND (New.[Password] IS NOT NULL)
    AND (New.Firm_Code IS NOT NULL)
    ------------------------------------------------------------
    tblAgent_New_Data has about 12 fields, including [User_ID], [Password],
    FName, LName, Phone, Email, etc.

    Everything works fine for 2 weeks. I come to work on Monday and find that
    out of 5000 rows of agent data loaded into tblAgent, 20-30 do not populate
    with [Password]. All the other fields have data, but [Password] is NULL.

    I have a DTS package that 1. Truncates tblAgent_New_Data, 2. Imports from ASCII tab-delimited, 3. Runs the various TSQL statements, like 7b above. Step 1 works fine; Step 2 works fine (and the [Password] exists in the table), but when Step 3 is completed, some records are missing the [Password].

    Any suggestions? I could make the table with [Password] set to never be NULL, but that doesn't explain why this could be happening.

    Thanks.



  2. Luis Martin Moderator

    Would you provide:
    1) SQL Version and SP.
    2) Fields defination in tblAgent.
    3) Any rule affect tblAgent.

    Thanks,

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. pametnjakovic New Member

    1. SQL Server 2000 Standard SP3
    2. As follows:

    CREATE TABLE [tblAgent] (
    [uid] [int] IDENTITY (1, 1) NOT NULL ,
    [Board_Code] [int] NOT NULL ,
    [User_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Password] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MI] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Full_Display_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Firm_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Agent_Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Agent_Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Access_Level] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Misc1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Misc2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Misc3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Date_Last_Update] [datetime] NULL
    ) ON [PRIMARY]

    3. No rules.

    4. tblAgent_New_Data is identical, except no uid, Board_code, or Date_Last_Update.
  4. bambola New Member

    > SELECT 7074 AS Board_Code, *, @GetDate AS Date_Last_Update

    Instead of using * specify the fields. If table structure (either one) had changed this insert would fail.

    INSERT INTO tblAgent (field1, field2.....)
    SELECT (field1, field2...)
    FROM tblAgent_New_Data...

    Bambola.


Share This Page