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.
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
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.
> 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.
