June 5, 2015 at 9:18 am
I have one job which runs in sql server and fetches data from teradata. Currently it's dropping existing table and recreating with all amount of data in teradata and its being done through linked server. So in future if amount of data becomes too large then migrating data through linked server would have performance issues. I want to modify my job to insert data in existing table only those entries which are not in sql server table. what is the good way to do it or can i modify my open query to insert only that data which doesn't exist insql sever.
June 5, 2015 at 9:33 am
madan.preeti2005 (6/5/2015)
I have one job which runs in sql server and fetches data from teradata. Currently it's dropping existing table and recreating with all amount of data in teradata and its being done through linked server. So in future if amount of data becomes too large then migrating data through linked server would have performance issues. I want to modify my job to insert data in existing table only those entries which are not in sql server table. what is the good way to do it or can i modify my open query to insert only that data which doesn't exist insql sever.
I can't imagine having to do more work and have it take less time. If you truncate the table and re-load from Teradata, whether via Linked Server or via an SSIS package via ODBC, having it compare still means having to bring ALL the data across the network in order to make that comparison. No amount of tweaking the query can eliminate the need to bring all the data across if you seek to compare so you can only insert what isn't there. And as pre-filtering the data by comparing on the Teradata side requires that you bring the data from SQL Server in the opposite direction, that's not going to do any better either.
I'd just use an SSIS package to do this via a SQL Agent job. Then you can use ODBC and not worry about a Linked Server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 5, 2015 at 10:03 am
Thank you so much. this is actually i was thinking that it will always be needed to bring data either way to make that comparison happen. I have never worked on other option using SSIS...any help would be appreciable.
Thank You
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply