SQL Server 导出插入数据脚本

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

1.利用Generate Scripts 向导

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

  a.

   b. 

   

 

 c.

 

d.

 

 

2. 利用sql 生成

 数据库表 batchbackup 的结构

 1 USE [eRebate]
 2 GO
 3 
 4 /****** Object:  Table [dbo].[Batchbackup]    Script Date: 03/16/2018 15:36:27 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 CREATE TABLE [dbo].[Batchbackup](
12     [Id] [int] IDENTITY(1,1) NOT NULL,
13     [Code] [nvarchar](max) NULL,
14     [Desc] [nvarchar](max) NULL,
15     [Date] [datetime] NULL,
16     [Status] [int] NOT NULL,
17     [BeforeStatus] [int] NOT NULL,
18     [TaskCode] [nvarchar](max) NULL,
19     [Policy_Id] [int] NULL,
20     [IsDeleted] [bit] NOT NULL,
21     [AreaType] [int] NOT NULL,
22  CONSTRAINT [PK_dbo.Batchbackup] PRIMARY KEY CLUSTERED 
23 (
24     [Id] ASC
25 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
26 ) ON [PRIMARY]
27 
28 GO
29 
30 ALTER TABLE [dbo].[Batchbackup] ADD  DEFAULT ((0)) FOR [IsDeleted]
31 GO
32 
33 ALTER TABLE [dbo].[Batchbackup] ADD  DEFAULT ((0)) FOR [AreaType]
34 GO

 

生成insert 语句：

 1 select  'INSERT INTO [eRebate].[dbo].[Batch]
 2            ([Id],
 3            [Code]
 4            ,[Desc]
 5            ,[Date]
 6            ,[Status]
 7            ,[BeforeStatus]
 8            ,[TaskCode]
 9            ,[Policy_Id]
10            ,[IsDeleted]
11            ,[AreaType])
12      VALUES('''
13       + CAST(Id as NVARCHAR) + ''','''
14       + Isnull(Code,'null')+ ''','''
15       + [Desc]+''','''
16       + convert(nvarchar,[date],20)+''','''
17       + CAST([status] as NVARCHAR) + ''','''
18       + CAST([BeforeStatus] as NVARCHAR) + ''','''
19       + Isnull(TaskCode,'null')+ ''','''
20       + Isnull(CAST(Policy_Id as NVARCHAR),'null') + ''','''
21       + CAST(IsDeleted as NVARCHAR) + ''','''
22       + CAST(AreaType as NVARCHAR) + ''')' as insertscript FROM dbo.Batch;

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

 

在结果前加上：

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

 

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

 

posted @ 2018-03-16 15:48  小布雷  阅读(424)  评论(0)    收藏  举报