Click here to Skip to main content
15,900,108 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

i want to transfer tables data from one sql server:74.13.0.452
database: TestDB1
table Name:Table1
UserName: UN1
Password:PW1

to another like

sql server:198.0.168.101
database:TestDB2
table name:table2
UserName:UN2
password:PW2.
please tell me how to write query for this.
Posted

It would be handy to know what language you are looking to do this in? I do DB backups as follows in php on a mysql database. This e-mails me an sql file I can then import.

PHP
backup_tables('localhost','username','password','DB');


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
  
  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);
  
  //get all of the tables
  if($tables == '*')
  {
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
    {
      $tables[] = $row[0];
    }
  }
  else
  {
    $tables = is_array($tables) ? $tables : explode(',',$tables);
  }
  
  //cycle through
  foreach($tables as $table)
  {
    $result = mysql_query('SELECT * FROM `'.$table.'`');
	
    $num_fields = mysql_num_fields($result);
    
    $return.= 'DROP TABLE `'.$table.'`;';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE `'.$table.'`'));
    $return.= "\n\n".$row2[1].";\n\n";
    
    for ($i = 0; $i < $num_fields; $i++) 
    {
      while($row = mysql_fetch_row($result))
      {
        $return.= 'INSERT INTO '.$table.' VALUES(';
        for($j=0; $j<$num_fields; $j++) 
        {
          $row[$j] = addslashes($row[$j]);
          $row[$j] = ereg_replace("\n","\\n",$row[$j]);
          if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
          if ($j<($num_fields-1)) { $return.= ','; }
        }
        $return.= ");\n";
      }
    }
    $return.="\n\n\n";
  }
  
  //save file
  $filename = 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql';
  $handle = fopen($filename,'w+');
  fwrite($handle,$return);
  fclose($handle);
  $to = 'db@tornbuddy.com';
//define the subject of the email
$subject = 'DB Backup';
//create a boundary string. It must be unique
//so we use the MD5 algorithm to generate a random hash
$random_hash = md5(date('r', time()));
//define the headers we want passed. Note that they are separated with \r\n
$headers = "From: db@tornbuddy.com\r\nReply-To: no-reply@tornbuddy.com";
//add boundary string and mime type specification
$headers .= "\r\nContent-Type: multipart/mixed; boundary=\"PHP-mixed-".$random_hash."\"";
//read the atachment file contents into a string,
//encode it with MIME base64,
//and split it into smaller chunks
$attachment = chunk_split(base64_encode(file_get_contents($filename)));
//define the body of the message.
ob_start(); //Turn on output buffering
?>
--PHP-mixed-<?php echo $random_hash; ???> 
Content-Type: multipart/alternative; boundary="PHP-alt-<?php echo $random_hash; ???>"

--PHP-alt-<?php echo $random_hash; ???> 
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

DB backup
--PHP-alt-<?php echo $random_hash; ???> 
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

<h2>DB Backup</h2>
<?php echo $filename; ???>

--PHP-alt-<?php echo $random_hash; ???>--

--PHP-mixed-<?php echo $random_hash; ???> 
Content-Type: application/zip; name="db_backup.sql" 
Content-Transfer-Encoding: base64 
Content-Disposition: attachment 

<?php echo $attachment; ???>
--PHP-mixed-<?php echo $random_hash; ???>--

//copy current buffer contents into $message variable and delete current output buffer
$message = ob_get_clean();
//send the email
$mail_sent = @mail( $to, $subject, $message, $headers );
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed"
unlink($filename);
}
?>
 
Share this answer
 
v2
Comments
ks ravi 18-Oct-11 5:18am    
thanks for the reply but since i know c# language,i'm not understanding this,Please post that how to write query for this.
This MSDN article should help. Copying Data Between Servers[^].

Also, other options such as Linked server, Replication can also be used.
 
Share this answer
 
If you're talking about SQL Server 2005 and higher you might want to look into this:

Stored procedure sp_addlinkedserver[^].
Distributed queries stored procedures[^].

Regards,

—MRB
 
Share this answer
 
open Sqlserver IDE an connect 198.0.168.101 server

SQL
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SOURCE', @srvproduct=N'SQLOLEDB', @provider=N'SQLOLEDB', @datasrc=N'74.13.0.452\InstanceName'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SOURCE', @locallogin = NULL , @useself = N'False', @rmtuser = N'UN1', @rmtpassword = N'PW1'
GO

change the InstanceName to your sqlserver instance on 74.13.0.452 server
SQL
use TestDB2
select * from [source].TestDB1.dbo.Table1

-- transfer tables data from one sql server:74.13.0.452 to 198.0.168.101 
insert into table2
select * from [source].TestDB1.dbo.Table1
 
Share this answer
 
v3
u can use linked server.. please check below


SELECT TOP 1 @t_Cnt=1 FROM Master.dbo.sysservers Where Srvname= 'Patron'
IF(@t_Cnt<>1) --No Records Found then Create Link to Server
BEGIN

--Create Link to the Patron Server
EXEC Master.dbo.sp_addlinkedserver
@server = 'Patron',
@srvproduct = 'sqlserver',
@provider = 'SQLOLEDB',
@datasrc = '12.168.12.9' --Patron Server IP Address

--Create Login Credentitals to The Patron Server
EXEC Master.dbo.SP_ADDLINKEDSRVLOGIN 'Patron', 'false', null, 'sa', 'sa'

--Create RPC to Server
EXEC Master.dbo.sp_serveroption @server='Patron', @optname='rpc', @optvalue='true'
EXEC Master.dbo.sp_serveroption @server='Patron', @optname='rpc out', @optvalue='true'

END
--Execute Patient List in Patron Server
--Procedure : sp_GetSupplyList
--Database : Patron

EXEC Patron.Patron.dbo.sp_GetSupplyList
OR
SELECT * FROM Patron.Patron.dbo.TBL_Test

--Delete the Server link and Login Credentials
--EXEC Master.dbo.sp_dropserver 'Patron', 'droplogins'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900