SQL導入

精选转载

yangying1235 2013-12-26 11:19:18

文章标签 SQL 导入 文章分类 数据库

先將數據庫選擇到要導入的目標數據庫

--第一步：清空數據庫各表數據，執行如下SQL

DECLARE tables_cursor1 CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
and
name not in
---以下表不需要導入
('aspnet_Applications','aspnet_Membership','aspnet_Roles','aspnet_Users','aspnet_SchemaVersions','aspnet_UsersInRoles','aspnet_Membership','aspnet_Roles','User')--??用?名表

OPEN tables_cursor1 --//打開游表連接
DECLARE @tablename1 sysname   --// 定義變量
FETCH NEXT FROM tables_cursor1 INTO @tablename1   --//如果集中一行一行或取表名
WHILE (@@FETCH_STATUS <> -1) --//判斷游標狀態
BEGIN
EXEC (' TRUNCATE TABLE ' + @tablename1)   --//清空表中的數据
FETCH NEXT FROM tables_cursor1 INTO @tablename1 --//下一行數據据
END
close tables_cursor1
  DEALLOCATE tables_cursor1 --//釋放游標

--第二步：導入數據SQL

DECLARE tables_cursor1 CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
and
name not in

('aspnet_Applications','aspnet_Membership','aspnet_Roles','aspnet_Users','aspnet_SchemaVersions','aspnet_UsersInRoles','aspnet_Membership','aspnet_Roles','User')--??用?名表

--YY為原數據源的數據庫名稱
EXEC (' insert  into ' + @tablename1+'  select * from YY.dbo.'+@tablename1)   --//清空表中的數据
print @tablename1
print '----------------'
end try
begin catch
print @tablename1
end catch                                                                                                                                                                 FETCH NEXT FROM tables_cursor1 INTO @tablename1 --//下一行數據据
END
close tables_cursor1
  DEALLOCATE tables_cursor1 --//釋放游標