May 4, 2009 at 12:26 pm
Hello rooms,
I divieded the original table on my transaction replication into two index views.
Original Table: T
able TestTable
PK1,PK2, PK3, PK4, col1, col2, col3,col4.
CREATE VIEW idxview1 WITH SCHEMABINDING AS
SELECT PK1, PK2, PK3, PK4, col1, col2
FROM dbo.TestTable
GO
CREATE VIEW idxview2 WITH SCHEMABINDING AS
SELECT PK1, PK2, PK3,PK4, col3, col4
FROM dbo.TestTable
GO
SELECT
idxView1.PK1,
idxView1.PK2,
idxview1.PK3,
idxview1.PK4,
idxview1.col1,
idxview1.col2,
idxview2.col3,
idxview2.col4
INTO TestTableFinal
FROM dbo.idxView1 INNER JOIN dbo.idxview2
ON dbo.idxview1.PK1 = dbo.idxview2.PK1
AND ON dbo.idxview1.PK2 = dbo.idxview2.PK2
AND ON dbo.idxview1.PK3 = dbo.idxview2.PK3
AND ON dbo.idxview1.PK4 = dbo.idxview2.PK4
GO
Can someone help and advice on my SELECT INTO statement?
Thanks,
May 4, 2009 at 12:40 pm
Edwin (5/4/2009)
Hello rooms,I divieded the original table on my transaction replication into two index views.
Original Table: T
able TestTable
PK1,PK2, PK3, PK4, col1, col2, col3,col4.
CREATE VIEW idxview1 WITH SCHEMABINDING AS
SELECT PK1, PK2, PK3, PK4, col1, col2
FROM dbo.TestTable
GO
CREATE VIEW idxview2 WITH SCHEMABINDING AS
SELECT PK1, PK2, PK3,PK4, col3, col4
FROM dbo.TestTable
GO
SELECT
idxView1.PK1,
idxView1.PK2,
idxview1.PK3,
idxview1.PK4,
idxview1.col1,
idxview1.col2,
idxview2.col3,
idxview2.col4
INTO TestTableFinal
FROM dbo.idxView1 INNER JOIN dbo.idxview2
ON dbo.idxview1.PK1 = dbo.idxview2.PK1
AND ON dbo.idxview1.PK2 = dbo.idxview2.PK2
AND ON dbo.idxview1.PK3 = dbo.idxview2.PK3
AND ON dbo.idxview1.PK4 = dbo.idxview2.PK4
GO
Can someone help and advice on my SELECT INTO statement?
Thanks,
Why do you need a "SELECT INTO" ?
* Noel
May 4, 2009 at 2:48 pm
I need to restore those index views into original table on my subscriber database
May 4, 2009 at 3:04 pm
I'm confused. It looks like you created two indexed views, and are joining those two views together to recreate the original table. Why?
May 4, 2009 at 3:17 pm
Since you already have the original table (the table the views select from), why not just use that as your source for the final table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 4, 2009 at 3:31 pm
Hi Lynn and GSquared,
Apologies that I did not explain clearly the original problems.
I have setup the transaction replication on the following environment:
1.Publishers (SQL 2000 SP3)
2.A remote distribution (SQL 2008)
3.A subscriber (SQL 2008)
There are several tables that have more than 250 columns in the Publisher databases.
SQL Server 2000 wont' support an publisher's articles with more than 250 columns.
So, I created Index views to split those tables.
Those index views was replicated over into my subscribers' databases.
My logics tell me that I need to combine those index views and restore into single table.
or
Can you advise me any better ways?
Thanks in advance.
Edwin
May 4, 2009 at 3:42 pm
The issue you'll have is that this isn't a one time thing. With replication, the "tables" will be changing constantly.
The easy way would be to insert from one table, and then update from the second table/indexed view, based on the PK.
May 4, 2009 at 3:52 pm
Or, create a view over the tables created by the replication of the two indexed views so that it appears as one table. Name the view the same as the original table on the publisher.
May 4, 2009 at 4:22 pm
Hi Steve,
Thanks. Can you explain in detail how to implement it?
May 5, 2009 at 7:44 am
Edwin (5/4/2009)
I need to restore those index views into original table on my subscriber database
Edwin, you just create a normal view with the name of the source table :
CREATE VIEW dbo.TestTableFinal AS
SELECT
idxView1.PK1,
idxView1.PK2,
idxview1.PK3,
idxview1.PK4,
idxview1.col1,
idxview1.col2,
idxview2.col3,
idxview2.col4
FROM dbo.idxView1 INNER JOIN dbo.idxview2
ON dbo.idxview1.PK1 = dbo.idxview2.PK1
AND ON dbo.idxview1.PK2 = dbo.idxview2.PK2
AND ON dbo.idxview1.PK3 = dbo.idxview2.PK3
AND ON dbo.idxview1.PK4 = dbo.idxview2.PK4
GO
That's it. Your Indexed Views will replicate as tables and your view will give the clients the "feeling" that all is
normal and they can read the combined info as if it was the real deal.
Hope it helps.
* Noel
May 5, 2009 at 7:48 am
noeld has a great solution, which I wish I'd thought of.
On the subscriber you'll have two tables that represent your indexed views. create a view over them.
select a.1, a.2, a.3, b.2, b.3, ...
from table1 a
inner join table2 b
on a.1 = b.1
May 5, 2009 at 8:10 am
Steve Jones - Editor (5/5/2009)
noeld has a great solution, which I wish I'd thought of.On the subscriber you'll have two tables that represent your indexed views. create a view over them.
select a.1, a.2, a.3, b.2, b.3, ...
from table1 a
inner join table2 b
on a.1 = b.1
Okay, I may not have put the code in my post, but I did suggest the very same thing as well. (See here)
May 5, 2009 at 9:19 am
Note that there are cases in which the subscriber could be used as a DR DB.
In that scenario, you can setup a trigger on the tables created by replication and populate a physical table
OR you can modify the replication stored procedures to do the maintenance right there.
Out of those two the second ensure better performance but is also more fragile.
Take your pick.
Good luck.
* Noel
May 5, 2009 at 9:37 am
Actually you'd need to be careful with my method either way. Since you don't know which table would necessarily get updated first, you'd need your triggers to check for matching PKs from inserted.* to the final table and either insert or update.
May 5, 2009 at 9:38 am
Lynn Pettis (5/5/2009)
Okay, I may not have put the code in my post, but I did suggest the very same thing as well.
Apologies, Lynn!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply