将数据从一个数据库导入另一个数据库
是否有T-SQL代码能够实现将一类表从一个数据库导入另一个数据库？
比如我这里想要将所有名字像TSQL201102%的表从本地数据库A导入本地数据库B，请问可有SQL能够实现？
[解决办法]
调整一下 我在SQL SERVER 2005中是没有问题的。
USE A /*在A库创建存储过程*/
CREATE PROC SP_IMPORTDATA
AS
BEGIN
DECLARE @iINT,
@iCountINT,
@Columnsvarchar(3000),
@ExecStrVARCHAR(8000),
@TableNameVARCHAR(40)
/*将TSQL201102 这一类的表查询出来插入到临时表*/
if exists(select * from tempdb.dbo.sysobjects where id = object_id(N'[tempdb].[dbo].[#tmp_TableList]'))
begin
drop table [dbo].[#tmp_TableList]
end
select distinct ID = IDENTITY(int,1,1),[NAME] AS TableName
into #tmp_TableList
FROM dbo.SysObjects
WHERE xtype = 'U'
AND [NAME] LIKE 'TSQL201102%'
SET @i = 1
SELECT @iCount = COUNT(1) FROM #tmp_TableList
IF @iCount < 1
BEGIN
RETURN-2
END
/*然后循环临时 将每个表的数据插入到B库对应的表*/
WHILE @i < @iCount+1
BEGIN
SELECT @TableName = TableName FROM #tmp_TableList
WHERE ID = @i
SET @Columns = ''
SET @ExecStr = ''
EXEC('if exists(select * from B.dbo.SysObjects where NAME = '''+@TableName+''' AND xtype = ''U'')
begin
drop table B.dbo.'+@TableName+'
end ')
EXEC('SELECT TOP 0 * INTO [B].[DBO].'+@TableName+' FROM '+@TableName)
/*这里是动态取表字段，自动增长的列和timestamp类型的数据不参与插入.
timestamp 这种数据类型表现自动生成的二进制数，确保这些数在数据库中是唯一的。
timestamp 一般用作给表行加版本戳的机制。存储大小为 8 字节*/
SELECT @Columns = @Columns + NAME + ',' FROM syscolumns A
LEFT OUTER JOIN systypes B ON a.xusertype=b.xusertype
LEFT OUTER JOIN SysObjects D ON A.ID = D.ID AND D.xtype = 'U'
WHERE D.NAME = @TableName
AND COLUMNPROPERTY(A.ID,A.NAME,'IsIdentity')<>1
AND B.[NAME] <> 'timestamp'
SET @Columns = SubString(@Columns,1,LEN(@Columns)-1)
SET @ExecStr = ''
SET @ExecStr = ' INSERT INTO [B].[DBO].'+@TableName+'('+@Columns+') SELECT '+@Columns+' FROM '+@TableName
EXEC(@ExecStr)
SET @i = @i + 1
END
END
DECLARE insertintotableb CURSOR FOR
SELECT NAME FROM sys.tables WHERE name LIKE 'TSQL201102%'
OPEN insertintotableb
DECLARE @tablename VARCHAR(8000)
FETCH NEXT FROM insertintotableb
INTO @tablename
WHILE (@@fetch_status=0)
BEGIN
DECLARE @sql VARCHAR(8000)
SET @sql='insert into 数据库A.dbo.'+@tablename+' select * from 数据库B.dbo.'+@tablename+' with(nolock)'
EXEC (@sql)
FETCH NEXT FROM insertintotableb
INTO @tablename
END
CLOSE insertintotableb
DEALLOCATE insertintotableb
