Insert Problem

Question

Insert Problem

cdun2

SSCertifiable

Points: 7893
More actions
October 21, 2002 at 2:32 pm

#357633

Hello,
I have a table called 'Teacher_Data_Main' with the following fields:
*************
CREATE TABLE [Teacher_Data_Main] (
[STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCHOOLNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TCHNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LASTNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIRSTNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ABBREVNAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemUserName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TeacherID] AS ([tchnum] + [schoolnum]) ,
CONSTRAINT [UN_SchoolNum_Tchnum] UNIQUE NONCLUSTERED
(
[SCHOOLNUM],
[TCHNUM]
) ON [PRIMARY]
) ON [PRIMARY]
GO
**************
The other table in this situation is called 'Teacher_Data_MainTEMP', and it
looks like this:
**************
CREATE TABLE [Teacher_Data_MainTEMP] (
[STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCHOOLNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TCHNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LASTNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIRSTNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ABBREVNAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemUserName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
**************
I am trying to insert data into 'Teacher_Data_Main' from
'Teacher_Data_Main_TEMP'. 'Teacher_Data_Main_TEMP' is a 'temporary' holding
place for data. This table is populated via DTS from a DB4 database table.
The insert code is written so that records only go into 'Teacher_Data_Main'
if the record in 'Teacher_Data_MainTEMP' and not already in
'Teacher_Data_Main'.
The insert code looks like this:
***************
Insert Into Teacher_Data_Main
Select TMP.Status, TMP.Schoolnum, TMP.Tchnum,
TMP.LastName, TMP.FirstName, TMP.Abbrevname, TMP.systemusername
From Teacher_Data_MainTEMP TMP
Where TMP.Schoolnum Not In (Select Schoolnum from Teacher_Data_Main)
and TMP.Tchnum Not In (Select Tchnum from Teacher_Data_Main)
and TMP.Lastname Not In (Select Lastname from Teacher_Data_Main)
and TMP.Firstname Not In (Select Firstname from Teacher_Data_Main)
****************
This insert code is not working, as no new records in
'Teacher_Data_MainTEMP' are being inserted into 'Teacher_Data_Main' with
this code. This code works if I set the fields in the WHERE condition to
specific values like so:
Where TMP.Schoolnum = '368'
and TMP.Tchnum = '125'
and TMP.Lastname = 'Jones'
and TMP.Firstname = 'John'
Why would the insert code work with specific values to insert, but not
otherwise? Thanks.
CSDunn

Viewing 8 posts - 1 through 7 (of 7 total)

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

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

The code should be rewritten to left outer join the tables on the fields TMP.Schoolnum, TMP.Tchnum, TMP.Lastname, and TMP.Firstname

and check for it not being there by using a where clause such as

where TMP.primary key is null

and inserting these records.

With it as it is, any occurrance of any of these fields would prevent an insert, rather than checking for the existence of a row.

In other words, anyone having the same first name would not be inserted, anyone having the same schoolnum would not be inserted, etc....

If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....

NPeeters SSChampion Points: 12558 More actions · Answer 2

There is no real fundamental reason that your insert won't work. The problem is your data (or your query ).

Let me be more specific. The way your query is written, you do not check if a certain record is present in the real table. Instead, you check if a record exists that has the same SchoolNum, and you check if a record exists that has the same TCHNUM, and ...

But you never state that these values should be present in the same record.

Suppose you have following data in the Teacher_Data_Main :

 

TCHNUM SCHOOLNUM ...

1      1

2      1

1      2

1      3

Now, if you would try to insert a record from the temp table with TCHNUM = 3 and SCHOOLNUM = 1 it won't work because the first condition in your query is not met. Vice versa, if you would try to insert TCHNUM=1 and SCHOOLNUM=4, the second condition is not met.

I hope you get the point...

And now, for the solution...



INSERT INTO Teacher_Data_Main

(STATUS

,SCHOOLNUM

,TCHNUM

,LASTNAME

,FIRSTNAME

,ABBREVNAME

,SYSTEMUSERNAME)

(SELECT

 TMP.Status

,TMP.Schoolnum

,TMP.Tchnum

,TMP.LastName

,TMP.FirstName

,TMP.Abbrevname

,TMP.systemusername

FROM Teacher_Data_MainTEMP TMP

Where NOT EXISTS

(SELECT 1 FROM TEACHER_DATA_MAIN TDM WHERE

    TMP.SCHOOLNUM = TDM.SCHOOLNUM

AND TMP.TCHNUM = TDM.TCHNUM

AND TMP.LASTNAME = TDM.LASTNAME

AND TMP.FIRSTNAME = TDM.FIRSTNAME))

Edited by - NPeeters on 10/21/2002 3:21:18 PM

Edited by - NPeeters on 10/21/2002 3:23:00 PM

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 3

I was bored so I wrote it for you....

INSERT INTO Teacher_Data_MainTEMP

(STATUS, SCHOOLNUM, TCHNUM, LASTNAME, FIRSTNAME, ABBREVNAME, SystemUserName, TeacherId)

SELECT Teacher_Data_MainTEMP.STATUS, Teacher_Data_MainTEMP.SCHOOLNUM, Teacher_Data_MainTEMP.TCHNUM,

Teacher_Data_MainTEMP.LASTNAME, Teacher_Data_MainTEMP.FIRSTNAME, Teacher_Data_MainTEMP.ABBREVNAME,

Teacher_Data_MainTEMP.SystemUserName

Teacher_Data_MainTEMP.SCHOOLNUM + Teacher_Data_MainTEMP.TCHNUM,

FROM Teacher_Data_MainTEMP LEFT OUTER JOIN

Teacher_Data_Main ON Teacher_Data_MainTEMP.SCHOOLNUM = Teacher_Data_Main.SCHOOLNUM AND

Teacher_Data_MainTEMP.TCHNUM = Teacher_Data_Main.TCHNUM AND Teacher_Data_MainTEMP.LASTNAME = Teacher_Data_Main.LASTNAME AND

Teacher_Data_MainTEMP.FIRSTNAME = Teacher_Data_Main.FIRSTNAME

WHERE (Teacher_Data_Main.TCHNUM IS NULL)

And keep in mind that you may have to deal with concatenation issues with the TeacherId if you have nulls, etc...

If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

His will work as well, its just done with a subquery rather than a join....I didn't see it until I posted mine.

Actually, after looking at it closer, I notice he's using a not exists and a subquery, which I do not believe will be as efficient. Sorry Npeeters, I truly do not mean to step on toes, and if I am wrong, please show me where.

If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....

Edited by - Scorpion_66 on 10/21/2002 3:33:05 PM

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 5

I think the issue of NULLS is also a problem.

To be able to compare NULLS in an = clause you have to use SET ANSI_NULLS OFF

Have a look at "NULL Comparison Search Conditions" in BOL

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 6

you are right Simon, and I only thought about it in the concatenation, not in the join.....good catch...

If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....

NPeeters SSChampion Points: 12558 More actions · Answer 7

Scorpion_66,

Glad to learn something new. That's why I visit these pages .

Anyway, this looked like the simplest solution and I have to admit that I did not think about performance (after a 14 hour working day and a 3 hour drive).