Link to home
Start Free TrialLog in
Avatar of sbornstein2
sbornstein2

asked on

Should I use replication, BCP, DTS etc. for the following scenario?

Hello all.  Here is what I have.  I have a Web Server that has a table with only two fields.  I want to update this table on a weekly basis on Sunday with only one field from a large table on my production database.  The production database table has over 4 million records.  I was thinking of transaction replication but it asks me to select a table etc.  I have set this up in the past but I think it did the whole table etc.  I want to keep the tables consistent as well.  Should I use transactional or snapshot or BCP a text file over to the Web box etc.  I never used BCP before but I know its a bulk copy.  On the Web server there is already a DTS package setup to load in a CSV file.  Thanks all
ASKER CERTIFIED SOLUTION
Avatar of Kevin Hill
Kevin Hill
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sbornstein2
sbornstein2

ASKER

Ya I have one table that has approx 4.5 Mil records and will increase even with an archive beleive it or not.   I have one field called Part number that I actually need to only take distinct and strip out any non alpha numeric characters from the field.  I have a function to handle this but the problem is it will take forever.  I want to run this on Saturday evening and have an updated table with the one field on my Web box which I already can connect to in SE.
So you are moving one part number, or 4.5 million part numbers?  If a large number, have the function dump the results into a temp table or working table and push those to the web database
4.5 million but there will be dups in there and part numbers a need to clean and strip off non alpha numeric
I think I will do what your saying.  I have the UDF which cleans the parts and then I do a DISTINCT on the parts using the function it just returned in about 15 minutes which is not terrible.  I guess I can then just do a clear table and append.  You think?
I might create a "scratch table", insert the distinct part numbers and then run the function to clean the numbers...then move them...just guessing at a high level here
ok ill try that.  thanks Kevin
cool....let us know if it doesn't work out, and thanks for the poinks