MERGE target cannot be a partitioned view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MERGE target cannot be a partitioned view

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-03-15 : 07:09:41

I'm new to using the MERGE statement. I want to merge the results of DB1 to those in DB2, but DB2 'tables' are in fact partitioned views. I get the error message:

Msg 5317, Level 16, State 1, Procedure InsertFromChargeBack, Line 22
The target of a MERGE statement cannot be a partitioned view.

So is there a work around, or is this just not allowed?

Hearty head pats

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-15 : 07:40:21

The error message is pretty clear so it doesn't look like it's possible. The workaround would probably be the old-fashioned

IF EXISTS (... )
  UPDATE ...
ELSE
  INSERT...

...or am I missing something obvious here?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-03-15 : 07:48:31

Yeah, alas, I think you are right. I guess I wasn't sure because in BOL it doesn't specify that you can't use a partitioned view:

target_table 
Is the table or view against which the data rows from <table_source> are matched based on <clause_search_condition>. target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement. 

If target_table is a view, any actions against it must satisfy the conditions for updating views. For more information, see Modifying Data Through a View. 

target_table cannot be a remote table. target_table cannot have any rules defined on it.

I'll probably go for:

INSERT...
IF @@ROWCOUNT = 0
UDPATE...

Thanks

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources