Insert hardcoded values acting strange 1

[Catadmin]

ARGGH!!!

I have a table with a combined Primary Key, ValidProductID and OverRemitID. I'm trying to do an insert with hardcoded values, but the insert is acting strangely and duplicated 4 of the 6 records.

The pre-insert recordset looks like this:

VPID ORID Amt CreatedBy CreatedOn

426638 713 10 ITS-ME 2005-08-26 09:38:30.497
426638 1101 30 ITS-ME 2005-08-26 09:38:30.497
426639 713 10 ITS-ME 2005-08-26 09:38:30.497
426639 1101 30 ITS-ME 2005-08-26 09:38:30.497

Here's the Insert Statement:

Insert into tblOverRemit (ValidProductID, OverRemitID,
OverRemitAmount, CreatedBy, CreatedOn, EntryDate, ModifiedBy, ModifiedOn)
(Select 426637,	942, 28.0000, 'ITS-ME', '2005-08-26 09:38:30.497', NULL as EntryDate, NULL as ModifiedBy, NULL as ModifiedOn)
union 
(Select 426637,	1132, 12.0000, 'ITS-ME', '2005-08-26 09:38:30.497', NULL as EntryDate, NULL as ModifiedBy, NULL as ModifiedOn)
union 
(Select 426638,	942, 28.0000, 'ITS-ME', '2005-08-26 09:38:30.497', NULL as EntryDate, NULL as ModifiedBy, NULL as ModifiedOn)
union 
(Select 426638, 1132, 12.0000, 'ITS-ME', '2005-08-26 09:38:30.497', NULL as EntryDate, NULL as ModifiedBy, NULL as ModifiedOn)
union 
(Select 426639, 942, 28.0000, 'ITS-ME', '2005-08-26 09:38:30.497', NULL as EntryDate, NULL as ModifiedBy, NULL as ModifiedOn)
union 
(Select 426639,	1132, 12.0000, 'ITS-ME', '2005-08-26 09:38:30.497', NULL as EntryDate, NULL as ModifiedBy, NULL as ModifiedOn)

When I do the insert, I get the message back (6 rows affected). When I do a selecct on the table, the records for 426637 (which has no pre-existing records) inserts fine, but the records for 426638 and 426639 have each inserted twice, giving me 10 new records instead of the 6 records SQL Server has said I inserted!

New RecordSet:

VPID ORID Amt CreatedBy CreatedOn

426637 942 280 ITS-ME 2005-08-26 09:38:30.497
426637 1132 120 ITS-ME 2005-08-26 09:38:30.497
426638 713 100 ITS-ME 2005-08-26 09:38:30.497
426638 942 280 ITS-ME 2005-08-26 09:38:30.497
426638 942 280 ITS-ME 2005-08-26 09:38:30.497
426638 1101 300 ITS-ME 2005-08-26 09:38:30.497
426638 1132 120 ITS-ME 2005-08-26 09:38:30.497426638 1132 120 ITS-ME 2005-08-26 09:38:30.497426639 713 100 ITS-ME 2005-08-26 09:38:30.497
426639 942 280 ITS-ME 2005-08-26 09:38:30.497
426639 942 280 ITS-ME 2005-08-26 09:38:30.497
426639 1101 300 ITS-ME 2005-08-26 09:38:30.497
426639 1132 120 ITS-ME 2005-08-26 09:38:30.497
426639 1132 120 ITS-ME 2005-08-26 09:38:30.497

I've never seen this happen before with a hard coded value list. I've seen it, with an Insert stmt using a join to a multiple record table, but never when I supply the values to the table.

Does anyone have a clue what is happening here? I can repeat this ad-naseum all day. And yes, I know I can insert the records individually (that's what I'm going to do right now), but I'd like to know what caused this in the first place.

Thanks in advance!!!



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Catadmin]

Nevermind. I think this might be a trigger issue. If it turns out not to be, I'll post on this thread again later.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Catadmin]

That would be a no. I dropped the Insert triggers for this table and I'm still getting duplicate records.

Any ideas???



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[vongrunt]

How many rows (count(*)) before and after INSERT?

Are you using joins or something to display records?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Catadmin]

Before the insert, 4 rows. After the insert, 14 rows.

The select statement I'm using to view the rows is:

Select * from tblOverRemit where ValidProductID = 426638
or ValidProductID = 426639 or ValidProductID = 426637
Order by ValidProductID, OverRemitID

I've even just highlighted the first row up to the first ValidProductID, executed that line, and still come up with the dups.

Anyone know the name of a good mental institution? I'm probably going to need one by the end of this whole thing. @=/



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[vongrunt]

Hm... no joins, count difference is 10.

What says DBCC CHECKTABLE( 'tblOverRemit' ) WITH ALL_ERRORMSGS?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Catadmin]

AHA!! It was a trigger after all. For some reason, the trigger didn't drop when I tried to delete it the first time. I didn't find this out, of course, until I tried to recreate it. It took me 3 tries to get that trigger to drop (using the same code) and then I was able to run my code without inserting duplicate records.

Thanks for trying to help, vongrunt. Star for effort! @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[vongrunt]

Good... next thing on my mind included some odd rituals with candles, chicken legs & guts... penthagram more or less

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Catadmin]

Actually, I was going to call in a computer psychic. Ask her to communicate with all the dead DBAs buried in the server to see if they had any answers. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???