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 into a linked server fails

Hello,

I am trying to perform bulk insert operation on a linked server.

Here is the T-sql code for the same:

Declare @.dynamic_sql nvarchar(1000)
Declare @.file_name varchar(100)

set @.file_name = 'C:\mvam\calls\11182003.txt'

set @.dynamic_sql = 'bulk insert
[MUMBAI\AMIT_DATABASE]...cdr_repositroy from ' + '''' + @.file_name + '''' + ' with (FIELDTERMINATOR = ' + '''' + ',' + '''' + ', ROWTERMINATOR = ' + '''' + '\n' + '''' + ', FIRSTROW =3, DATAFILETYPE = ' + '''' + 'char' + '''' + ')'

execute sp_executesql @.dynamic_sql

On executing the above posted code I get the following error

Invalid object name 'MUMBAI\AMIT_DATABASE...cdr_repositroy'

Any suggestions would be helpful

ThanxAmit,
Unless you specifically configured your linked server to point to the appropriate database it defaults to the master DB.

To remove ambiguity you need to specify the full 4 part name
e.g

insert [DBDEVSERVER\DEVELOP].pubs.dbo.test
select 'a'

For your bulk insert specify the database name

set @.dynamic_sql = 'bulk insert
[MUMBAI\AMIT_DATABASE].databasename.ownername.cdr_repositroy from ' + '''' + @.file_name + '''' + ' with (FIELDTERMINATOR = ' + '''' + ',' + '''' + ', ROWTERMINATOR = ' + '''' + '\n' + '''' + ', FIRSTROW =3, DATAFILETYPE = ' + '''' + 'char' + '''' + ')'

let us know if you are still having problem|||I tried with the option you mentioned in your reply i.e. using the complete naming convention but of no use. I still get the same error.

Also, I checked if the Database is correct on linked server and its correct.

Anyways, thanx for the help.

Amit|||Does the link work with a select statement to the linked table

ie
Select * from servername.databasename.ownername.objectname.

Bulk Insert - Cannot perform SET operation.

I am trying to use Bulk Insert for a user that is not sysadmin.
I have already set up the user as a member of "bulkadmin".

When I run the following script:
DECLARE @.SQLVARCHAR(1000)
CREATE TABLE amdbo.#temp (
[id] [varchar] (10) NULL
,[fld2] [varchar] (10) NULL
,[fld3] [varchar] (10) NULL
)
set @.SQL =
'BULK INSERT amdbo.#temp
FROM ''F:\test.txt''
WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
= ''\n'')'
EXEC (@.SQL)
select * from #temp

I still get the message ...
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table '#temp'.
Cannot perform SET operation.

Anyone have an idea what I am doing wrong?
Drew.Drew (cladre@.hotmail.com) writes:
> I am trying to use Bulk Insert for a user that is not sysadmin.
> I have already set up the user as a member of "bulkadmin".
> When I run the following script:
> DECLARE @.SQL VARCHAR(1000)
> CREATE TABLE amdbo.#temp (
> [id] [varchar] (10) NULL
> ,[fld2] [varchar] (10) NULL
> ,[fld3] [varchar] (10) NULL
> )
> set @.SQL =
> 'BULK INSERT amdbo.#temp
> FROM ''F:\test.txt''
> WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
>= ''\n'')'
> EXEC (@.SQL)
> select * from #temp
> I still get the message ...
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table '#temp'.
> Cannot perform SET operation.

As I recall you cannot bulk load to temp tables if you are not sysadmin.
Or, this is somewhat weird: the user is the dbo of tempdb.

It's probably better to create a permanent table that this user is
the owner of and use that table as the target.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The solution I've done is to create a physical table, populate it, and
when the procedure ends, drop the table. Which is pretty much what
you suggested.
Thanks,
Drew

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402F24BBF061Yazorman@.127.0.0.1>...
> Drew (cladre@.hotmail.com) writes:
> > I am trying to use Bulk Insert for a user that is not sysadmin.
> > I have already set up the user as a member of "bulkadmin".
> > When I run the following script:
> > DECLARE @.SQL VARCHAR(1000)
> > CREATE TABLE amdbo.#temp (
> > [id] [varchar] (10) NULL
> > ,[fld2] [varchar] (10) NULL
> > ,[fld3] [varchar] (10) NULL
> > )
> > set @.SQL =
> > 'BULK INSERT amdbo.#temp
> > FROM ''F:\test.txt''
> > WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
> >= ''\n'')'
> > EXEC (@.SQL)
> > select * from #temp
> > I still get the message ...
> > Server: Msg 8104, Level 16, State 2, Line 1
> > The current user is not the database or object owner of table '#temp'.
> > Cannot perform SET operation.
> As I recall you cannot bulk load to temp tables if you are not sysadmin.
> Or, this is somewhat weird: the user is the dbo of tempdb.
> It's probably better to create a permanent table that this user is
> the owner of and use that table as the target.|||I have the similiar issues encountered. Please refer to MS
knowledgebase 302621. Microsoft has confirmed this is an software
issue.

cladre@.hotmail.com (Drew) wrote in message news:<d00212d6.0310200700.3b2bca9f@.posting.google.com>...
> The solution I've done is to create a physical table, populate it, and
> when the procedure ends, drop the table. Which is pretty much what
> you suggested.
> Thanks,
> Drew
>
> Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402F24BBF061Yazorman@.127.0.0.1>...
> > Drew (cladre@.hotmail.com) writes:
> > > I am trying to use Bulk Insert for a user that is not sysadmin.
> > > I have already set up the user as a member of "bulkadmin".
> > > > When I run the following script:
> > > DECLARE @.SQL VARCHAR(1000)
> > > CREATE TABLE amdbo.#temp (
> > > [id] [varchar] (10) NULL
> > > ,[fld2] [varchar] (10) NULL
> > > ,[fld3] [varchar] (10) NULL
> > > )
> > > set @.SQL =
> > > 'BULK INSERT amdbo.#temp
> > > FROM ''F:\test.txt''
> > > WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
> > >= ''\n'')'
> > > EXEC (@.SQL)
> > > select * from #temp
> > > > I still get the message ...
> > > Server: Msg 8104, Level 16, State 2, Line 1
> > > The current user is not the database or object owner of table '#temp'.
> > > Cannot perform SET operation.
> > As I recall you cannot bulk load to temp tables if you are not sysadmin.
> > Or, this is somewhat weird: the user is the dbo of tempdb.
> > It's probably better to create a permanent table that this user is
> > the owner of and use that table as the target.