Title: use commands to import and export ms SQL data
1. Remote Data Import and Export-MSSQL Remote Data Import and Export
Select * into [local database name]... [Table name] from OPENDATASOURCE (
'Sqlodb ',
'Data Source = remote ip address; User ID = sa; Password = password'
). [Database name]. dbo. Table Name
Or
Insert [local database name]... [Table name] select * from OPENDATASOURCE (
'Sqlodb ',
'Data Source = remote ip address; User ID = sa; Password = password'
). [Database name]. dbo. Table Name
2. Use the connection Server:
-- Create a linked server
Declare @ rmtsrvname varchar (64)
Declare @ useself varchar (64)
Declare @ locallogin varchar (64)
Declare @ rmtuser varchar (64)
Declare @ rmtpassword varchar (64)
Exec sp_addmediaserver 'remote service alias ', '', 'msdasql', NULL, NULL, 'driver = {SQL Server}; SERVER = Remote Server IP address; UID = Remote SERVER user name; PWD = Remote Server user password ;'
Exec sp_addlinkedsrvlogin @ rmtsrvname = 'remote service alias ', @ useself = 'false', @ locallogin = 'sa', @ rmtuser = 'remote server username ', @ rmtpassword = 'remote Server user password' GO
Select * from remote service alias. Remote database name. dbo. Name of the remote database table
-- Delete the linked server after processing.
Exec sp_dropserver 'remote service alias ', droplogins
GO