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" <forum@.nospam.SQLMonster.com> wrote in message
news:97f86ad1e7ec4a7497b655baa10a64fb@.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 (forum@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok thanks , very helpful
--
Message posted via http://www.sqlmonster.com|||i use this format file:
8.0
1
1 SQLCHAR 0 8 "\n" 1 mob Hungarian_CS_AI
however i got an error message:
Server: Msg 4822, Level 16, State 1, Line 1
Could not bulk insert. Invalid number of columns in format file 'c:\1.fmt'.
--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> i use this format file:
>
> 8.0
> 1
> 1 SQLCHAR 0 8 "\n" 1 mob Hungarian_CS_AI
>
> however i got an error message:
> Server: Msg 4822, Level 16, State 1, Line 1
> Could not bulk insert. Invalid number of columns in format file
>'c:\1.fmt'.
As I said, leave out the indentation. They were only in my post to
separate the data from my text. You cannot have leading spaces on
the lines in your format file.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||sorry, but i don't really understand what do u mean.
Please explain, Thanks
--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> sorry, but i don't really understand what do u mean.
> Please explain, Thanks
In the sample file you posted, you appear to have leading spaces on line
two and three. You need to remove these.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok, i understand. there are no spaces at all, each number ended with
<ENTER>.
However how can solve this problem, i got an error
--
Message posted via http://www.sqlmonster.com|||Thanks i remove them and it works. Thanks KING
--
Message posted via http://www.sqlmonster.com|||i have another question for u.
Why when i disable replication from Enterprise Manager, tables and jobs not
removed (or not disabled).
What i need do to perform remove replication (remove publishing and
distributor) from some DB, THANKS.
sql server 2000
--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> i have another question for u.
> Why when i disable replication from Enterprise Manager, tables and jobs
> not removed (or not disabled).
> What i need do to perform remove replication (remove publishing and
> distributor) from some DB, THANKS.
Since this a completely different question not related to BULK INSERT,
it would be a good idea to start a new thread. That makes it more likely
that people with experience of replication (I am not one of them) might see
the thread and can help you.
You could also try the newsgroup microsoft.public.sqlserver.replication.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>BULK INSERT myTable FROM 'E:\temp\slask.bcp'
Is it possible to do mapping
e.g
in my slask.bcp file 20 columns, i need to insert to myTable that have
suppose 15 columns also the names of colums in the file and table are
DIFFERENT, so how to acomplish mapping ?
Thanks
--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
>>BULK INSERT myTable FROM 'E:\temp\slask.bcp'
> Is it possible to do mapping
> e.g
> in my slask.bcp file 20 columns, i need to insert to myTable that have
> suppose 15 columns also the names of colums in the file and table are
> DIFFERENT, so how to acomplish mapping ?
Yes, this is possible. The format file describes in input file, and
is indeed a mapping to the table. Each line describes a field in the
file, and possibly also maps it to a table column. Here is a format file
with for a file with three fields:
8.0
3
1 SQLCHAR 1 0 "" 7 col1 Hungarian_CS_AI
2 SQLCHAR 0 10 "" 2 col2 Hungarian CS_AI
3 SQLCHAR 0 0 "\r\n" 5 col5 ""
The first number is the field number, and they should come in consecutive
order. Note that the 3 on the second line, describes the number of fields
in the field.
The next is the data type *in the file*. As long as you work with text
files this is always SQLCHAR (or SQLNCHAR for Unicode files), no matter
the data type of the target column in the database. But it is possible
to bulk load binary files, for which you would use SQLINT and such.
The next three fields describes the field is in the file. And while
they can be combined, you normally use them one by one. (Honestly, I
don't exactly what happens if you combine them.)
The first of these columns is a "prefix length" and is always 1, 2 or 4.
This prefix gives the actual length the of the field, and the prefix
is itself a binary value of 1, 2 or 4 bytes. From this follows that
prefix lengths are rarely used with data files in text format.
The sceond of these columns is a fixed length in bytes.
The last column is a character sequence that terminates the field. Note
that in this example the delimiter is newline, \r\n, and many files do
indeed have one data record per line in the file. However, BCP makes
no such assumptions, and will just iterate over the field definitions,
and if a newline appears in what BCP thinks is in the middle of a text
field, then that newline is handled as data. This permits you to import
data with newlines, but it also means that when BCP goes out of sync,
it gets out of sync badly.
Next number is the mapping you are looking for. This is the column
number in the database. In the example the file loads columns 2, 5
and 7 in the database. You can also specify 0 to indicate that that
field in the table should not be loaded at all. This can be used to
handle formats like:
"quoted data","more quoted data",989
Next column in the format file is the column name, but the value of
this field is ignored, so you can put "" if you like.
The last column is the collation of the field in the text file, in
case you would need some conversion. You can set "" for non-character
columns, or set "" all the way to get some default.
All of what I have said here is Books Online, under
Administering SQL Server
Importing and Exporting Data
Using bcp and BULK INSERT
Using Format files
But my presentation above maybe somewhat clearer than the sections in
Books Online.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank u very much. I can't understand why some documentation are written in
language that only the author of it can understand.
Last question on the subject however i think it's impossible, in case the
datatype are different in some columns (in the table and in the file)
is it possible to convert in some way? (using the BULK INSERT)
THANKS.
P.S. Do u have some new tutorials or books?? Please let me know in case
the books i want buy in case tutorials (like ERROR HAndling) please give me
the links, thanks.
--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> Last question on the subject however i think it's impossible, in case the
> datatype are different in some columns (in the table and in the file)
> is it possible to convert in some way? (using the BULK INSERT)
Not really sure what you mean here. Assuming that the file you import is
a text file, the data type will be different as soon as the target column
is not a character data type.
I have not dug deeply into this, but I would assume that the same
conversions to be available, that are available in SQL statements.
Maybe if you have a specific example, I can give some suggestions.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Suppose if i acomplish UPDATING to some table and i need to convert data
(in my case data that i got from parameters) i can use the CASE statement
e.g.
UPDATE myTable
SETcol1 = CASE WHEN @.param = 'TRUE' THEN 1
ELSE 0 END,
...........................
...........................
...........................
the col1 of myTable has datatype bit (because the datatype of the column
different form this that i got in param i need to convert)
Also while i perfom UPDATING with using the CASE statement i can convert
from any datatype that i want.
Ny question about above but in BULK INSERT ??
--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> Suppose if i acomplish UPDATING to some table and i need to convert data
> (in my case data that i got from parameters) i can use the CASE statement
> e.g.
> UPDATE myTable
> SET col1 = CASE WHEN @.param = 'TRUE' THEN 1
> ELSE 0 END,
> ...........................
> ...........................
> ...........................
> the col1 of myTable has datatype bit (because the datatype of the column
> different form this that i got in param i need to convert)
> Also while i perfom UPDATING with using the CASE statement i can convert
> from any datatype that i want.
> Ny question about above but in BULK INSERT ??
I'm sorry, but you have me lost completely. First you talk about
UPDATE, and then you go on with BULK INSERT. You cannot update tables
with BULK INSERT, so I fail to see the connection.
If you have problem with BULK INSERT, please post:
o CREATE TABLE statement for your table.
o Sample data file.
o Any format file you are using.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In my aoverhead post i wanted to show u as an example that i can covert
data e.g. from char to int, in my case when i accomplish BULK INSERT i want
to convert from suppese char to int. If in my data file one column is char
i need in some way to cenvert it to int like i did in my overhead post with
CASE statement.
Thanks, and sorry that confused u
--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> In my aoverhead post i wanted to show u as an example that i can covert
> data e.g. from char to int, in my case when i accomplish BULK INSERT i
> want to convert from suppese char to int. If in my data file one column
> is char i need in some way to cenvert it to int like i did in my
> overhead post with CASE statement.
Normally, when I hear a conversion I think in terms of implicit conversion
as when the string literal '20000520 12:21:31' can be interpreted as a
datetime value, or when you use explicit conversion with cast() or
convert().
The only form of conversion you can do with bulk load is implicit
conversion, since you cannot apply functions to the data you load. From
this follows that you neither can do user-implemented "conversion"
as in your example with bulk-load directly.
If you need to do transformation like storing TRUE/FALSE in an input
file as bit values, there are two choices: 1) use an intermediate
table into which you load the data, and the use INSERT-SELECT to move
the data to the target table. 2) Use DTS to write a transformation task.
As for 1), this is often needed anyway, because the file data may be
imperfect and need scrubbing of duplicates etc. As for 2) I assume that
this is what you can use DTS for, but never having used DTS myself, I
can give any details. It's possible that running the Import/Export
wizard can give you headstart in this area.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks the 1) is perfect.
Thanks.
--
Message posted via http://www.sqlmonster.comsql
