您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:数据库作业,导出导入的施行

新葡亰496net:数据库作业,导出导入的施行

发布时间:2019-10-20 23:48编辑:网络数据库浏览(74)

    作业介绍

    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代理->右键选属性->作业系统->限制作业历史记录日志的轻重

    学业历史记录日志的最大尺寸(行数) ,默感到一千。假诺某台机器的学业数量比非常多,一定要抓好它,比方为一千00

    各样作业历史记录日志的最大行数,默感到100。要是作业每一日实践三回,供给保留三个月的日记,能够设为60

    它们中间有三个交互制约关系, 我们得以依附自个儿的急需来改.

    如 果SQL Server服务器改过机器名, 管理是旧名称时创建的job的时候大概会遇上。错误14274: 不能够增加、更新或删除从MSX服务器上提倡的学业(或其步骤或调解)。看了Microsoft的文书档案:http://support.microsoft.com/default.aspx?scid=kb;en-us;281642说SQL Server 两千系统里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(八千),--要奉行的授命
    @dbname sysname='',--私下认可为当前的数据库名
    @freqtype varchar(6)='day',--时间周期,month 月,week 周,day 日
    @fsinterval int=1,--相对于每一日的重新次数
    @time int=1柒仟0--初步推行时间,对于再一次实践的学业,将从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 二〇〇九,他们用的mysql,五个例外的数据库,生成的语句略区别,由于比较懒,想用最简便的不二诀窍来共同数据,就在英特网搜到了消除方案,参照他事他说加以考察完结,即使网络类似的很多,不过毕竟是谐和亲自体验过的,记录下来。

    1. 先是张开mysql操作分界面(那省略了安装配备等手续),输入想要连的数据库,举办求证链接的不利。

     新葡亰496net 1

     

     

    2 确认mysql的驱动是或不是存在,若存在则选拔,展开调节面板—》管理工科具—》数据源(ODBC)

     新葡亰496net 2

     

    3 点击加多开关

    新葡亰496net 3

     

     

    4  若有有下图所示类似的mysql驱动,则张开抉择,若未有驱动那时就须求和谐在官网络下载一个(),进行设置(步骤省略,下一步就行),安装完成后再次以上操作,踏向下如下图分界面,实行抉择—》点击完结

    新葡亰496net 4

     

     

    5 实现后会弹出该分界面,按需填写消息—》Test 测量试验一下,若成功会有提醒。

    新葡亰496net 5

     

     

    6 以上便是mysql驱动及连接的装置测量试验,以下是哪些进展在数据库中操作

     

     

    7 展开sql 二〇一〇 新建查询窗口如下图

    新葡亰496net 6

     

     

    8 把上边包车型地铁代码拷贝到查询框中,点击实施(注意:@provstr那天脾气中驱动应当要和刚刚配置的保存黄金时代致【N'DRAV4IVE奇骏={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 二零一零 最外层服务器对象下中会出现如下图所示:

    新葡亰496net 7

     

     

    10 你能够在SQL 二〇〇八中新建贰个库(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管理的数码;使应用程序直接垄断(monopoly)DB中的数据,免除随DB的变动而退换。用ODBC 能够访谈各个Computer上的DB文件,以至访谈如Excel 表和ASCI I数据文件那类非数据库对象

    SQLServer提供了多样数码导出导入的工具和章程,在这里,分享小编试行的经历(只关乎数据库与Excel、数据库与公事文件、数据库与数据库之间的导出导入)。

         SQL SEXC60VEQX56的学业是风姿罗曼蒂克体系由SQL SEWranglerVESportage代理按梯次实行的钦定操作。作业能够实行一文山会海活动,包罗运维Transact-SQL脚本、命令行应用程序、Microsoft ActiveX脚本、Integration Services 包、Analysis Services 命令和查询或复制职务。作业能够运作重复职责或那贰个可布署的职分,它们能够透过更动警示来机关文告客商作业状态,进而相当的大地简化了 SQL Server 处理[参见MSDN]。

    (意气风发)数据库与Excel

        创立作业、删除作业、查看作业历史记录....等具备操作都足以由此SSMS管理工科具GUI界面操作,不时候也真正挺方便的。不过当一个实例有三个作业或七个数据库实例时,通过图形化的分界面去处理、维护作业也是个发烧的主题素材,对于SQL脚本与GUI分界面管理珍视作业熟优熟劣那几个标题,只好说要看地方。上面主要介绍通过SQL脚本来管理、维护作业。

    方法1:

    作业优劣

    使用数据库客商端(SSMS)的分界面工具。右键选取要导出数据的数据库,选取“任务”——“导出数据”,下图1,依据向导一步一步操作就能够。而导入则相反,导入时,SQLServer会默许创造一张新表,字段名也私下认可跟导入的Excel标题一样,况且会暗中同意字段数据类型等。当然在能够在带领进行更动。必要潜心的是只要标题不是波兰语而是汉语,暗中同意成立字段名也是华语,那将给末端数据更新操作带来劳动,所以最佳大概以有意义的日文字段名。把数量导入后,再经过施行语句,把多少插入/更新到业务表。

         作业符合部分简练的操作,首假若定期实施一些陈设,利用JOB定期实践,先清空本地数据,然后从远程LinkServer远程数据库提取数额插入当前数据库,方式轻巧,操作轻松,可是功能低,无需检讨东西日志去比较糟糕异。**
    **

    新葡亰496net 8

         不过屡屡的清空,插入,要是数据量比较大时候。成效会相当的低,何况拓展一些头昏眼花的操作是有点困难。重要看你的需若是怎么样。

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

    实质上例子

     

      必要:将大器晚成台服务器上的sql server二〇〇七某部分表的数据同步到另如日方升台服务器装有sql server 2010的数据库中。在大器晚成道中,有黄金时代部分数量4时辰更新三次。别的一些2钟头更新一回。

    方法2:

       设计:出于操作简便大家首荐选拔server JOB来成功。在指标数据库中来形成作业创立。因为sql server二〇〇五版本有一些低。按时将SourceDate中数量更新到指标数据库中,**

    从SQLServer2007发轫,能够一向在SSMS上询问出来的结果复制,然后粘贴到Excel上,对于一些些多少以来,是可怜火速方便的,须要小心的是长数字可能会成为科学记数法的情势,提前在Excel上钦命列的格式为文本就能够。

             首先删除目的数据库中数据,然后开展插队操作。有个别供给无需删除全数数据,但是你进行局地料定是或不是留存要立异和插入。在JOB倒霉操作,作者不常未有开采好的艺术,只可以将表清空,然后在插入,不过如此数据量大的时候就能够慢下来。

    导入的话,ctrl c 复制Excel上的多寡,然后在甄选有关表,编辑数据,把多少间接粘贴上去就可以。不过不提出直接粘贴到业务表(假若表是空白未有数量,何况字段顺序对应,能够这么操作),而是建议先粘贴到二个新建的中级表中,然后再通过言语,把数量插入/更新到业务表。

             这里有三种艺术落到实处设计,第风度翩翩种正是源数据源中创设作业,别的热气腾腾种是不怕指标数据源中创制作业因为sql server二〇〇六版本有一些低。定期将SourceDate中多少更新到对象数据库中,**

    这种方式的导出导入,切合于少些的数目,如陆仟行以内的笔录,大于四千行以上就不建议了,速度非常慢,假设数据过大,还一定成功。

    **         这里有两种情势落到实处长途数据库连接,第风姿洒脱种就是在开立作业链接远程数据库中运用脚本连接,其他后生可畏种是运用GUI分界面中在链接服务器中成立连接就可以,作者个人以为有分界面版的好有的。**

     

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

     

     

    (二)数据库与公事文件、数据库与数据库

     

    数据库之间的数量迁移或导出导入其实是比较平价的,比如备份数据库后,在新的机器上做复苏。可是须要注意的是SQL二零零六事先的本子的备份无法在SQL二零一二或以上版本上直接回复的,而是经过中间的SQL二〇〇八做一个连接,把旧版本的数据库苏醒到SQL二〇〇八,然后做备份,最终在SQL贰零壹叁上回复。

    新葡亰496net 9

    设如果新本子(下边以SQL二零一二为例)的备份文件苏醒到旧版本(以SQL二零零六为例)上就比较费心了,平常是不扶植新本子备份文件在旧版本中还原的。只可以通过编写制定脚本,把新本子的数码导入到旧版本中。

     

     

        注 意:链接服务器IP:192.168.1.65是目的数据库,建设构造这一个链接服务器计划直接访谈192.168.1.65的服务器上的数码库表。由于此处是 SQL SEHavalVE凯雷德 所以服务器类型选用SQL Server。

    方法1:

     

    第一推荐应用的是数码不一败涂地的“链接服务器”。使用SQL二零一三的SSMS,同不时间连接到SQL2013和SQL二〇〇九的实例,通过编写制定脚本把SQL2013的数额导入到SQL2008中。八个实例的能够透过链接服务器来延续。以下是设置步骤。

       ②:选拔【安全性】设置签到顾客和密码,增多八个本土服务器登陆到长途服务器的报到映射。然后【明确】(如下图)

    新葡亰496net 10

    新葡亰496net:数据库作业,导出导入的施行。 

    figure-2:新建链接服务器

     

     

     

    新葡亰496net 11

    新葡亰496net 12

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

     

     

     

    新葡亰496net 13

     

    figure-4:认证

        ③:那时能够看看了一个192.168.1.56的链接服务器。右键【测量检验连接(T)】(如下图)

     

     

    新葡亰496net 14

    新葡亰496net 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:创制链接服务器的剧本

     

     

     

    创制成功后,能够直接询问数据。

     

    新葡亰496net 16

    新葡亰496net 17

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

       

     

        在源数据库服务器上确立三个job主动的把多少push到对象数据库里去。

    透过视图sys.servers能够查询全部服务器及有关的性质。

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

    新葡亰496net 18

     

    figure-7:查询全体链接服务器

    新葡亰496net 19

     

     

    在SSMS上或运维以下脚本能够去除钦定的链接服务器。

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

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

     

     code-2:删除链接服务器及全数登入

    新葡亰496net 20

     

     

    详尽请参考:

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

     

     

     

    新葡亰496net 21

    方法2:

     

    假定多少个实例不能三回九转,只可以在SQL2013上导出数据,再到SQL二零零六上导入。SQLServer提供生成包涵数据的台本工具,下图2。在第三步的“高等”选项里有旭日东升项“Types of data to scripts”有多少个挑选:Data only,Schema and data,Schema only,分别是只生成多少、生成表(对象)和数据,表(对象)。还应该有生成脚本的本子“Script for Server Version”,下图3。其余选拔,按其实要求选取。

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

     

       从源数据库push数据到对象数据库的sql语句:

    新葡亰496net 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]

    新葡亰496net 23

       go

    figure-9:生成脚本的尖端选项

     

     

    新葡亰496net 24

    也能够应用存款和储蓄进程生成包括数据的脚本。这里介绍一人家已经做写好存款和储蓄进度:sp_generate_inserts。运营之后,会按表每条记录生成一条insert的讲话

     

    新葡亰496net 25新葡亰496net 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

    新葡亰496net 27

    code-3:sp_generate_inserts脚本源代码

     

     

       

    在小编的莫过于行使中,独有两八个参数相比较常用,分别是@table_name、@from和@owner,借使表的框架结构使用暗中同意的dbo,则能够简轻松单。以下是三个用到的例证:

        ⑩:最后【分明】就能够了(如下图)

    新葡亰496net 28

     

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

    新葡亰496net 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的测量试验脚本

    新葡亰496net 30

     

     

    在运维下输入CMD,输入:

       12:结果出现上面的主题材料(如下图)

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

     

    code-5:SQLMCD的命令

    新葡亰496net 31

     

     

    回车实施后如下图,SQLCMD的详细用法,请参见:

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

     

     

    新葡亰496net 32

    新葡亰496net 33

     

     figure-11:SQLCMD的测量试验例子

    新葡亰496net 34

     

     

     

     

    方法3:

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

    运用BCP导出导入大体量数据。能够参照笔者的另一日千里篇博客《BCP导出导入大容积数据实行》。

     

     

     

    以上二种办法是自作者在常常工作比较常使用的多寡导出导入的工具,每大器晚成种方式都有独家的优势和差别的使用景况,使用不相同的点子结合,能够节约不计其数光阴,提升级技术员作功用,希望对你的全体利于。如若您有更加好的提出或方式应接告诉自个儿!

         方法二:在指标数据库服务器上创造二个链接服务器,让对象数据库主动的把多少pull到源数据库中。

     

        1:成立链接服务器 参照上方的诀窍,并测量试验链接成功。然后在指标数据中新建作业,如下图

     

    新葡亰496net 35

     

    新葡亰496net,  2、新建步骤(如下图)

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

      truncate table TargetData.[dbo].[TargetTable]

      go

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

      go

     

    新葡亰496net 36

     

     

       3、新建安插(如下图)

     

    新葡亰496net 37

     

      4、最终【确定】

     

    新葡亰496net 38

     

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

     

    新葡亰496net 39

     

       6、实行结果如下

     

    新葡亰496net 40

     

       7、再查看日志如下

     

    新葡亰496net 41

     

      8:表明试行结果是水到渠成的。从地方的操作中得以总括出:

      链接服务器的多少个特征:

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

        2:能过链接服务器能够对链接源服务器的表打开insert,updae,delete操作.

     2017-10-1314:58:20

    二零一六.10.30 10:26 willem SqlServer二零一一 数据库同步的两种方法(SQL JOB 创设链接服务器)

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:数据库作业,导出导入的施行

    关键词:

上一篇:数据库中的范式,关周密据库设计理论

下一篇:没有了