SQL statements for direct operations on two database tables

From: http://blog.163.com/haitaosong_accp/blog/static/5523025920094222105655/

Original table and data: (n original tables)

Tb1
Id aa bb cc dd stime
1 11 22 33 44 9-5-18
2 55 66 77 88 2009-5-19

Tb2
Id ee ff Gg HH stime
1 12 23 34 45 2009-5-18
2 56 67 78 89 2009-5-19

Import table: (To achieve the following effects)

ID sname svalue stime
1 A01 11 2009-5-18
2 B01 22 2009-5-18
3 c01 33 2009-5-18
4 d01 44 2009-5-18
5 A01 55 2009-5-19
6 B01 66 2009-5-19
7 c01 77 2009-5-19
8 d01 88 2009-5-19
9 e01 12 2009-5-18
10 F01 23 2009-5-18
11 g01 34 2009-5-18
12 h01 45 2009-5-18
13 e01 56 2009-5-19
14 F01 67 2009-5-19
15 g01 78 2009-5-19
16 h01 89 2009-5-19

If you want to import data from the original table to the table, the original table and the import table are not in the same database, and the field names of the tables are also different, but they can be written to an end,
For example, in tb1, the value of AA corresponds to the value of a81.
You can use a smallProgramThe condition is that the original table name and time are selected, and the data at the corresponding time of the table is imported

Case 1:

Create Table tb1

(

Id int, AA int, BB int, CC int, DD int, stime datetime

)

Create Table tb2

(

Id int, ee int, FF int, GG int, HH int, stime datetime

)

Insert into tb1

Select 1, 11, 22, 33, 44, '2017-5-18 'Union all

Select 2, 55, 66, 77, 88, '2017-5-19'

Insert into tb2

Select 1, 12, 23, 34, 45, '2017-5-18 'Union all

Select 2, 56, 67, 78, 89, '2017-5-19'

Select * From tb1

Select * From tb2

Create Table modelbaby5

(

Id int identity (1, 1), sname varchar (10), svalue varchar (10), stime datetime

)

Create proc tb1_tb1_to_modelbaby5

As

Insert into database name. DBO. modelbaby5

Select 'a01', AA, stime from DBO. tb1

Union all

Select 'b01', BB, stime from DBO. tb1

Union all

Select 'c01', CC, stime from DBO. tb1

Union all

Select 'd01', DD, stime from DBO. tb1

Union all

Select 'e01', EE, stime from DBO. tb2

Union all

Select 'f01', FF, stime from DBO. tb2

Union all

Select 'g01', GG, stime from DBO. tb2

Union all

Select 'h01', HH, stime from DBO. tb2

Exec tb1_tb1_to_modelbaby5

Select * From modelbaby5

 

Case 2: How can I insert data on different servers? For example, tb1 \ tb2 is in the base1 database on 192.168.1.1 server.
In the base2 database on the 192.168.1.2 server

/* Data operations between different Server databases */

-- Create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

Exec sp_add1_srvlogin 'itsv', 'false', null, 'username', 'Password'

-- Query example

Select * From itsv. Database Name. DBO. Table Name

-- Import example

Select * into table from itsv. Database Name. DBO. Table Name

-- Delete the linked server when it is no longer in use

Exec sp_dropserver 'itsv', 'droplogins'

-- Connect to remote/LAN data (OpenRowSet/openquery/OpenDataSource)

-- 1. OpenRowSet

-- Query example

Select * From OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. Table name)

-- Generate a local table

Select * into table from OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. Table name)

-- Import a local table to a remote table

Insert OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. Table name)

Select * from local table

-- Update local table

Update B

Set B. Column A = A. Column

From OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. Table Name) as a inner join local Table B

On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

-- Query

Select *

From openquery (itsv, 'select * from database. DBO. Table name ')

-- Import a local table to a remote table

Insert openquery (itsv, 'select * from database. DBO. Table name ')

Select * from local table

-- Update local table

Update B

Set B. Column B = A. Column B

From openquery (itsv, 'select * from database. DBO. Table name') as

Inner join local table B on A. Column A = B. Column

-- 3. OpenDataSource/OpenRowSet

Select *

From OpenDataSource ('sqlodb', 'Data source = IP/servername; user id = login name; Password = password'). Test. DBO. roy_ta

-- Import a local table to a remote table