USE A /*在A库创建存储过程*/CREATE PROC SP_IMPORTDATA AS BEGIN DECLARE @i INT, @iCount INT, @Columns varchar(3000), @ExecStr VARCHAR(8000), @TableName VARCHAR(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 = '' /*这里是动态取表字段,自动增长的列和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 = ' INSERT INTO [B].[DBO].'+@TableName+'('+@Columns+') SELECT '+@Columns+' FROM '+@TableName EXEC(@ExecStr) SET @i = @i + 1 END END
然后在A库执行 EXEC SP_IMPORTDATA可起用代理服务,创建作业设置周期自动导入
SELECT @Columns = @Columns + NAME + ',' FROM syscolumns A 服务器: 消息 209,级别 16,状态 1,过程 SP_IMPORTDATA,行 42 列名 'name' 不明确。好像有这个问题。这句话是需要改动的,是么。
SELECT @Columns = @Columns + A.NAME + ',' FROM syscolumns A
我回头想了一下,这个好象不能满足你的需求,因为B库可能不存在TSQL201102这类的表。
调整一下 我在SQL SERVER 2005中是没有问题的。USE A /*在A库创建存储过程*/CREATE PROC SP_IMPORTDATA AS BEGIN DECLARE @i INT, @iCount INT, @Columns varchar(3000), @ExecStr VARCHAR(8000), @TableName VARCHAR(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 @tablenameWHILE (@@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 ENDCLOSE insertintotablebDEALLOCATE insertintotableb
你是光拷贝表结构过去?不是表内容? 那就把:SET @sql='insert into 数据库A.dbo.'+@tablename+' select * from 数据库B.dbo.'+@tablename+' with(nolock)'代码修改为:SET @sql='select top (0) * into 数据库A.dbo.'+@tablename+' from 数据库B.dbo.'+@tablename+' with(nolock)'
主要现在要在远程SQL2000上导数据,每次都是手动选择文件,还不能shift,再这样下去就练成一指禅了……肯定有代码能实现的,感觉。
提供个方案:1.在远程SQL上面写个SQL 存储过程,自动把你每天需要的数据导出,excel。
2.second copy软件 FTP 自动传到你要的电脑上面
hmmm……其实我不需要考虑版本问题,我只是想将这个local数据库的目标表导在另一个local数据库里,旨在免去每次在选择界面狂点鼠标的繁琐。
USE A /*在A库创建存储过程*/CREATE PROC SP_IMPORTDATA
AS
BEGIN
DECLARE @i INT,
@iCount INT,
@Columns varchar(3000),
@ExecStr VARCHAR(8000),
@TableName VARCHAR(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 = '' /*这里是动态取表字段,自动增长的列和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 = ' INSERT INTO [B].[DBO].'+@TableName+'('+@Columns+') SELECT '+@Columns+' FROM '+@TableName
EXEC(@ExecStr) SET @i = @i + 1
END
END
服务器: 消息 209,级别 16,状态 1,过程 SP_IMPORTDATA,行 42
列名 'name' 不明确。好像有这个问题。这句话是需要改动的,是么。
SELECT @Columns = @Columns + A.NAME + ',' FROM syscolumns A
AS
BEGIN
DECLARE @i INT,
@iCount INT,
@Columns varchar(3000),
@ExecStr VARCHAR(8000),
@TableName VARCHAR(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 @tablenameWHILE (@@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
ENDCLOSE insertintotablebDEALLOCATE insertintotableb
问一下,你的数据库是不是要求ID是个等差数列的,我怀疑是我表ID的问题。当然可能完全没干系,我只是个新手……
那就把:SET @sql='insert into 数据库A.dbo.'+@tablename+' select * from 数据库B.dbo.'+@tablename+' with(nolock)'代码修改为:SET @sql='select top (0) * into 数据库A.dbo.'+@tablename+' from 数据库B.dbo.'+@tablename+' with(nolock)'