--1) SSIS 做个包,完成数据的拷贝--2)用如下存储过程生成Insert语句,在将表名替换后执行.DROP PROC InsertGenerator GOSET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROC InsertGenerator (@tableName varchar(100) ) ASDECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName OPEN cursCol DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement DECLARE @dataType nvarchar(1000) --data types returned for respective columns SET @string='INSERT '+@tableName+'(' SET @stringData=''DECLARE @colName nvarchar(50)FETCH NEXT FROM cursCol INTO @colName,@dataTypeIF @@fetch_status<>0 begin print 'Table '+@tableName+' not found, processing skipped.' close curscol deallocate curscol return ENDWHILE @@FETCH_STATUS=0 BEGINIF @dataType in ('varchar','char') BEGIN SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+' END ELSE IF @dataType in ('nchar','nvarchar') BEGIN SET @stringData=@stringData+'''N'+'''+isnull('''''+'''''+REPLACE('+@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+' END ELSE if @dataType in ('text','ntext') BEGIN IF @dataType = 'text' BEGIN SET @stringData=@stringData+'''''''''+REPLACE(isnull(cast('+@colName+' as varchar(4000)),''''),'''''''','''''''''''')+'''''',''+' END ELSE BEGIN SET @stringData=@stringData+'''N'+'''''''+REPLACE(isnull(cast('+@colName+' as nvarchar(4000)),''''),'''''''','''''''''''')+'''''',''+' END END ELSE IF @dataType = 'money' BEGIN SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+' END ELSE IF @dataType='datetime' BEGIN SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+' END ELSE IF @dataType='image' BEGIN SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+' END ELSE BEGIN SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+' ENDSET @string=@string+@colName+','FETCH NEXT FROM cursCol INTO @colName,@dataType END DECLARE @Query nvarchar(4000)SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableNameexec sp_executesql @query CLOSE cursCol DEALLOCATE cursCol GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
那就用DTS
导入导出
那为什么不用select into
--1) SSIS 做个包,完成数据的拷贝--2)用如下存储过程生成Insert语句,在将表名替换后执行.DROP PROC InsertGenerator
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROC InsertGenerator
(@tableName varchar(100)
)
ASDECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''DECLARE @colName nvarchar(50)FETCH NEXT FROM cursCol INTO @colName,@dataTypeIF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
ENDWHILE @@FETCH_STATUS=0
BEGINIF @dataType in ('varchar','char')
BEGIN
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'
END
ELSE
IF @dataType in ('nchar','nvarchar')
BEGIN
SET @stringData=@stringData+'''N'+'''+isnull('''''+'''''+REPLACE('+@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext')
BEGIN
IF @dataType = 'text'
BEGIN
SET @stringData=@stringData+'''''''''+REPLACE(isnull(cast('+@colName+' as varchar(4000)),''''),'''''''','''''''''''')+'''''',''+'
END
ELSE
BEGIN
SET @stringData=@stringData+'''N'+'''''''+REPLACE(isnull(cast('+@colName+' as nvarchar(4000)),''''),'''''''','''''''''''')+'''''',''+'
END
END
ELSE
IF @dataType = 'money'
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE
BEGIN
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
ENDSET @string=@string+@colName+','FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableNameexec sp_executesql @query
CLOSE cursCol
DEALLOCATE cursCol
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
最后用了
insert into B select * from A
结了