How to copy table data between different databases

How to copy table data between different databases:

When the target table exists:

Insert into destination database... table select * from source database... table

If the target table does not exist:

Select * into destination database... table from source database... table

-- For different SQL statements:

Insert into OpenRowSet ('sqloledb', 'destination server name'; 'sa'; '', destination database. DBO. Table)
Select * from source database... table

-- Or use the link Server:
---------------------------------------- Create a linked server ------------------------------------
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqlodb', 'remote server name'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'sa', 'Password'
Exec sp_serveroption 'srv _ lnk ', 'rpc out', 'true' -- this allows you to call stored procedures on the linked server.
Go

-- Query example
Select * From srv_lnk. Database Name. DBO. Table Name

-- Import example
Select * into table from srv_lnk. Database Name. DBO. Table Name

Go
-- Delete the linked server
Exec sp_dropserver 'srv _ lnk ', 'droplogins'

-- If you want to copy all the data in one database to another, and the two databases have the same structure, backup/recovery will be used:
/* -- Copy one database to another --*/
/* -- Call example
Exec p_copydb @ ddbname = 'test'
--*/

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_copydb] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_copydb]
Go

Create proc p_copydb
@ Sdbname sysname = '', -- defines the name of the database to be copied. The default value is the current database.
@ Ddbname sysname, -- define the name of the database generated after Replication
@ Overexist bit = 1, -- whether to overwrite existing databases
@ Killuser bit = 1 -- whether to disable the user process. This parameter is only valid when @ overexist = 1.
As
Declare @ SQL varchar (8000), @ bpath varchar (8000), @ rpath varchar (8000)

-- Obtain the name of the database to be copied.
If isnull (@ sdbname, '') ='' set @ sdbname = db_name ()
-- Get the temporary backup data directory and file name
Select @ bpath = rtrim (reverse (filename) from Master .. sysfiles where name = 'master'
Select @ bpath = substring (@ bpath, charindex ('/', @ bpath) + 1,8000)
, @ Bpath = reverse (substring (@ bpath, charindex ('/', @ bpath), 8000) + 'backup /'
Mailto: + @ sdbname + % 20 _ % 20 + convert (varchar, getdate (), 112)
+ '_' + Replace (convert (varchar, getdate (), 108 ),':','')
+ '. Bak'

-- Generate Database Backup statements for Database Backup
Set @ SQL = 'backup database mailto: % 20 + @ sdbname
+ 'To mailto: disk = % 20% 20% 20 + @ bpath
+ ''' With noinit'
Exec (@ SQL)

-- Restore the database to a new one based on the backup file (copy finished)
Set @ SQL = 'Restore database mailto: % 20 + @ ddbname
+ 'From mailto: disk = % 20% 20% 20 + @ bpath +
+ 'With file = 1'
+ Case when @ overexist = 1 then', replace 'else' end

-- Get the default database directory
-- Obtain the data file path set during SQL installation.
Select @ rpath = rtrim (reverse (filename) from Master .. sysfiles where name = 'master'
Select @ rpath = reverse (substring (@ rpath, charindex ('/', @ rpath), 8000 ))

-- Processing of adding a Mobile logical File
-- Obtain the logical file name from the backup file
Declare @ LFN nvarchar (128), @ TP char (1), @ I int

-- Create a temporary table and save the obtained information
Create Table # Tb (LN nvarchar (128), PN nvarchar (260), TP char (1), FGN nvarchar (128), SZ numeric (20, 0 ), msz numeric (20, 0 ))
-- Obtain information from the backup file
Insert into # TB exec ('Restore filelistonly from mailto: disk = % 20% 20% 20 + @ bpath +)
Declare # F cursor for select ln, TP from # TB
Open # F
Fetch next from # F into @ LFN, @ TP
Set @ I = 0
While @ fetch_status = 0
Begin
Select @ SQL = @ SQL + ', move mailto: % 20% 20% 20 + @ LFN + to mailto: % 20% 20% 20 + @ rpath + @ ddbname + Cast (@ I as varchar)
+ Case @ TP when 'd 'then'. MDF ''' else'. ldf''' end
, @ I = @ I + 1
Fetch next from # F into @ LFN, @ TP
End
Close # F
Deallocate # F

-- Disable User process Processing
If @ overexist = 1 and @ killuser = 1
Begin
Declare @ spid varchar (20)
Declare # spid cursor
Select spid = cast (spid as varchar (20) from Master .. sysprocesses where dbid = db_id (@ ddbname)
Open # spid
Fetch next from # spid into @ spid
While @ fetch_status = 0
Begin
Exec ('Kill mailto: % 20 + @ spid)
Fetch next from # spid into @ spid
End
Close # spid
Deallocate # spid
End

-- Restore database
Exec (@ SQL)

-- Delete temporary backup files
Set @ SQL = 'del "mailto: % 20 + @ bpath + % 20% 22
Exec master .. xp_mongoshell @ SQL, no_output
Select @ SQL, @ bpath, @ rpath
Go

-- If you must copy tables one by one, use:
Use source database
Go
Exec sp_msforeachtable 'select * into target library ..? From? '

The best way is to use DTS (import and export tool) to prepare the DTS package.

-- If the two databases have different structures, use:

/* -- Database Data Replication
Copy data from one database to another
If a column is an identifier column in the target database, it will not be copied.
Applicability: the database structure has changed and you want to upgrade the old database.
In this way, you can create an empty database based on the new database structure, and then
Copy all data from the old database to the new database --*/

/* -- Call example

Exec p_copydb 'source database', 'target database'
Exec p_copydb 'Acc _ Wuzhi ', 'Acc _ demo data 8'
--*/

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_copydb] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_copydb]
Go

Create proc p_copydb
@ O_dbname sysname, -- database for Data Replication -- source database
@ N_dbname sysname, -- database for receiving data -- target database
@ Cleardb bit = 0 -- clear the target database
As
Declare @ SQL nvarchar (4000)

-- Disable constraints to prevent data conflicts during replication
Set @ SQL = 'Clare # TBC cursor for select name, tbname = object_name (parent_obj)
From mailto: % 20 + @ n_dbname + % 20 .. sysobjects where xtype in ('C', ''f '')'
Exec (@ SQL)
Declare @ name sysname, @ tbname sysname
Open # TBC
Fetch next from # TBC into @ name, @ tbname
While @ fetch_status = 0
Begin
Set @ SQL = 'alter table mailto: % 20 + @ n_dbname + % 20 .. [% 20 + @ tbname +] nocheck constraint ['+ @ name +']'
Exec (@ SQL)
Fetch next from # TBC into @ name, @ tbname
End
Close # TBC

-- Copy Data
Declare @ sql1 varchar (8000)
Set @ SQL = 'Clare # TB cursor for select a. Name from'
Mailto: + @ o_dbname + % 20 .. sysobjects a inner join'
Mailto: + @ n_dbname + % 20 .. sysobjects B on A. Name = B. Name
Where a. xtype = ''u'' and B. xtype = ''u '''
Exec (@ SQL)
Open # TB
Fetch next from # TB into @ tbname
While @ fetch_status = 0
Begin
Select @ sql1 =''
, @ SQL = 'select @ sql1 = @ sql1 + '', ['' + A. Name + '']'' from (
Select name from mailto: % 20 + @ o_dbname + % 20 .. syscolumns where ID in
(Select ID from mailto: % 20 + @ o_dbname + % 20 .. sysobjects where mailto: Name = % 20% 20% 20 + @ tbname +)
) A inner join (
Select name from mailto: % 20 + @ n_dbname + % 20 .. syscolumns where status <> 0x80 and ID in
(Select ID from mailto: % 20 + @ n_dbname + % 20 .. sysobjects where mailto: Name = % 20% 20% 20 + @ tbname +)
) B on A. Name = B. name'
Exec sp_executesql @ SQL, n' @ sql1 nvarchar (4000) out', @ sql1 out

Select @ sql1 = substring (@ sql1, 2,8000)
Exec ('insert into mailto: % 20 + @ n_dbname + % 20 .. [% 20 + @ tbname + % 20] (% 20 + @ sql1
+ ') Select mailto: % 20 + @ sql1 + from mailto: % 20 + @ o_dbname + % 20 .. [% 20 + @ tbname + % 20])
If @ error <> 0
Print ('insert into mailto: % 20 + @ n_dbname + % 20 .. [% 20 + @ tbname + % 20] (% 20 + @ sql1
+ ') Select mailto: % 20 + @ sql1 + from mailto: % 20 + @ o_dbname + % 20 .. [% 20 + @ tbname + % 20])
Fetch next from # TB into @ tbname
End
Close # TB
Deallocate # TB

-- Enable constraints after data replication is complete
Open # TBC
Fetch next from # TBC into @ name, @ tbname
While @ fetch_status = 0
Begin
Set @ SQL = 'alter table mailto: % 20 + @ n_dbname + % 20 .. [% 20 + @ tbname +] Check constraint ['+ @ name +']'
Exec (@ SQL)
Fetch next from # TBC into @ name, @ tbname
End
Close # TBC
Deallocate # TBC
Go