澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站server作业实现数据同步,不

2019-11-10 作者:数据库网络   |   浏览(116)

作业介绍

SQL Server:定时作业的设置方法

如果在SQL Server 里需要定时或者每隔一段时间执行某个存储过程或3200字符以内的SQL语句时,可以用管理->SQL Server代理->作业来实现。

1、管理->SQL Server代理->作业(按鼠标右键)->新建作业

2、 新建作业属性(常规)->名称[自定义本次作业的名称]->启用的方框内是勾号->分类处可选择也可用默认的[未分类(本 地)]->所有者默认为登录SQL Server用户[也可选其它的登录]->描述[填写本次工作详细描述内容];

创建作业分类的步骤: SQL Server代理->作业->右键选所有任务->添加、修改、删除

3、 新建作业属性(步骤)->新建->步骤名[自定义第一步骤名称]->类型[Transact-SQL(TSQL)脚本]->数据 库[要操作的数据库]->命令 [ 如果是简单的SQL直接写进去即可,也可用打开按钮输入一个已写好的*.sql。文件如果要执行存储过程,填 exec p_procedure_name v_parameter1,[ v_parameter2…v_parameterN] ->确定 (如果有多个步骤,可以再次调用下面的新建按钮;也可以对已有的多个步骤插入、编辑、删除);

4、建作业属性(调度)->新建调度->名称[自定义调度名称]->启用的方框内是勾号->调度->反复出现-> 更改[调度时间表]->确定(如果只要保存此作业,不要定时做可以把启用的方框内是勾号去掉);

5、建作业属性(通知)->用默认的通知方法就好[当作业失败时,写入Windows应用程序系统日志] ->确定。

跟作业执行相关的一些SQL Server知识:

SQL Server Agent服务必须正常运行,启动它的NT登录用户要跟启动SQL Server数据库的NT登录用户一致;

点作业右键可以查看作业执行的历史记录情况,也可以立即启动作业和停止作业。

最近在看作业历史记录时,发现有的作业记录的历史记录多,有的作业记录的记录的历史记录少。如何能使某些作业按各自的需求,保留一段时间.比如保留一个月的历史记录.看了SQL Server的在线帮助文档,里面介绍说:

在管理->SQL Server代理->右键选属性->作业系统->限制作业历史记录日志的大小

作业历史记录日志的最大大小(行数) ,默认为1000 。如果某台机器的作业数量很多,一定要提高它,例如为100000

每个作业历史记录日志的最大行数,默认为100。如果作业每天执行两次,需要保留一个月的日志,可以设为60

它们之间有一个相互制约关系, 我们可以根据自己的需要来改.

如 果SQL Server服务器改过机器名, 管理是旧名称时建立的job的时候可能会遇到。错误14274: 无法添加、更新或删除从MSX服务器上发起的作业(或其步骤或调度)。看了Microsoft的文 档:http://support.microsoft.com/default.aspx?scid=kb;en-us;281642说SQL Server 2000系统里msdb..sysjobs 里originating_server 字段里存的是原来的服务器的名称。24X7在用的系统肯定不能按上面Microsoft的文档说的那样把名字改回来又改过去。于是 想,msdb..sysjobs 能否update originating_server 字段成现在在用的新服务器名?

use msdb
select * from sysjobs

找到originating_server 字段还是旧服务器的job_id, 然后执行update语句:

update sysjobs set originating_server='new_server_name'
where job_id='B23BBEBE-A3C1-4874-A4AB-0E2B7CD01E14'

(所影响的行数为 1 行)

这样就可以添加、更新或删除那些曾经出error 14274 的作业了。

如果想把作业由一台机器迁移到另一台机器,可以先保留好创建作业的脚本, 然后在另一台机器上运行。

导出所有作业的创建脚本操作步骤:

管理->SQL Server代理->作业(鼠标右键)->所有任务->生成SQL脚本->保存到操作系统下的某个sql文件

导出某一个作业的创建脚本操作步骤:

管理->SQL Server代理->作业->选中待转移的作业(鼠标右键)->所有任务->生成SQL脚本->保存到OS下的某个sql文件

然 后在目的服务器上运行刚才保存下来的创建作业的sql脚本。( 如果建作业的用户或者提醒的用户不存在, 则会出错; 我们需要在目的服务器上建立相关的WINDOWS用户或者SQL Server数据库登录, 也可以修改创建作业的脚本, 把目的服务器上不存在的用户替换成已经有的用户。如果生成日志的物理文件目录不存在,也应该做相关的修改,例如d:区转f:区等字符串的 @command 命令里有分隔符号 go 也会出错, 可以把它去掉)

********************************************

--每月执行的作业
exec p_createjob @jobname='mm',@sql='select * from syscolumns',@freqtype='month'
--每周执行的作业
exec p_createjob @jobname='ww',@sql='select * from syscolumns',@freqtype='week'
--每日执行的作业
exec p_createjob @jobname='a',@sql='select * from syscolumns'
--每日执行的作业,每天隔4小时重复的作业
exec p_createjob @jobname='b',@sql='select * from syscolumns',@fsinterval=4
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_createjob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_createjob]
GO
create proc p_createjob
@jobname varchar(100),--作业名称
@sql varchar(8000),--要执行的命令
@dbname sysname='',--默认为当前的数据库名
@freqtype varchar(6)='day',--时间周期,month 月,week 周,day 日
@fsinterval int=1,--相对于每日的重复次数
@time int=170000--开始执行时间,对于重复执行的作业,将从0点到23:59分
as
if isnull(@dbname,'')='' set @dbname=db_name()
--创建作业
exec msdb..sp_add_job @job_name=@jobname
--创建作业步骤
exec msdb..sp_add_jobstep @job_name=@jobname,
@step_name = '数据处理',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔
--创建调度
declare @ftype int,@fstype int,@ffactor int
select @ftype=case @freqtype when 'day' then 4
when 'week' then 8
when 'month' then 16 end
,@fstype=case @fsinterval when 1 then 0 else 8 end
if @fsinterval<>1 set @time=0
set @ffactor=case @freqtype when 'day' then 0 else 1 end
EXEC msdb..sp_add_jobschedule @job_name=@jobname,
@name = '时间安排',
@freq_type=@ftype , --每天,8 每周,16 每月
@freq_interval=1,--重复执行次数
@freq_subday_type=@fstype,--是否重复执行
@freq_subday_interval=@fsinterval, --重复周期
@freq_recurrence_factor=@ffactor,
@active_start_time=@time --下午17:00:00分执行
-- 添加目标服务器
EXEC msdb.dbo.sp_add_jobserver
@job_name = @jobname ,
@server_name = N'(local)'
go
——创建存储过程—在“管理”—“作业”中进行操作

不同类型的数据库同步数据

项目中遇到某些数据,这些数据已经在其他项目中体现,直接把他们的表及表数据复制下来就好,但是在复制时发现,我用的是sql 2008,他们用的mysql,两个不同的数据库,生成的语句略有差别,由于比较懒,想用最省事的方式来同步数据,就在网上搜到了解决方案,参考实现,虽然网上类似的较多,但是毕竟是自己亲自体验过的,记录下来。

  1. 首先打开mysql操作界面(这省略了安装配置等步骤),输入想要连的数据库,进行验证链接的正确性。

 澳门新萄京官方网站 1

 

 

2 确认mysql的驱动是否存在,若存在则选择,打开控制面板—》管理工具—》数据源(ODBC)

 澳门新萄京官方网站 2

 

3 点击添加按钮

澳门新萄京官方网站 3

 

 

4  若有有下图所示类似的mysql驱动,则进行选择,若没有驱动这时就需要自己在官网上下载一个(),进行安装(步骤省略,下一步就行),安装完毕后重复以上操作,进入下如下图界面,进行选择—》点击完成

澳门新萄京官方网站 4

 

 

5 完成后会弹出该界面,按需填写信息—》Test 测试一下,若成功会有提示。

澳门新萄京官方网站 5

 

 

6 以上就是mysql驱动及连接的设置测试,以下是如何进行在数据库中操作

 

 

7 打开sql 2008 新建查询窗口如下图

澳门新萄京官方网站 6

 

澳门新萄京官方网站server作业实现数据同步,不同类型的数据库同步数据。 

8 把下面的代码拷贝到查询框中,点击执行(注意:@provstr这个属性中驱动一定要和刚才配置的保存一致【N'DRIVER={MySQL ODBC 5.3 ANSI Driver}】),然后就是链接地址

EXEC master.dbo.sp_addlinkedserver

@server = N'MYSQL', 

@srvproduct=N'MySQL',

@provider=N'MSDASQL',

@provstr=N'DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=192.16.10.101; _

 DATABASE=lcj; USER=root; PASSWORD=111111; OPTION=3'

9 执行成功,在SQL 2008 最外层服务器对象下中会出现如下图所示:

澳门新萄京官方网站 7

 

 

10 你可以在SQL 2008中新建一个库(dataabase),表可以不用创建,执行如下SQL语句把MySQL数据库lcj中的表stationd复制到新建库下

    SELECT * INTO [dataabase].dbo.stationd 

    FROM openquery(MYSQL, 'SELECT * FROM lcj.stationd') 

11  这是在同一SQL server下不同库【DHQXZB,dataabase】之间复制表的语句,顺便写上

SELECT * into DHQXZB.dbo.stationd from dataabase.dbo.stationd

 

---------------------------分割线----------------------------------

名词解释

 ODBC:开放数据库连接(Open Database Connectivity,ODBC)是为解决异构数据库间的数据共享而产生的,现已成为WOSA(The Windows Open System Arhitecture,Windows 开放系统体系结构)的主要部分和基于Windows环境的一种数据库访问接口标准ODBC 为异构数报库访问提供统一接口,允许应用程序以SQL 为数据存取标准,存取不同DBMS管理的数据;使应用程序直接操纵DB中的数据,免除随DB的改变而改变。用ODBC 可以访问各类计算机上的DB文件,甚至访问如Excel 表和ASCI I数据文件这类非数据库对象

SQLServer提供了多种数据导出导入的工具和方法,在此,分享我实践的经验(只涉及数据库与Excel、数据库与文本文件、数据库与数据库之间的导出导入)。

     SQL SERVER的作业是一系列由SQL SERVER代理按顺序执行的指定操作。作业可以执行一系列活动,包括运行Transact-SQL脚本、命令行应用程序、Microsoft ActiveX脚本、Integration Services 包、Analysis Services 命令和查询或复制任务。作业可以运行重复任务或那些可计划的任务,它们可以通过生成警报来自动通知用户作业状态,从而极大地简化了 SQL Server 管理[参见MSDN]。

(一)数据库与Excel

    创建作业、删除作业、查看作业历史记录....等所有操作都可以通过SSMS管理工具GUI界面操作,有时候也确实挺方便的。但是当一个实例有多个作业或多个数据库实例时,通过图形化的界面去管理、维护作业也是个头痛的问题,对于SQL脚本与GUI界面管理维护作业熟优熟劣这个问题,只能说要看场合。下面主要介绍通过SQL脚本来管理、维护作业。

方法1:

作业优劣

使用数据库客户端(SSMS)的界面工具。右键选择要导出数据的数据库,选择“任务”——“导出数据”,下图1,按照向导一步一步操作即可。而导入则相反,导入时,SQLServer会默认创建一张新表,字段名也默认跟导入的Excel标题一样,并且会默认字段数据类型等。当然在可以在向导进行修改。需要注意的是如果标题不是英文而是中文,默认创建字段名也是中文,这将给后面数据更新操作带来麻烦,所以最好还是以有意义的英文字段名。把数据导入后,再通过执行语句,把数据插入/更新到业务表。

     作业适合一些简单的操作,主要是定时执行一些计划,利用JOB定时执行,先清空本地数据,然后从远程LinkServer远程数据库提取数据插入当前数据库,方式简单,操作容易,但是效率低,不需要检查事物日志去比较差异。**
**

澳门新萄京官方网站 8

     但是频繁的清空,插入,如果数据量比较大时候。效率会很低,而且进行一些复杂的操作是有点困难。主要看你的需求是咋样。

 figure-1:任务——导出数据

实际例子

 

  需求:将一台服务器上的sql server2005某一些表的数据同步到另一台服务器装有sql server 2008的数据库中。在同步中,有一些数据4小时更新一次。另外一些2小时更新一次。

方法2:

   设计:由于操作简单我们首选采用server JOB来完成。在目标数据库中来完成作业创建。因为sql server2005版本有点低。定时将SourceDate中数据更新到目标数据库中,**

从SQLServer2005开始,可以直接在SSMS上查询出来的结果复制,然后粘贴到Excel上,对于少量数据来说,是非常快速方便的,需要注意的是长数字可能会变成科学记数法的形式,提前在Excel上指定列的格式为文本即可。

         首先删除目标数据库中数据,然后进行插入操作。有些需求不需要删除所有数据,但是你进行一些判断是否存在要更新和插入。在JOB不好操作,我暂时没有发现好的方法,只能将表清空,然后在插入,但是这样数据量大的时候就会慢下来。

导入的话,ctrl c 复制Excel上的数据,然后在选择相关表,编辑数据,把数据直接粘贴上去即可。但是不建议直接粘贴到业务表(如果表是空白没有数据,并且字段顺序对应,可以这样操作),而是建议先粘贴到一个新建的中间表中,然后再通过语句,把数据插入/更新到业务表。

         这里有两种办法实现设计,第一种就是源数据源中创建作业,另外一种是就是目标数据源中创建作业因为sql server2005版本有点低。定时将SourceDate中数据更新到目标数据库中,**

这种方法的导出导入,适合于少量的数据,如5000行以内的记录,大于5000行以上就不建议了,速度较慢,如果数据过大,还一定成功。

**         这里有两种办法实现远程数据库连接,第一种就是在创建作业链接远程数据库中采用脚本连接,另外一种是利用GUI界面中在链接服务器中创建连接即可,我个人认为有界面版的好一些。**

 

**   实现:①:在源数据库服务器上建立链接服务器。【服务器对象】-【链接服务器】-右键【新建链接服务器(N)...】-【常规】(如下图)**

 

 

(二)数据库与文本文件、数据库与数据库

 

数据库之间的数据迁移或导出导入其实是比较方便的,比如备份数据库后,在新的机器上做恢复。但是需要注意的是SQL2008之前的版本的备份无法在SQL2012或以上版本上直接恢复的,而是通过中间的SQL2008做一个过渡,把旧版本的数据库恢复到SQL2008,然后做备份,最后在SQL2012上恢复。

澳门新萄京官方网站 9

如果是新版本(下面以SQL2012为例)的备份文件恢复到旧版本(以SQL2008为例)上就比较麻烦了,一般是不支持新版本备份文件在旧版本中恢复的。只能通过编写脚本,把新版本的数据导入到旧版本中。

 

 

    注 意:链接服务器IP:192.168.1.65是目标数据库,建立这个链接服务器准备直接访问192.168.1.65的服务器上的数据库表。由于这里是 SQL SERVER 所以服务器类型选择SQL Server。

方法1:

 

首先推荐使用的是数据不落地的“链接服务器”。使用SQL2012的SSMS,同时连接到SQL2012和SQL2008的实例,通过编写脚本把SQL2012的数据导入到SQL2008中。两个实例的可以通过链接服务器来连接。以下是设置步骤。

   ②:选择【安全性】设置登录用户和密码,添加一个本地服务器登录到远程服务器的登录映射。然后【确定】(如下图)

澳门新萄京官方网站 10

 

figure-2:新建链接服务器

 

 

 

澳门新萄京官方网站 11

澳门新萄京官方网站 12

figure-3:链接服务器和数据源

 

 

 

澳门新萄京官方网站 13

 

figure-4:认证

    澳门新萄京官方网站server作业实现数据同步,不同类型的数据库同步数据。③:这时可以看到了一个192.168.1.56的链接服务器。右键【测试连接(T)】(如下图)

 

 

澳门新萄京官方网站 14

澳门新萄京官方网站 15

figure-5:创建成功后,可以直接浏览链接服务器的目录,也可以使用语句查询了。

 

 

 

也可以使用脚本来创建链接服务器。

 

--创建链接服务器
EXEC sp_addlinkedserver 
@server='LINKED_SERVER_TEST2',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.88.6,11433'--数据源
GO

--创建登录名和密码
EXEC sys.sp_addlinkedsrvlogin
@rmtsrvname = 'LINKED_SERVER_TEST2', -- 被访问的服务器别名
@useself = 'false',
@locallogin = NULL,
@rmtuser = 'sa', -- 数据源登录名
@rmtpassword = 'psd123456' -- 数据源登录密码
GO

--设置数据可以访问
EXEC sys.sp_serveroption
@server = 'LINKED_SERVER_TEST2', 
@optname = 'data access',
@optvalue = N'true'
GO

   ④:如果出现如下对话框则说明配置正确(如下图)

code-1:创建链接服务器的脚本

 

 

 

创建成功后,可以直接查询数据。

 

澳门新萄京官方网站 16

澳门新萄京官方网站 17

figure-6:查询链接服务器的数据

   

 

    在源数据库服务器上建立一个job主动的把数据push到目标数据库里去。

通过视图sys.servers可以查询所有服务器及相关的属性。

    ⑤:新建立作业:【SQL Server 代理】-【作业】-右键【新建作业(N)...】

澳门新萄京官方网站 18

 

figure-7:查询所有链接服务器

澳门新萄京官方网站 19

 

 

在SSMS上或运行以下脚本可以删除指定的链接服务器。

   ⑥:在【常规】中为作业起个名称(如下图)

--删除链接服务器及所有登录
EXEC sys.sp_dropserver
 @server = 'LINKED_SERVER_TEST2',
 @droplogins = 'droplogins'
 GO

 

 code-2:删除链接服务器及所有登录

澳门新萄京官方网站 20

 

 

详细请参考:

   ⑦:点击【步骤】,然后点击【新建(N)...】创建作业步骤(如下图)

 

 

 

澳门新萄京官方网站 21

方法2:

 

如果两个实例不能连接,只能在SQL2012上导出数据,再到SQL2008上导入。SQLServer提供生成包含数据的脚本工具,下图2。在第三步的“高级”选项里有一项“Types of data to scripts”有三个选择:Data only,Schema and data,Schema only,分别是只生成数据、生成表(对象)和数据,表(对象)。还有生成脚本的版本“Script for Server Version”,下图3。其他选项,按实际需要选择。

   ⑧:编辑步骤(如下图),然后【确定】

 

   从源数据库push数据到目标数据库的sql语句:

澳门新萄京官方网站 22

   truncate table [192.168.1.56].TargetData.[dbo].[TargetTable]

 figure-8:任务——生成脚本

   go

 

   insert into [192.168.1.56 ].TargetData.[dbo].[TargetTable ] select * from SourceData.[dbo].[SourceTable]

澳门新萄京官方网站 23

   go

figure-9:生成脚本的高级选项

 

 

澳门新萄京官方网站 24

也可以使用存储过程生成包含数据的脚本。这里介绍一个别人已经做写好存储过程:sp_generate_inserts。运行之后,会按表每条记录生成一条insert的语句

 

澳门新萄京官方网站 25澳门新萄京官方网站 26

   ⑨:新建作业计划(如下图),然后【确定】

CREATE PROC [dbo].[sp_generate_inserts]
    (
      @table_name VARCHAR(776) ,          -- The table/view for which the INSERT statements will be generated using the existing data
      @target_table VARCHAR(776) = NULL ,     -- Use this parameter to specify a different table name into which the data will be inserted
      @include_column_list BIT = 1 ,        -- Use this parameter to include/ommit column list in the generated INSERT statement
      @from VARCHAR(800) = NULL ,         -- Use this parameter to filter the rows based on a filter condition (using WHERE)
      @include_timestamp BIT = 0 ,         -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
      @debug_mode BIT = 0 ,            -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
      @owner VARCHAR(64) = NULL ,        -- Use this parameter if you are not the owner of the table
      @ommit_images BIT = 0 ,            -- Use this parameter to generate INSERT statements by omitting the 'image' columns
      @ommit_identity BIT = 1 ,        -- Use this parameter to ommit the identity columns
      @top INT = NULL ,            -- Use this parameter to generate INSERT statements only for the TOP n rows
      @cols_to_include VARCHAR(8000) = NULL ,    -- List of columns to be included in the INSERT statement
      @cols_to_exclude VARCHAR(8000) = NULL ,    -- List of columns to be excluded from the INSERT statement
      @disable_constraints BIT = 0 ,        -- When 1, disables foreign key constraints and enables them after the INSERT statements
      @ommit_computed_cols BIT = 1        -- When 1, computed columns will not be included in the INSERT statement

    )
AS 
    BEGIN

/***********************************************************************************************************
Procedure:    sp_generate_inserts  (Build 22) 
        (Copyright ?2002 Narayana Vyas Kondreddi. All rights reserved.)

Purpose:    To generate INSERT statements from existing data. 
        These INSERTS can be executed to regenerate the data at some other location.
        This procedure is also useful to create a database setup, where in you can 
        script your data along with your table definitions.

Written by:    Narayana Vyas Kondreddi
            http://vyaskn.tripod.com

Acknowledgements:
        Divya Kalra    -- For beta testing
        Mark Charsley    -- For reporting a problem with scripting uniqueidentifier columns with NULL values
        Artur Zeygman    -- For helping me simplify a bit of code for handling non-dbo owned tables
        Joris Laperre   -- For reporting a regression bug in handling text/ntext columns

Tested on:     SQL Server 7.0 and SQL Server 2000

Date created:    January 17th 2001 21:52 GMT

Date modified:    May 1st 2002 19:50 GMT

Email:         vyaskn@hotmail.com

NOTE:        This procedure may not work with tables with too many columns.
        Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types
        Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
        IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
        you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
        like nchar and nvarchar


Example 1:    To generate INSERT statements for table 'titles':

        EXEC sp_generate_inserts 'titles'

Example 2:     To ommit the column list in the INSERT statement: (Column list is included by default)
        IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
        to avoid erroneous results

        EXEC sp_generate_inserts 'titles', @include_column_list = 0

Example 3:    To generate INSERT statements for 'titlesCopy' table from 'titles' table:

        EXEC sp_generate_inserts 'titles', 'titlesCopy'

Example 4:    To generate INSERT statements for 'titles' table for only those titles 
        which contain the word 'Computer' in them:
        NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter

        EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"

Example 5:     To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
        (By default TIMESTAMP column's data is not scripted)

        EXEC sp_generate_inserts 'titles', @include_timestamp = 1

Example 6:    To print the debug information:

        EXEC sp_generate_inserts 'titles', @debug_mode = 1

Example 7:     If you are not the owner of the table, use @owner parameter to specify the owner name
        To use this option, you must have SELECT permissions on that table

        EXEC sp_generate_inserts Nickstable, @owner = 'Nick'

Example 8:     To generate INSERT statements for the rest of the columns excluding images
        When using this otion, DO NOT set @include_column_list parameter to 0.

        EXEC sp_generate_inserts imgtable, @ommit_images = 1

Example 9:     To generate INSERT statements excluding (ommiting) IDENTITY columns:
        (By default IDENTITY columns are included in the INSERT statement)

        EXEC sp_generate_inserts mytable, @ommit_identity = 1

Example 10:     To generate INSERT statements for the TOP 10 rows in the table:

        EXEC sp_generate_inserts mytable, @top = 10

Example 11:     To generate INSERT statements with only those columns you want:

        EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"

Example 12:     To generate INSERT statements by omitting certain columns:

        EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"

Example 13:    To avoid checking the foreign key constraints while loading data with INSERT statements:

        EXEC sp_generate_inserts titles, @disable_constraints = 1

Example 14:     To exclude computed columns from the INSERT statement:
        EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/

        SET NOCOUNT ON

--Making sure user only uses either @cols_to_include or @cols_to_exclude
        IF ( ( @cols_to_include IS NOT NULL )
             AND ( @cols_to_exclude IS NOT NULL )
           ) 
            BEGIN
                RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
                RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
            END

--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
        IF ( ( @cols_to_include IS NOT NULL )
             AND ( PATINDEX('''%''', @cols_to_include) = 0 )
           ) 
            BEGIN
                RAISERROR('Invalid use of @cols_to_include property',16,1)
                PRINT 'Specify column names surrounded by single quotes and separated by commas'
                PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
                RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
            END

        IF ( ( @cols_to_exclude IS NOT NULL )
             AND ( PATINDEX('''%''', @cols_to_exclude) = 0 )
           ) 
            BEGIN
                RAISERROR('Invalid use of @cols_to_exclude property',16,1)
                PRINT 'Specify column names surrounded by single quotes and separated by commas'
                PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
                RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
            END


--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is not allowed
        IF ( PARSENAME(@table_name, 3) ) IS NOT NULL 
            BEGIN
                RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
                RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
            END

--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just create a view on the system tables and script the view instead

        IF @owner IS NULL 
            BEGIN
                IF ( ( OBJECT_ID(@table_name, 'U') IS NULL )
                     AND ( OBJECT_ID(@table_name, 'V') IS NULL )
                   ) 
                    BEGIN
                        RAISERROR('User table or view not found.',16,1)
                        PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
                        PRINT 'Make sure you have SELECT permission on that table or view.'
                        RETURN -1 --Failure. Reason: There is no user table or view with this name
                    END
            END
        ELSE 
            BEGIN
                IF NOT EXISTS ( SELECT  1
                                FROM    INFORMATION_SCHEMA.TABLES
                                WHERE   TABLE_NAME = @table_name
                                        AND ( TABLE_TYPE = 'BASE TABLE'
                                              OR TABLE_TYPE = 'VIEW'
                                            )
                                        AND TABLE_SCHEMA = @owner ) 
                    BEGIN
                        RAISERROR('User table or view not found.',16,1)
                        PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
                        PRINT 'Make sure you have SELECT permission on that table or view.'
                        RETURN -1 --Failure. Reason: There is no user table or view with this name        
                    END
            END

--Variable declarations
        DECLARE @Column_ID INT ,
            @Column_List NVARCHAR(MAX) ,
            @Column_Name VARCHAR(128) ,
            @Start_Insert NVARCHAR(MAX) ,
            @Data_Type VARCHAR(128) ,
            @Actual_Values NVARCHAR(MAX) ,    --This is the string that will be finally executed to generate INSERT statements
            @IDN VARCHAR(128)        --Will contain the IDENTITY column's name in the table

--Variable Initialization
        SET @IDN = ''
        SET @Column_ID = 0
        SET @Column_Name = ''
        SET @Column_List = ''
        SET @Actual_Values = ''

        IF @owner IS NULL 
            BEGIN
                SET @Start_Insert = 'INSERT INTO '   '['
                      RTRIM(COALESCE(@target_table, @table_name))   ']' 
            END
        ELSE 
            BEGIN
                SET @Start_Insert = 'INSERT '   '['   LTRIM(RTRIM(@owner))
                      '].'   '['   RTRIM(COALESCE(@target_table, @table_name))
                      ']'         
            END


--To get the first column's ID

        SELECT  @Column_ID = MIN(ORDINAL_POSITION)
        FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
        WHERE   TABLE_NAME = @table_name
                AND ( @owner IS NULL
                      OR TABLE_SCHEMA = @owner
                    )


--Loop through all the columns of the table, to get the column names and their data types
        WHILE @Column_ID IS NOT NULL 
            BEGIN
                SELECT  @Column_Name = QUOTENAME(COLUMN_NAME) ,
                        @Data_Type = DATA_TYPE
                FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
                WHERE   ORDINAL_POSITION = @Column_ID
                        AND TABLE_NAME = @table_name
                        AND ( @owner IS NULL
                              OR TABLE_SCHEMA = @owner
                            )



                IF @cols_to_include IS NOT NULL --Selecting only user specified columns
                    BEGIN
                        IF CHARINDEX(''''   SUBSTRING(@Column_Name, 2,
                                                      LEN(@Column_Name) - 2)
                                       '''', @cols_to_include) = 0 
                            BEGIN
                                GOTO SKIP_LOOP
                            END
                    END

                IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
                    BEGIN
                        IF CHARINDEX(''''   SUBSTRING(@Column_Name, 2,
                                                      LEN(@Column_Name) - 2)
                                       '''', @cols_to_exclude) <> 0 
                            BEGIN
                                GOTO SKIP_LOOP
                            END
                    END

        --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
                IF ( SELECT COLUMNPROPERTY(OBJECT_ID(QUOTENAME(COALESCE(@owner,
                                                              USER_NAME()))
                                                       '.'   @table_name),
                                           SUBSTRING(@Column_Name, 2,
                                                     LEN(@Column_Name) - 2),
                                           'IsIdentity')
                   ) = 1 
                    BEGIN
                        IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
                            SET @IDN = @Column_Name
                        ELSE 
                            GOTO SKIP_LOOP            
                    END

        --Making sure whether to output computed columns or not
                IF @ommit_computed_cols = 1 
                    BEGIN
                        IF ( SELECT COLUMNPROPERTY(OBJECT_ID(QUOTENAME(COALESCE(@owner,
                                                              USER_NAME()))
                                                               '.'
                                                               @table_name),
                                                   SUBSTRING(@Column_Name, 2,
                                                             LEN(@Column_Name)
                                                             - 2),
                                                   'IsComputed')
                           ) = 1 
                            BEGIN
                                GOTO SKIP_LOOP                    
                            END
                    END

        --Tables with columns of IMAGE data type are not supported for obvious reasons
                IF ( @Data_Type IN ( 'image' ) ) 
                    BEGIN
                        IF ( @ommit_images = 0 ) 
                            BEGIN
                                RAISERROR('Tables with image columns are not supported.',16,1)
                                PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
                                PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
                                RETURN -1 --Failure. Reason: There is a column with image data type
                            END
                        ELSE 
                            BEGIN
                                GOTO SKIP_LOOP
                            END
                    END

        --Determining the data type of the column and depending on the data type, the VALUES part of
        --the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
        --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
                SET @Actual_Values = @Actual_Values
                      CASE WHEN @Data_Type IN ( 'char', 'varchar', 'nchar','nvarchar' )
                           THEN 'COALESCE(''N''''''   REPLACE(RTRIM('
                                  @Column_Name
                                  '),'''''''','''''''''''') '''''''',''NULL'')'
                           WHEN @Data_Type IN ( 'datetime', 'smalldatetime',
                                                'DATE','time' )
                           THEN 'COALESCE(''''''''   RTRIM(CONVERT(char,'
                                  @Column_Name   ',120)) '''''''',''NULL'')'
                           WHEN @Data_Type IN ( 'uniqueidentifier' )
                           THEN 'COALESCE(''''''''   REPLACE(CONVERT(char(255),RTRIM('
                                  @Column_Name
                                  ')),'''''''','''''''''''') '''''''',''NULL'')'
                           WHEN @Data_Type IN ( 'text', 'ntext' )
                           THEN 'COALESCE(''N''''''   REPLACE(CONVERT(char(8000),'
                                  @Column_Name
                                  '),'''''''','''''''''''') '''''''',''NULL'')'
                           WHEN @Data_Type IN ( 'binary', 'varbinary' )
                           THEN 'COALESCE(RTRIM(CONVERT(char,'
                                  'CONVERT(int,'   @Column_Name
                                  '))),''NULL'')'
                           WHEN @Data_Type IN ( 'timestamp', 'rowversion' )
                           THEN CASE WHEN @include_timestamp = 0
                                     THEN '''DEFAULT'''
                                     ELSE 'COALESCE(RTRIM(CONVERT(char,'
                                            'CONVERT(int,'   @Column_Name
                                            '))),''NULL'')'
                                END
                           WHEN @Data_Type IN ( 'hierarchyid' )
                           THEN 'COALESCE(''CAST('''''' LTRIM(RTRIM('
                                  'CONVERT(char, '   @Column_Name   ')'
                                  ')),''NULL'')'   ' ''''''AS  hierarchyid)'''
                           WHEN @Data_Type IN ( 'float', 'real', 'money',
                                                'smallmoney' )
                           THEN 'COALESCE(LTRIM(RTRIM('   'CONVERT(char, '
                                  @Column_Name   ',2)'   ')),''NULL'')'
                           ELSE 'COALESCE(LTRIM(RTRIM('   'CONVERT(char, '
                                  @Column_Name   ')'   ')),''NULL'')'
                      END   ' '   ''','''   '   '


        --Generating the column list for the INSERT statement
                SET @Column_List = @Column_List   @Column_Name   ','    

                SKIP_LOOP: --The label used in GOTO

                SELECT  @Column_ID = MIN(ORDINAL_POSITION)
                FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
                WHERE   TABLE_NAME = @table_name
                        AND ORDINAL_POSITION > @Column_ID
                        AND ( @owner IS NULL
                              OR TABLE_SCHEMA = @owner
                            )


    --Loop ends here!
            END

--To get rid of the extra characters that got concatenated during the last run through the loop
        SET @Column_List = LEFT(@Column_List, LEN(@Column_List) - 1)
        SET @Actual_Values = LEFT(@Actual_Values, LEN(@Actual_Values) - 6)

        IF LTRIM(@Column_List) = '' 
            BEGIN
                RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
                RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
            END

--Forming the final string that will be executed, to output the INSERT statements
        IF ( @include_column_list <> 0 ) 
            BEGIN
                SET @Actual_Values = 'SELECT '   CASE WHEN @top IS NULL
                                                           OR @top < 0 THEN ''
                                                      ELSE ' TOP '
                                                             LTRIM(STR(@top))
                                                             ' '
                                                 END   ''''
                      RTRIM(@Start_Insert)   ' '' '   '''('
                      RTRIM(@Column_List)   ''' '   ''')'''
                      '  ''VALUES(''  '   @Actual_Values   ' '')'''   ' '
                      COALESCE(@from,
                               ' FROM '   CASE WHEN @owner IS NULL THEN ''
                                               ELSE '['   LTRIM(RTRIM(@owner))
                                                      '].'
                                          END   '['   RTRIM(@table_name)   ']'
                                 '(NOLOCK)')
            END
        ELSE 
            IF ( @include_column_list = 0 ) 
                BEGIN
                    SET @Actual_Values = 'SELECT '
                          CASE WHEN @top IS NULL
                                    OR @top < 0 THEN ''
                               ELSE ' TOP '   LTRIM(STR(@top))   ' '
                          END   ''''   RTRIM(@Start_Insert)
                          ' ''  ''VALUES(''  '   @Actual_Values   ' '')'''
                          ' '   COALESCE(@from,
                                         ' FROM '
                                           CASE WHEN @owner IS NULL THEN ''
                                                ELSE '['   LTRIM(RTRIM(@owner))
                                                       '].'
                                           END   '['   RTRIM(@table_name)
                                           ']'   '(NOLOCK)')
                END    

--Determining whether to ouput any debug information
        IF @debug_mode = 1 
            BEGIN
                PRINT '/*****START OF DEBUG INFORMATION*****'
                PRINT 'Beginning of the INSERT statement:'
                PRINT @Start_Insert
                PRINT ''
                PRINT 'The column list:'
        --PRINT @Column_List
                PRINT ''
                PRINT 'The SELECT statement executed to generate the INSERTs'
                PRINT @Actual_Values
                PRINT ''
                PRINT '*****END OF DEBUG INFORMATION*****/'
                PRINT ''
            END

        --PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'
        --PRINT '--Build number: 22'
        --PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'
        --PRINT '--http://vyaskn.tripod.com'
        --PRINT ''
        --PRINT 'SET NOCOUNT ON'
        --PRINT ''


--Determining whether to print IDENTITY_INSERT or not
        IF ( @IDN <> '' ) 
            BEGIN
                PRINT 'SET IDENTITY_INSERT '   QUOTENAME(COALESCE(@owner,
                                                              USER_NAME()))
                      '.'   QUOTENAME(@table_name)   ' ON'
                PRINT 'GO'
                PRINT ''
            END


        IF @disable_constraints = 1
            AND ( OBJECT_ID(QUOTENAME(COALESCE(@owner, USER_NAME()))   '.'
                              @table_name, 'U') IS NOT NULL ) 
            BEGIN
                IF @owner IS NULL 
                    BEGIN
                        SELECT  'ALTER TABLE '
                                  QUOTENAME(COALESCE(@target_table,
                                                     @table_name))
                                  ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
                    END
                ELSE 
                    BEGIN
                        SELECT  'ALTER TABLE '   QUOTENAME(@owner)   '.'
                                  QUOTENAME(COALESCE(@target_table,
                                                     @table_name))
                                  ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
                    END

                PRINT 'GO'
            END

        PRINT ''
        PRINT 'PRINT ''Inserting values into '   '['
              RTRIM(COALESCE(@target_table, @table_name))   ']'   ''''


--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!
        EXEC (@Actual_Values)

        PRINT 'PRINT ''Done'''
        PRINT ''


        IF @disable_constraints = 1
            AND ( OBJECT_ID(QUOTENAME(COALESCE(@owner, USER_NAME()))   '.'
                              @table_name, 'U') IS NOT NULL ) 
            BEGIN
                IF @owner IS NULL 
                    BEGIN
                        SELECT  'ALTER TABLE '
                                  QUOTENAME(COALESCE(@target_table,
                                                     @table_name))
                                  ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
                    END
                ELSE 
                    BEGIN
                        SELECT  'ALTER TABLE '   QUOTENAME(@owner)   '.'
                                  QUOTENAME(COALESCE(@target_table,
                                                     @table_name))
                                  ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
                    END

                PRINT 'GO'
            END

        PRINT ''
        IF ( @IDN <> '' ) 
            BEGIN
                PRINT 'SET IDENTITY_INSERT '   QUOTENAME(COALESCE(@owner,
                                                              USER_NAME()))
                      '.'   QUOTENAME(@table_name)   ' OFF'
                PRINT 'GO'
            END

        --PRINT 'SET NOCOUNT OFF'


        SET NOCOUNT OFF
        RETURN 0 --Success. We are done!
    END
GO

 

View Code

澳门新萄京官方网站 27

code-3:sp_generate_inserts脚本源代码

 

 

   

在我的实际使用中,只有两三个参数比较常用,分别是@table_name、@from和@owner,如果表的架构使用默认的dbo,则可以省略。以下是一个使用的例子:

    ⑩:最终【确定】就可以了(如下图)

澳门新萄京官方网站 28

 

figure-10:使用sp_generate_inserts的一个例子

澳门新萄京官方网站 29

 

 

其他参数的用法,这里就不一一解释了。我经常使用这个存储过程做一些简单而少量(如数万行记录以内)的数据导出导入,比前面介绍的方法方便快捷许多。但这个存储过程支持处理一般常用的数据类型,像XML这种类型则不支持。还有,如果生成的数据太多太大,SSMS返回数据会很慢,甚至SSMS会挂了,这时还是使用SSMS自带的导出脚本到文件稳妥些。如果使用生成的数据脚本文件很大,几百MB甚至上GB,在导入时,就不能直接使用SSMS直接打开来执行了。可以使用SQLCMD实用工具来在执行脚本。如下面的一个例子,在D盘下有一个脚本1.sql,内容为:

   11:执行SQL JOB,右键点击刚新建的作业【PushDataToTarget】-【作业开始步骤(T)...】(如下图)

USE AdventureWorks2008R2
GO
SELECT * FROM Person.CountryRegion;
GO

 

code-4:SQLMCD的测试脚本

澳门新萄京官方网站 30

 

 

在运行下输入CMD,输入:

   12:结果出现下面的问题(如下图)

sqlcmd -S localhost -d AdventureWorks2008R2 -i D:1.sql

 

code-5:SQLMCD的命令

澳门新萄京官方网站 31

 

 

回车执行后如下图,SQLCMD的详细用法,请参考:

    13:然后去查看日志右键点击【PushDataToTarget】-【查看历史记录(V)】(如下图)

 

 

澳门新萄京官方网站 32

澳门新萄京官方网站 33

 

 figure-11:SQLCMD的测试例子

澳门新萄京官方网站 34

 

 

 

 

方法3:

=============================================================

使用BCP导出导入大容量数据。可以参阅我的另一篇博客《BCP导出导入大容量数据实践》。

 

 

 

以上几种方法是我在日常工作比较常使用的数据导出导入的工具,每一种方法都有各自的优势和不同的使用场景,使用不同的方法组合,可以节省不少时间,提高工作效率,希望对您的有所帮助。如果您有更好的建议或方法欢迎告诉我!

     方法二:在目标数据库服务器上建立一个链接服务器,让目标数据库主动的把数据pull到源数据库中。

 

    1:创建链接服务器 参照上方的方法,并测试链接成功。然后在目标数据中新建作业,如下图

 

澳门新萄京官方网站 35

 

  2、新建步骤(如下图)

  从源数据库pull数据的sql语句:

  truncate table TargetData.[dbo].[TargetTable]

  go

  insert into TargetData.[dbo].[TargetTable ] select * from [192.168.2.200].SourceData.[dbo].[SourceTable]

  go

 

澳门新萄京官方网站 36

 

 

   3、新建计划(如下图)

 

澳门新萄京官方网站 37

 

  4、最终【确定】

 

澳门新萄京官方网站 38

 

   5、右键点击【PullDataFromSource】-【作业开始步骤(T)...】

 

澳门新萄京官方网站 39

 

   6、执行结果如下

 

澳门新萄京官方网站 40

 

   7、再查看日志如下

 

澳门新萄京官方网站 41

 

  8:表明执行结果是成功的。从上面的操作中可以总结出:

  链接服务器的两个特点:

    1.通过链接服务器不能删除(including truncate)链接源服务器的任何对像.

    2:能过链接服务器可以对链接源服务器的表进行insert,updae,delete操作.

 2017-10-1314:58:20

2015.10.30 10:26 willem SqlServer2012 数据库同步的两种方式(SQL JOB 建立链接服务器)

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站server作业实现数据同步,不

关键词: