BULK INSERT问题 [英] BULK INSERT Question
问题描述
我有15列的表格
CREATE TABLE [dbo]。[myTable]（
[m] [bigint] PRIMARY KEY，
[c1] [bigint] NULL，
[c2] [bigint] NULL，
[c3] [bit] NULL，
[c4] [tinyint] NULL，
[c5] [nvarchar]（50）NULL，
[c6] [bit] NULL
................
...................
现在我想从文件中运行BULK INSERT，只包含第一个
列的值，例如
22222222222
33333333333
44444444444
............
..... .......
我需要其他列设置为它的默认值即NULL
任何想法???? ？
谢谢
-
留言通过 http://www.sqlmonster.com
" akej通过SQLMon ster.com" < FO *** @ nospam.SQLMonster.com>在消息中写道
news：97 ****************************** @ SQLMonster.c om。 ..我有15列的表格
创建表[dbo]。[myTable]（
[m] [bigint] PRIMARY KEY，< br> [c1] [bigint] NULL，
[c2] [bigint] NULL，
[c3] [bit] NULL，
[c4] [tinyint] NULL，
[c5] [nvarchar]（50）NULL，
[c6] [bit] NULL
................
... ................
现在我想从文件中运行BULK INSERT，它只包含第一列的值，例如
22222222222
33333333333
44444444444
...........
...........
我需要其他列设置为它的默认值即NULL
任何想法?????
谢谢
-
通过 http://www.sqlmonster.com 发布的消息
您需要使用格式文件 - 请参阅使用格式文件"和使用数据
文件与较少的字段在线书籍。 DTS是另一种选择，如果这是一次性任务，可能会更快
。
Simon
akej通过SQLMonster.com（fo***@nospam.SQLMonster.com）写道：我有15列的表
CREATE TABLE [dbo ]。[myTable]（
[m] [bigint] PRIMARY KEY，
[c1] [bigint] NULL，
[c2] [bigint] NULL，
[c3] [bit] NULL，
[c4] [tinyint] NULL，
[c5] [nvarchar]（50）NULL，
[c6] [bit] NULL
... .................................................................................................................................................................................................................从文件中插入仅包含第一列的值，例如
22222222222
33333333333
44444444444
...........
...........
我需要其他列设置为它的默认值，即NULL
这是格式文件（没有标识保存）：
8.0
1
1 SQLCHAR 0 0" \\\\ n" 1 X""
这是SQL命令：
BULK INSERT myTable FROM''E：\ temp \ slask.bcp''
WITH（FORMATFILE =''E：\ temp\slask.fmt''）
go
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
< blockquote>好的谢谢，非常有帮助
-
留言通过 http://www.sqlmonster.com
Hi, i have table with 15 columns
CREATE TABLE [dbo].[myTable] (
[m] [bigint] PRIMARY KEY ,
[c1] [bigint] NULL ,
[c2] [bigint] NULL ,
[c3] [bit] NULL ,
[c4] [tinyint] NULL ,
[c5] [nvarchar] (50) NULL ,
[c6] [bit] NULL
................
...................
now i want to run BULK INSERT from file that consist only values for first
column e.g.
22222222222
33333333333
44444444444
............
............
and i need that other columns will sets to it''s default values i.e. NULL
Any ideas ?????
Thanks
--
Message posted via http://www.sqlmonster.com
"akej via SQLMonster.com" <fo***@nospam.SQLMonster.com> wrote in message
news:97******************************@SQLMonster.c om...Hi, i have table with 15 columns
CREATE TABLE [dbo].[myTable] (
[m] [bigint] PRIMARY KEY ,
[c1] [bigint] NULL ,
[c2] [bigint] NULL ,
[c3] [bit] NULL ,
[c4] [tinyint] NULL ,
[c5] [nvarchar] (50) NULL ,
[c6] [bit] NULL
................
...................
now i want to run BULK INSERT from file that consist only values for first
column e.g.
22222222222
33333333333
44444444444
...........
...........
and i need that other columns will sets to it''s default values i.e. NULL
Any ideas ?????
Thanks
--
Message posted via http://www.sqlmonster.com
You need to use a format file - see "Using Format Files" and "Using a Data
File with Fewer Fields" in Books Online. DTS is another option, and probably
quicker if this is a one-off task.
Simon
akej via SQLMonster.com (fo***@nospam.SQLMonster.com) writes:Hi, i have table with 15 columns
CREATE TABLE [dbo].[myTable] (
[m] [bigint] PRIMARY KEY ,
[c1] [bigint] NULL ,
[c2] [bigint] NULL ,
[c3] [bit] NULL ,
[c4] [tinyint] NULL ,
[c5] [nvarchar] (50) NULL ,
[c6] [bit] NULL
................
...................
now i want to run BULK INSERT from file that consist only values for first
column e.g.
22222222222
33333333333
44444444444
...........
...........
and i need that other columns will sets to it''s default values i.e. NULL
This is the format file (save without identation):
8.0
1
1 SQLCHAR 0 0 "\r\n" 1 X ""
And this is the SQL command:
BULK INSERT myTable FROM ''E:\temp\slask.bcp''
WITH (FORMATFILE = ''E:\temp\slask.fmt'')
go
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
ok thanks , very helpful
--
Message posted via http://www.sqlmonster.com
这篇关于BULK INSERT问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
