Replicating a table at the end of each day

Last Post 24 May 2011 11:39 PM by river1. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
23 May 2011 07:28 AM
Hi Masters,


I have a database which as one table that i need to copy and send to other database.

I think i need do create a job on the source database with a bcp command to export the table structure and data (text file) to the other database.

In the other database (other computer) i think that i need to see if new files have arrived to the folder(some folder...)  and if so, import them , by first deleting the actual table and copying the new one and the data rows.

How would you master do this?

Thnak you
rm
New Member
New Member

--
23 May 2011 09:56 AM
Table schema changes everyday? If not, you can just copy data with ssis package. Otherwise copy whole table with 'copy db object task' in ssis.
river1
New Member
New Member

--
23 May 2011 10:01 AM
What i need is something that needs to be done in SQL Server 2000 but in the next 6 months / 1 year will probably be migrated to SQL Server 2005.
What is the simplest way to achieve this without having to do big changes later because of migration?
rm
New Member
New Member

--
23 May 2011 11:38 AM
Sql2k has dts, you can do same as with ssis.
river1
New Member
New Member

--
24 May 2011 05:16 AM
Yes, but then i will have to migrate the DTS to SSIS.

What about if i do a stored procedure that executes a bcp comand?

This don't need any upgrade when updating to SQL Server 2005.

What do you think?

Thank you.

P.S - Table Schema does not change, only data needs to be copied.
rm
New Member
New Member

--
24 May 2011 07:03 AM
You can run dts package in sql2k5.
gunneyk
New Member
New Member

--
24 May 2011 05:00 PM
If it's a single table and it is to be replaced in its entirety I would use bcp and bulk insert. I would use BCP to export the data to a file, copy it to the other server where another job would truncate the table and bulk insert the new one. Or something along those lines. If this needs to be more available you can load into another table first, drop the original and then rename this one. This is very flexable so you can adjust this to what ever your needs are. Since there is no data transformation I wouldn't bother with DTS or SSIS. Just my 2 cents.
river1
New Member
New Member

--
24 May 2011 11:39 PM
agree with you.

Thanks
You are not authorized to post a reply.

Acceptable Use Policy