SQL Server 导出插入数据脚本

对于sql sever中的数据如果想实现导出，一般有几种方案，下面介绍两种：

1.利用Generate Scripts 向导

   右键数据库=>tasks=>generate scripts

  a.

   b. 

   

 

 c.

 

d.

 

 

2. 利用sql 生成

 数据库表 batchbackup 的结构

USE [eRebate]
GO

/****** Object:  Table [dbo].[Batchbackup]    Script Date: 03/16/2018 15:36:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Batchbackup](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nvarchar](max) NULL,
    [Desc] [nvarchar](max) NULL,
    [Date] [datetime] NULL,
    [Status] [int] NOT NULL,
    [BeforeStatus] [int] NOT NULL,
    [TaskCode] [nvarchar](max) NULL,
    [Policy_Id] [int] NULL,
    [IsDeleted] [bit] NOT NULL,
    [AreaType] [int] NOT NULL,
 CONSTRAINT [PK_dbo.Batchbackup] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Batchbackup] ADD  DEFAULT ((0)) FOR [IsDeleted]
GO

ALTER TABLE [dbo].[Batchbackup] ADD  DEFAULT ((0)) FOR [AreaType]
GO

 

生成insert 语句：

select  'INSERT INTO [eRebate].[dbo].[Batch]
           ([Id],
           [Code]
           ,[Desc]
           ,[Date]
           ,[Status]
           ,[BeforeStatus]
           ,[TaskCode]
           ,[Policy_Id]
           ,[IsDeleted]
           ,[AreaType])
     VALUES('''
      + CAST(Id as NVARCHAR) + ''','''
      + Isnull(Code,'null')+ ''','''
      + [Desc]+''','''
      + convert(nvarchar,[date],20)+''','''
      + CAST([status] as NVARCHAR) + ''','''
      + CAST([BeforeStatus] as NVARCHAR) + ''','''
      + Isnull(TaskCode,'null')+ ''','''
      + Isnull(CAST(Policy_Id as NVARCHAR),'null') + ''','''
      + CAST(IsDeleted as NVARCHAR) + ''','''
      + CAST(AreaType as NVARCHAR) + ''')' as insertscript FROM dbo.Batch;

将生成的结果拷出并且替换掉 'null' => null 

 

在结果前加上：

SET QUOTED_IDENTIFIER ON
SET IDENTITY_INSERT [dbo].[Batch] ON

 

执行完毕即可插入到对应的表中