在工作过程中由于项目,经常需要将txt 或者xml文件的数据导入到数据库中去或者从数据库中导出,以下是自己写的直接用存储过程实现导入数据的方法
代码
5 bulk insert dbo.demotable from 'D:\code.txt'
6 with (
7 DATAFILETYPE = 'char',
8 FIELDTERMINATOR = ',',
9 ROWTERMINATOR = '\n',
10 --FIRSTROW=100,
11 --LASTROW=110,
12 ROWS_PER_BATCH=5000000000 )
13
14 alter procedure AddLotData
15 @StartRow int,
16 @EndRow int,
17 @Rows int,
18 @URL nvarchar(100)
19 as
20 declare @sql nvarchar(2000)
21 set @sql='
22 bulk insert dbo.demotable from '''+@URL+'''
23 with (
24 DATAFILETYPE = ''char'',
25 FIELDTERMINATOR = '','',
26 ROWTERMINATOR = ''\n'',
27 FIRSTROW='+convert(nvarchar,@StartRow)+','+'
28 LASTROW='+convert(nvarchar,@EndRow)+',
29 ROWS_PER_BATCH='+convert(nvarchar,@Rows)+' ) '
30 print @sql
31 exec sp_executesql @sql
32
33
34 --==========
35 exec AddLotData
36
37
38 USE [demo]
39 GO
40 /****** 对象: Table [dbo].[demotable] 脚本日期: 09/11/2009 17:32:58 ******/
41 SET ANSI_NULLS ON
42 GO
43 SET QUOTED_IDENTIFIER ON
44 GO
45 SET ANSI_PADDING ON
46 GO
47 CREATE TABLE [dbo].[demotable](
48 [id] [decimal](24, 0) IDENTITY(1,1) NOT NULL,
49 [info] [varchar](max) NULL,
50 CONSTRAINT [PK_demotable] PRIMARY KEY CLUSTERED
51 (
52 [id] ASC
53 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
54 ) ON [PRIMARY]
55
56 GO
57 SET ANSI_PADDING OFF
58
59
60 truncate table dbo.demotable
61
62 select top 100 * from demotable
63
64 create procedure BindInfo
65 as
66 select count(*) from demotable
67
68
69
70 EXEC master.dbo.sp_configure 'show advanced options', 1
71
72 RECONFIGURE
73
74 EXEC master.dbo.sp_configure 'xp_cmdshell', 1
75
76 RECONFIGURE
77
78
79
80 EXEC xp_cmdshell 'bcp "SELECT * FROM demo.dbo.demotable" queryout D:\data.txt -c -t, -S APASS-F992C009E -U sa -P sa'
81
82
83 EXEC master..xp_cmdshell 'bcp "Select * from demo..demotable" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'
84
85
86 EXEC master..xp_cmdshell 'bcp "Select * from demo..demotable" queryout D:\DT.txt -c -t, -S APASS-F992C009E -U sa -P sa'
87
88
89 bulk select * from 'D:\wl_ln_boxcode.txt'
90 with (
91 DATAFILETYPE = 'char',
92 FIELDTERMINATOR = ',',
93 ROWTERMINATOR = '\n',
94 FIRSTROW=7000000,
95 LASTROW=7000500
96 )
97
98
99 Declare @I int
100
101 Set @I = 0
102
103 Begin Tran
104
105 InsertData:
106
107 Insert into dbo.demotable(info,remark) values (@I, @I)
108
109 Set @I = @I + 1
110
111 If @I < 100000
112
113 Goto InsertData
114
115 Commit Tran
116
117
118
119 create proc ADDdemoInfo
120 @info varchar(max)
121 as
122 insert into dbo.demotable(info)
123 values(@info)
124
125 create proc DelDemoinfo
126 @id int
127 as
128 delete from dbo.demotable where id=@id
129
130 create proc QueryInfo
131 @Info varchar(50)
132 as
133 declare @sql nvarchar(2000)
134 set @sql='select * from demotable where info like ''%'+@Info+'%'''
135
136 print @sql
137 exec SP_executesql @sql
138
139 create proc UpdateInfo
140 @id int,
141 @info varchar(50)
142 as
143 update dbo.demotable set info=@info where id=@id
144
145 create proc BindUpdate
146 @id int
147 as
148 select * from dbo.demotable where id=@id
149
150
151 create procedure ClearAll
152 as
153 truncate table dbo.demotable
5 bulk insert dbo.demotable from 'D:\code.txt'
6 with (
7 DATAFILETYPE = 'char',
8 FIELDTERMINATOR = ',',
9 ROWTERMINATOR = '\n',
10 --FIRSTROW=100,
11 --LASTROW=110,
12 ROWS_PER_BATCH=5000000000 )
13
14 alter procedure AddLotData
15 @StartRow int,
16 @EndRow int,
17 @Rows int,
18 @URL nvarchar(100)
19 as
20 declare @sql nvarchar(2000)
21 set @sql='
22 bulk insert dbo.demotable from '''+@URL+'''
23 with (
24 DATAFILETYPE = ''char'',
25 FIELDTERMINATOR = '','',
26 ROWTERMINATOR = ''\n'',
27 FIRSTROW='+convert(nvarchar,@StartRow)+','+'
28 LASTROW='+convert(nvarchar,@EndRow)+',
29 ROWS_PER_BATCH='+convert(nvarchar,@Rows)+' ) '
30 print @sql
31 exec sp_executesql @sql
32
33
34 --==========
35 exec AddLotData
36
37
38 USE [demo]
39 GO
40 /****** 对象: Table [dbo].[demotable] 脚本日期: 09/11/2009 17:32:58 ******/
41 SET ANSI_NULLS ON
42 GO
43 SET QUOTED_IDENTIFIER ON
44 GO
45 SET ANSI_PADDING ON
46 GO
47 CREATE TABLE [dbo].[demotable](
48 [id] [decimal](24, 0) IDENTITY(1,1) NOT NULL,
49 [info] [varchar](max) NULL,
50 CONSTRAINT [PK_demotable] PRIMARY KEY CLUSTERED
51 (
52 [id] ASC
53 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
54 ) ON [PRIMARY]
55
56 GO
57 SET ANSI_PADDING OFF
58
59
60 truncate table dbo.demotable
61
62 select top 100 * from demotable
63
64 create procedure BindInfo
65 as
66 select count(*) from demotable
67
68
69
70 EXEC master.dbo.sp_configure 'show advanced options', 1
71
72 RECONFIGURE
73
74 EXEC master.dbo.sp_configure 'xp_cmdshell', 1
75
76 RECONFIGURE
77
78
79
80 EXEC xp_cmdshell 'bcp "SELECT * FROM demo.dbo.demotable" queryout D:\data.txt -c -t, -S APASS-F992C009E -U sa -P sa'
81
82
83 EXEC master..xp_cmdshell 'bcp "Select * from demo..demotable" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'
84
85
86 EXEC master..xp_cmdshell 'bcp "Select * from demo..demotable" queryout D:\DT.txt -c -t, -S APASS-F992C009E -U sa -P sa'
87
88
89 bulk select * from 'D:\wl_ln_boxcode.txt'
90 with (
91 DATAFILETYPE = 'char',
92 FIELDTERMINATOR = ',',
93 ROWTERMINATOR = '\n',
94 FIRSTROW=7000000,
95 LASTROW=7000500
96 )
97
98
99 Declare @I int
100
101 Set @I = 0
102
103 Begin Tran
104
105 InsertData:
106
107 Insert into dbo.demotable(info,remark) values (@I, @I)
108
109 Set @I = @I + 1
110
111 If @I < 100000
112
113 Goto InsertData
114
115 Commit Tran
116
117
118
119 create proc ADDdemoInfo
120 @info varchar(max)
121 as
122 insert into dbo.demotable(info)
123 values(@info)
124
125 create proc DelDemoinfo
126 @id int
127 as
128 delete from dbo.demotable where id=@id
129
130 create proc QueryInfo
131 @Info varchar(50)
132 as
133 declare @sql nvarchar(2000)
134 set @sql='select * from demotable where info like ''%'+@Info+'%'''
135
136 print @sql
137 exec SP_executesql @sql
138
139 create proc UpdateInfo
140 @id int,
141 @info varchar(50)
142 as
143 update dbo.demotable set info=@info where id=@id
144
145 create proc BindUpdate
146 @id int
147 as
148 select * from dbo.demotable where id=@id
149
150
151 create procedure ClearAll
152 as
153 truncate table dbo.demotable