Insert Into returns 0 Rows: Indexed Views Fault?

Question

Post reply

Insert Into returns 0 Rows: Indexed Views Fault?

Sonix Three

SSC Journeyman

Points: 96
More actions
November 18, 2004 at 8:57 am

#162814

Hi All,
I've got a very frustrating problem. I cannot seem to insert into a certain table using sytanx like:

Insert into table1 (col1, col2)
Select col1, col2 from Table2
i just get "0 Rows Affected" event though table2 (in this example) has got thousands of recordsd!
I've got a hunch that it's got something to do with an Indexed view using table1, anyone have a similar problem?

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

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

Rax SSC Enthusiast Points: 144 More actions · Answer 1

Can you share the indexed view script on the table1? Does table1 has any triggers?

Sonix Three SSC Journeyman Points: 96 More actions · Answer 2

SET QUOTED_IDENTIFIER ON

Create Unique Clustered Index vw_index ON vw_VoyageContainers(ID, colA, colB, colC)

I know that the above index doesn't match the tables exactly, but this is bascially what it looks like, i've just changed the column names.

It's definately to do with this index, as when I drop it, the Insert works fine.

I'm just wondering if they're a workaround or some sort of fix.

Mkumari Hall of Fame Points: 3498 More actions · Answer 3

If Index is the problem than droping the index on the view and recreating it after inserting the data should work right.

MK

Rax SSC Enthusiast Points: 144 More actions · Answer 4

Can you share the definition of view? Index create statement alone doesn't help. We need to see columns in the view and then analyze insert statement...

Sonix Three SSC Journeyman Points: 96 More actions · Answer 5

Hi All,

This is all of it:

Alter View vw_VoyageContainers

WITH SCHEMABINDING

AS

Select

vc.VoyageContainerID,

v.VoyageID,

c.ContainerID,

--v.ParentID,

v.VoyageRef,

vs.VesselName,

c.ContainerNo,

p.etd,

pol.PortName AS POL,

d.eta,

vt.VoyageType,

v.VesselID,

v.VoyageStatusID,

v.VoyageTypeID,

--v.CreatedDate,

v.FirstETA,

v.FirstETD,

p.Polid,

d.PODID

From

dbo.VoyageContainers vc

Inner JOIN

dbo.Voyages v ON

vc.VoyageID = v.VoyageID

Inner JOIN

dbo.Vessels vs ON

vs.VesselID = v.VesselID

Inner JOIN

dbo.VoyagePol p ON

p.VoyageID = v.VoyageID and

P.VoyageContainerID = vc.VoyageContainerID

Inner Join

dbo.Ports pol ON

pol.PortID = p.Polid

Inner JOIN

dbo.VoyagePod d ON

d.VoyageID = v.VoyageID and

d.VoyageContainerID = vc.VoyageContainerID

Inner Join

dbo.VoyageTypes vt ON

vt.VoyageTypeID = v.VoyageTypeID

Inner Join

dbo.Containers c ON

c.ContainerID = vc.ContainerID

GO

SET QUOTED_IDENTIFIER ON

Create Unique Clustered Index vw_VoyageContainers_ix ON vw_VoyageContainers(VoyageContainerID, ContainerID, polid, podid)

------------------

In reply to Mkumari : yeah that does work, but if i drop an index and re-create it every time a procedure is run, this would be very intensive right? exspecially since the tables can have thousands of entries..

Rax SSC Enthusiast Points: 144 More actions · Answer 6

OK...here is the potential problem. If you see in the View, there are many inner joins used. When you are going to insert row in any of the table used in this View, it is going to fire this entire Select statement and it is going to execute all inner joins. So if the inserted value doesn't satisfies the needs of inner join, I mean if it doesn't find any matching values based on the join clause, it will come up with zero rows.

Just to prove this, use this Select statement and replace the columns with the values you are inserting. If it comes with zero rows then definitely your Insert statement will also say zero rows inserted.

Do I make any sense....?

Sonix Three SSC Journeyman Points: 96 More actions · Answer 7

Problem solved! As you were suggesting it was the inner joins that were restricting it (they were trying to join on Foreign Keys that don't exist yet). I've changed the offending inner joins to Left joins and it works!

Thanks.