Because it is often necessary to import data from one database into another, this function is written as a stored procedure for easy invocation. Now paste out for your reference:
Attention:
1, the following example uses a system view such as Syscolumns,sysobjects, in the sql2005 and later versions, you can also use sys.columns and sys.objects to replace, just modify the corresponding field.
2, the following example also uses the SET IDENTITY_INSERT setting, a friend who is not very clear about the setting can click the link to see its usage.
3, combined with sys.tables tables and cursors, you can loop through the following stored procedures to implement importing data from all tables in one database into another database.
Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go
Create PROCEDURE [dbo]. [Usp_sys_importdata] @SourceDBName varchar, @TargetDBName varchar (+), @TableName varchar
/*
Stored Procedure name: Usp_sys_importdata
Feature brief: Import the specified table data from the source database to the target database
Related objects:
Parameters: @SourceDBName varchar (128) Source database name
@TargetDBName varchar (128) Target database name
@TableName varchar (128) Table name
*/
SET NOCOUNT on;
DECLARE @TempSql varchar (max)
--Fetch the source table field (not including the computed column)
DECLARE @T_SourceColumn Table (ColumnID int not null,columnname varchar (+) NOT NULL)
Select @TempSql = ' Select T1.colid,t1.name '
+char (+) + ' from ' + @SourceDBName + ' ... syscolumns T1 '
+char + ' join ' + @SourceDBName + ' ... sysobjects t2 on t2.id = T1.id '
+char + ' Where t2.name = ' ' + @TableName + '
+char (Ten) + ' and t1.iscomputed = 0 '
+char (+) + ' ORDER by T1.colid '
Insert into @T_SourceColumn (columnid,columnname)
EXEC (@TempSql)
--Take the target table field (not including the computed column)
DECLARE @T_TargetColumn Table (ColumnID int not null,columnname varchar (+) NOT NULL)
Select @TempSql = ' Select T1.colid,t1.name '
+char (+) + ' from ' + @TargetDBName + ' ... syscolumns T1 '
+char + ' join ' + @TargetDBName + ' ... sysobjects t2 on t2.id = T1.id '
+char + ' Where t2.name = ' ' + @TableName + '
+char (Ten) + ' and t1.iscomputed = 0 '
+char (+) + ' ORDER by T1.colid '
Insert into @T_TargetColumn (columnid,columnname)
EXEC (@TempSql)
--Check if there are self-increment columns
DECLARE @T_TargetIdentityColumn Table (ColumnID int not null,columnname varchar (+) NOT NULL)
Select @TempSql = ' Select T1.colid,t1.name '
+char (+) + ' from ' + @TargetDBName + ' ... syscolumns T1 '
+char + ' join ' + @TargetDBName + ' ... sysobjects t2 on t2.id = T1.id '
+char + ' Where t2.name = ' ' + @TableName + '
+char (Ten) + ' and T1.colstat = 1 '
+char (+) + ' ORDER by T1.colid '
Insert into @T_TargetIdentityColumn (columnid,columnname)
EXEC (@TempSql)
DECLARE @HasIdentityColumn int
if exists (SELECT * from @T_TargetIdentityColumn)
Begin
Select @HasIdentityColumn = 1
End
Else
Begin
Select @HasIdentityColumn = 0
End
--Take the fields you want to import (common fields)
DECLARE @ColumnList varchar (max)
Select @ColumnList = "
Select @ColumnList = @ColumnList + ', ' + t1. ColumnName
From @T_SourceColumn T1,
@T_TargetColumn T2
where T1. ColumnName = t2. ColumnName
ORDER by T1. ColumnID
If left (@ColumnList, 1) = ', '
Begin
Select @ColumnList = substring (@ColumnList, 2,len (@ColumnList)-1)
End
If @ColumnList < > "
Begin
--Construct SQL to import data
DECLARE @ExecSql varchar (max)
Select @ExecSql = ' ALTER TABLE ' + @TargetDBName + ': ' + @TableName + ' Disable trigger all '
+ char (+) + ' Delete from ' + @TargetDBName + ' ... ' + @TableName
If @HasIdentityColumn = 1
Begin
Select @ExecSql = @ExecSql + char (Ten) + ' set Identity_insert ' + @TargetDBName + ' ... ' + @TableName + ' on '
End
Select @ExecSql = @ExecSql + char (TEN) + ' insert INTO ' + @TargetDBName + ': ' + @TableName + ' (' + @ColumnList + ') '
+ char (+) + ' SELECT ' + @ColumnList + ' from ' + @SourceDBName + ' ... ' + @TableName
If @HasIdentityColumn = 1
Begin
Select @ExecSql = @ExecSql + char (Ten) + ' set Identity_insert ' + @TargetDBName + ' ... ' + @TableName + ' off '
End
Select @ExecSql = @ExecSql + char (+) + ' ALTER TABLE ' + @TargetDBName + ': ' + @TableName + ' Enable trigger all '
Print @ExecSql
--Perform the import
EXEC (@ExecSql)
If @ @error = 0
Begin
Return 1
End
Else
Begin
Return-1
End
End
Else
Begin
Return 1
endhttp://www.lmwlove.com/ac/id807
Using SQL implementation to import the specified table data into another database sample
