BULK INSERT Question

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" <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

Bulk insert is all or nothing?

Hi, I ran query 'Bulk insert mytable from myFile with (FIRE_TRIGGERS)'.

The problems are,
1.It does nothing if exist duplication key records. There is no option to skip the duplications. What else is possible to skip duplicated insertion and go next record when bulk insert?

2. The triggers are not fired even if bulk insert success.

My goal is that very large data should be loaded at once and use trigger.

The trigger is fired after bulk insert executed.
But, still remained some problems.
The result of executing trigger is affected only the last record of source data file. It seems that the trigger definition has logical or symantical problems. Any one help is very appreciated.

Here is my definition of trigger.
Can I control all of the inserted recorcd in trigger definition?

create table myTable
(
id int,
value1 int,
value2 int,
primary key(id)
);

create table myTempTable
(
id int,
value1 int,
value2 int
);

CREATE TRIGGER mytrigger ON myTempTable
INSTEAD OF INSERT
AS
BEGIN
PRINT 'trigger mytrigger'
DECLARE @.id AS int, @.value1 AS int, @.value2 AS int
SELECT @.id = INSERTED.id,
@.value1 = INSERTED.value1,
@.value2 = INSERTED.value2
FROM INSERTED

PRINT @.id
PRINT @.value1
PRINT @.value2

IF EXISTS(SELECT * FROM myTable WHERE id = @.id)
BEGIN
PRINT 'trigger EXISTS'
PRINT 'UPDATE'
UPDATE myTable SET value = value + @.value,outbKbps =outbKbps + @.outbKbps WHERE id=@.id
END
ELSE
BEGIN
PRINT 'trigger NOT EXISTS'
PRINT 'trigger INSERT'
INSERT INTO myTableVALUES(@.id,@.value1,@.value2)
END
END
GO

bulk insert myTempTable from 'c:\bulk_myTable.dat' with (FIRE_TRIGGERS);

-- bulk_myTable.dat file's contents --
1 100 200
1 100 200
1 100 200
1 100 200
1 100 200

From Books Online, topic: "Using bcp and BULK INSERT"

Triggers are fired once for each batch in the bulk copy operation. The inserted table passed to the trigger contains all of the rows inserted by the batch. Specify FIRE_TRIGGERS only when bulk copying into a table with INSERT and INSTEAD OF triggers that support multiple row inserts.

Your trigger fires only one time for the entire batch -not for each row in the batch.

Your trigger 'assumes' there is, and is designed to handle, only one row in the inserted table, and therefore will NOT do as you desire. It couldn't possibly work for a bulk insert of more than one row.

You should 're-design' the trigger to handle multiple rows at a time. If you provide, in a new posting, the table DDL, some sample data in the form of insert statements, and a statement of what you are attempting to accomplish, perhaps some here can help you create a trigger that will work for you.

Hopefully, this answered your question.

|||

I solved the number of 2 from my question in this thread.

I use the 'AFTER' instead of 'INSTEAD OF' trigger for getting the bulk insert result record.
And, in trigger definition, using the cursor, get the recordset that I want to get.
In the end, insert or update to the target table record by record.
I got this as hint from your commant. Thank you very much.

Here is the sample.

CREATE TRIGGER TRG_MY_TABLE ON MyTempTable
AFTER INSERT
AS
BEGIN

DECLARE @.id AS INT,
@.value1 AS INT,
@.value2 AS INT

DECLARE CursorMyTable CURSOR FOR
SELECT id AS cur_id, value1 AS cur_value1, value2 AS cur_value2
FROM MyTempTable

OPEN CursorMyTable

FETCH NEXT FROM CursorMyTable
INTO @.id,@.value1,@.value2

-
IF EXISTS(SELECT * FROM MyTable WHERE id=@.id)
BEGIN
UPDATE MyTable
SET value1= value1+ @.value1,
value2= value2+ @.value2
WHERE id=@.id

END
ELSE
BEGIN
INSERT INTO MyTable VALUES(@.id,@.value1,@.value2)
END
-

WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CursorMyTable
INTO @.id,@.value1,@.value2

IF EXISTS(SELECT * FROM MyTable WHERE id=@.id)
BEGIN
UPDATE MyTable
SET value1= value1+ @.value1,
value2= value2+ @.value2
WHERE id=@.id

END
ELSE
BEGIN
INSERT INTO MyTable VALUES(@.id,@.value1,@.value2)
END
-

END

CLOSE CursorMyTable

DEALLOCATE CursorMyTable
END

|||

I would highly recommend NOT using a CURSOR in a TRIGGER.

It would be far better to load the data into a 'Staging' table, and then process the data as needed. Stepping through a CURSOR in the context of a TRIGGER (meaning during a TRANSACTION) will hold a lot of locks and resources -most likely, unnecessarily.

Just not a good idea.

|||Thank you, but could you more explain about the 'Staging' table?|||

A 'Staging' table most likely would have the same columns as the final production table -but datatypes may be different. Data is loaded into the Staging table, and then cleansed, conformed, and moved to the production table.

This is a very common ETL operation.

Bulk Insert from Excel file

Hi ...
Having trouble getting Bulk Insert to work with .xls file.
Following code works fine for text file:
BULK INSERT MyTable FROM 'C:\bulkinsert.txt'
with (fieldterminator ='\t', datafiletype ='char')
Anybody know what changes I need to make to above code so it works for Excel
file?
Thanks in advance for your help ...
bill morganBill,
BULK INSERT cannot be used to import data from Excel.
You can use OpenRowset, or you can set up a linked server
with an Excel provider.
Here is one way, assuming ReportData is a named range of
the Excel sheet.
insert into MyTable
select <columns> from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Report.xls',
'select * from ReportData'
) -- will be called F1, F2, F3, ... if there are no headers.
If it's just a sheet, try
insert into MyTable
select <columns> from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Report.xls',
'select * from [Sheet1$]'
)
The $ is added to the sheet name, which itself has no $ character.
There are a few other things to watch out for, like type conversion,
and you can find other examples in these threads if the ones above
don't help.
http://groups.google.com/groups?hl=...cel%2Bsqlserver
Also, MSDN has a little more information than Books Online about this.
-- Steve Kass
-- Drew University
bill_morgan wrote:

>Hi ...
>Having trouble getting Bulk Insert to work with .xls file.
>Following code works fine for text file:
>BULK INSERT MyTable FROM 'C:\bulkinsert.txt'
>with (fieldterminator ='\t', datafiletype ='char')
>Anybody know what changes I need to make to above code so it works for Exce
l
>file?
>Thanks in advance for your help ...
>bill morgan
>
>
>|||Steve,
Great stuff. Thank you. Since I have over 200 Excel files to import (but
only one sheet per file) I'm setting up your suggestion_2 below. If I can
get it to work for one, then I'm sure I can loop through others, as files us
e
standardized names.
Thanks again ...
"Steve Kass" wrote:

> Bill,
> BULK INSERT cannot be used to import data from Excel.
> You can use OpenRowset, or you can set up a linked server
> with an Excel provider.
> Here is one way, assuming ReportData is a named range of
> the Excel sheet.
> insert into MyTable
> select <columns> from OpenRowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\Report.xls',
> 'select * from ReportData'
> ) -- will be called F1, F2, F3, ... if there are no headers.
> If it's just a sheet, try
> insert into MyTable
> select <columns> from OpenRowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\Report.xls',
> 'select * from [Sheet1$]'
> )
> The $ is added to the sheet name, which itself has no $ character.
> There are a few other things to watch out for, like type conversion,
> and you can find other examples in these threads if the ones above
> don't help.
> http://groups.google.com/groups?hl=...cel%2Bsqlserver
> Also, MSDN has a little more information than Books Online about this.
> -- Steve Kass
> -- Drew University
>
>
> bill_morgan wrote:
>
>|||PS - suggestion_2 worked perfectly...! Now for the loop ...
"Steve Kass" wrote:

> Bill,
> BULK INSERT cannot be used to import data from Excel.
> You can use OpenRowset, or you can set up a linked server
> with an Excel provider.
> Here is one way, assuming ReportData is a named range of
> the Excel sheet.
> insert into MyTable
> select <columns> from OpenRowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\Report.xls',
> 'select * from ReportData'
> ) -- will be called F1, F2, F3, ... if there are no headers.
> If it's just a sheet, try
> insert into MyTable
> select <columns> from OpenRowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\Report.xls',
> 'select * from [Sheet1$]'
> )
> The $ is added to the sheet name, which itself has no $ character.
> There are a few other things to watch out for, like type conversion,
> and you can find other examples in these threads if the ones above
> don't help.
> http://groups.google.com/groups?hl=...cel%2Bsqlserver
> Also, MSDN has a little more information than Books Online about this.
> -- Steve Kass
> -- Drew University
>
>
> bill_morgan wrote:
>
>

bulk insert and trigger

Some application insert record to MyTable using the bulk insert statement
without the clause "With fire_triggers".In MyTable i need that the triggers
fire.
I can't modify the bulk insert statement. How i can fire the triggers on my
table?
thx
In this case, you need to run sp_addtabletocontents to include the rows then
resynchronise. Alternatively you can use sp_mergedummyupdate for a single
row.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)