Export old data

Question

Export old data

alvirochka

SSC Rookie

Points: 39
More actions
November 22, 2010 at 12:37 am

#226907

I am using SQL Server 2008. There are two Servers: 1)Active Server "Server1" 2) Server for storing not important data or old data "Server2".
What the best way to Exporting old data from Server1 to Server2. For example: "Insert into Server2.t1 Select * from Server1.t1 where Server1.t1.dWhen <@from_date"

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

balaji.ganga SSCrazy Points: 2280 More actions · Answer 1

alvirochka (11/22/2010)
I am using SQL Server 2008. There are two Servers: 1)Active Server "Server1" 2) Server for storing not important data or old data "Server2".
What the best way to Exporting old data from Server1 to Server2. For example: "Insert into Server2.t1 Select * from Server1.t1 where Server1.t1.dWhen <@from_date"

Hi,

You can export a single table data.. you should build a linked server between t1 and t2. So that only you can access a servert2.

Or else if you want to export one or more tables. you can use import export wizard or ssis package.

Thanks

Balaji.G

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

There isn’t correct and wrong way. You can work with insert select and linked servers. Another way is to work with SSIS and write your own package. You can use the server’s wizard and you can export the data into file and then insert the data in the file to the target server. The way to go about it depends on several factors:

1) Is there a direct link between the 2 servers?

2) Can you work with linked servers?

3) Is the number of records is too big to work with one transaction?

4) With which of those ways you feel comfortable?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/

alvirochka SSC Rookie Points: 39 More actions · Answer 3

I have implemented Export by using the Linked Servers and following SP:

'SELECT Top 100 ' + @id_field + ' INTO #tmp_table FROM ' + @src_table WITH (NOLOCK) WHERE ' + @date_field + ' < ''' + CAST(@from_date AS varchar) + ''' ORDER BY id_field + ' ASC; INSERT INTO ' + @dst_table + ' SELECT * FROM ' + @src_table + ' WITH (NOLOCK) WHERE ' + @id_field + ' IN (SELECT * FROM #tmp_table);';

SET @query = @query + ' DELETE FROM ' + @src_table + ' WHERE ' + @id_field + ' IN (SELECT * FROM #tmp_table);';

SET @query = @query + ' DROP TABLE #tmp_table;';

EXEC(@query)

and by using transact SQL. The problem is that SP downs Server performance (I don't know why..), that is why I am looking for other ways of Exporting.