February 18, 2010 at 7:19 am
The set up is like so:
SERVER1
View A
View B
View C
View D
SERVER2
Table A
Table B
Table C
Table D
View E - Based on Table A - D and has clustered index
Ive used the Import/Export tool to create a package that moves data from Views A-D to Tables A-D but it is failing, the reason being that the package is set to TRUNCATE the table - this is not possible due to having a clustered index on View E.
Also, while running this the data in these tables is deleted and so there is an interim period where I do not have data (granted, only for a few seconds).
Note, I want all the records updated, I don't want data appended to the table.
I have looked at replication but to replicate the views on SERVER1 I need all the dependent tables - there are a lot.
Does anyone have any suggestions on the best way to achieve this?
February 18, 2010 at 10:00 am
I don't typically use a view for it is just a catalogued SQL Statement.
Why not use the SQL Statement as the source and the base table as the destination?
Are you inserting & updating records?
You can go into the SSIS Package Designer & Remove the SQL Task that truncates the table.
If you are updating then you need to Join on the Primary Key, or whatever columns that are needed.
With respect to Table E, why not populate the underling tables?
It would be nice to see the structure of the views and tables. I believe that you would get a more accurate response.
Does that make sense?
Regards...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply