bulk insert with a primary key?

i'm using sql2k.
can i do a bulk isnert operation to a table with a primary key
(identity field) on it? i suspect the dts pacakge didn't utilize the
bulk insert because pk is automatically a non-clustering index, and
bulk insert can only work on table w/o any index. in this case, what
should i do to make sure the fastest load possible?
thank you.> bulk insert can only work on table w/o any index.
Since when? I have several applications where BULK INSERT affects a table
with a clustered index on a datetime column and a non-clustered index on a
foreign key column. The only way it differs from your scenario is that all
the data is in the file (there is no surrogate column generated by the
system).

> in this case, what
> should i do to make sure the fastest load possible?
As long as the generation of the IDENTITY values does not need to correspond
directly 1:1 with the physical order of the file, you may wish to bulk
insert into a heap, and then insert real_table(column_list) select * from
heap.
A|||It is not true that bulk insert will only work with non-indexed tables.
In fact, you can achieve better throughput, if you have a clustered index on
the table, and input file is also sorted in the same order as the clustered
index.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1123518093.288607.254270@.g14g2000cwa.googlegroups.com...
> i'm using sql2k.
> can i do a bulk isnert operation to a table with a primary key
> (identity field) on it? i suspect the dts pacakge didn't utilize the
> bulk insert because pk is automatically a non-clustering index, and
> bulk insert can only work on table w/o any index. in this case, what
> should i do to make sure the fastest load possible?
> thank you.
>

bulk insert which check if record exists

Hi All,
I want to bulk insert from table A to table B (both table have the same
design fields which contain primary keys). Is there a way to check if record
exist before inserting rows to avoid primary key violation? What would be my
query?
thanks in advance,
joelTry something like this:
insert tableA (<cols> )
select b.<cols>
from tableA b
left outer join tableA a
on b.PrimaryKey = a.PrimaryKey
where a.PrimaryKey IS NULL|||If you assume that ID is your PK then you can do something like this
INSERT INTO TABLEB
SELECT A.*
FROM TABLEA A
LEFT JOIN TABLEB B
ON A.ID = B.ID
WHERE B.ID IS NULL
http://sqlservercode.blogspot.com/|||Sorry, tableA and tableB are backwards in my example. This will insert
into tableA, rows that are in tableB and not in tableA.
insert tableB (<cols> )
select a.<cols>
from tableA a
left outer join tableB b
on b.PrimaryKey = a.PrimaryKey
where b.PrimaryKey IS NULL|||Sorry again, untested code bites again.
This will insert
into tableB, rows that are in tableA and not in tableB.
insert tableB (<cols> )
select a.<cols>
from tableA a
left outer join tableB b
on b.PrimaryKey = a.PrimaryKey
where b.PrimaryKey IS NULLsql

Bulk Insert Runs out of space

Hello,
I am running a Bulk Insert and getting the error that the PRIMARY file group
is full. All the databases in the server have the Autogrow check box
selected and there is over a 100 GB space available on the hard drive. The
transaction log is set to autogrow as well.
Is there a maximum file size for the primary datafile? The size is
currently at 1.8 GB
Can someone help with some ideas for getting around this error.
Thanks
Hardee Mahoney
Washington, DC USA
Thank you for your reply. That explains it.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:51bm4eF1jbqr6U1@.mid.individual.net...
> hi,
> Hardee Mahoney wrote:
> MSDE has a limit of 2gb for each database (data file(s) only)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>
|||Remember that first, bulk copy does not write to the transaction log. Next,
SQL Express has twice (4GB) data capacity. However, if you're running out of
space with MSDE, I would reconsider what you're storing in the database. Are
you using it to store BLOBs? If so, consider storing those on a file share
and putting the path in the database.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
------
Microsoft MVP, Author, Mentor
Microsoft MVP
"Hardee Mahoney" <hardee_mahoney@.abtassoc.com> wrote in message
news:OLMB568OHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Thank you for your reply. That explains it.
>
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:51bm4eF1jbqr6U1@.mid.individual.net...
>

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

Hi,
I want to use bulk insert using a text file to import data from it to the DB, but how can I ignore the primary keys in the text file which is in the db table.
regards,I think there is no direct way to deal with to ignore PK constraint while Bulk inserting. You need to drop them before insert and re-enable once it finishes.|||Originally posted by Satya
I think there is no direct way to deal with to ignore PK constraint while Bulk inserting. You need to drop them before insert and re-enable once it finishes.

Which will fail if this is an issue for you now...

Why not load the data to a staging table, and audit the data before dropping it in to the destination table?|||I know other solutions but I want this one because the system is a real time system and I want the fastest way which is bulk insert.

regards,

Originally posted by Brett Kaiser
Which will fail if this is an issue for you now...

Why not load the data to a staging table, and audit the data before dropping it in to the destination table?|||If you want to "skip" a field, it's in BOL under "Using a Data File with Fewer Fields"...Wait a minute, I found this in my clipboard...Am I loosing my mind, or it's the same post? Oh, I get it, Groundhog day all over...hehehehe, I know all the answers!!!!!!!!

building models with views

Using data in SS2000 and RS2005. I tried to build a model from a view that I
created. I got the "tables does not have a primary key" error. Do I have to
use indexed views in the model?
--
Dan D.Dan,
You will need to specify a 'logical' primary key in your DSV (Data Source
View)
Bret
"Dan D." wrote:
> Using data in SS2000 and RS2005. I tried to build a model from a view that I
> created. I got the "tables does not have a primary key" error. Do I have to
> use indexed views in the model?
> --
> Dan D.

building a report - problem with nulls in math

so i have two tables that looks something like so:
CREATE TABLE transactions (
transactionumber INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
transactionamount MONEY,
transactiondate DATETIME
)
go
CREATE TABLE credits (
creditnumber INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
transactionnumber INT, -- this has an FK constraint to the PK of
transactions
creditamount MONEY,
creditdate DATETIME
)
now if i want to run a report that summarizes the amount of
transactions in a given time frame, i might say something like this:
SELECT SUM(t.transactionamount)
FROM transactions AS t
WHERE t.transactiondate > @.startdate AND t.transactiondate < @.enddate
however, that won't take into account the possible credits that were
applied to the transactions, which should be deducted. so i might do
something like this:
SELECT SUM(t.transactionamount) - SUM(c.creditamount)
FROM transactions AS t
LEFT JOIN credits AS c ON t.transactionnumber = c.transactionnumber
WHERE t.transactiondate > @.startdate AND t.transactiondate < @.enddate
which would work fine, except that credits are the exception, so most
of the time, the creditamount produced by the join is NULL, so the
attempt to SUM and subtract it produces an error.
how might i work around this? with a CASE statement? or do i have to do
the report math not in the query (where it would be super fast) but in
the data-consuming application (where it would be super slow)?
thanks for any help,
jasonSELECT SUM(Isnull(t.transactionamount),0) - SUM(Isnull(c.creditamount)
,0)
FROM transactions AS t
LEFT JOIN credits AS c ON t.transactionnumber = c.transactionnumber
WHERE t.transactiondate > @.startdate AND t.transactiondate < @.enddate
Madhivanan|||Thanks, that did the trick