BCP

1. When using BCP to copy data into a table, which of the following
statements apply? Choose 2.
A) Database users will not be able to access the table because BCP
will lock it.
B) Database users will see the rows inserted by BCP after each batch
is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.> 1. When using BCP to copy data into a table, which of the following
> statements apply? Choose 2.
> A) Database users will not be able to access the table because BCP
> will lock it.
>
The default behavior is row locking so other users can use the table.
However, a TABLOCK hint can also be specified.
> B) Database users will see the rows inserted by BCP after each batch
> is complete.
>
A BCP batch is a transaction so users can see data once committed.
> C) You must have INSERT permissions on the table.
>
Yes.
> D) Existing rows are replaced by BCP.
>
Nope.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"docsql" <docsql@.noemail.nospam> wrote in message
news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...|||Not sure if this question related to SQL2000 or SQL2005? Some more
information on permissions for BCP in SQL2005. You will note there is now a
requirement to get ALTER table permission if you are doing DDL operations
transparently. This was not the case in SQL2000. However, you will also
require SELECT permission on the traget table both in SQL2000 and SQL2005
A bcp out operation requires SELECT permission on the source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any of the
following is true:
(1)Constraints are disabled, which is the default behavior. To keep
constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint.
(2) Triggers are disabled, which is the default behavior. To fire triggers,
use the -h option with the FIRE_TRIGGERS hint.
(3) You use the -E option to import identity values from a data file.
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:elgg6Cp2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>> 1. When using BCP to copy data into a table, which of the following
>> statements apply? Choose 2.
>> A) Database users will not be able to access the table because BCP
>> will lock it.
> The default behavior is row locking so other users can use the table.
> However, a TABLOCK hint can also be specified.
>> B) Database users will see the rows inserted by BCP after each
>> batch is complete.
> A BCP batch is a transaction so users can see data once committed.
>
>> C) You must have INSERT permissions on the table.
> Yes.
>> D) Existing rows are replaced by BCP.
>>
> Nope.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

BCP

1. When using BCP to copy data into a table, which of the following
statements apply? Choose 2.
A) Database users will not be able to access the table because BCP
will lock it.
B) Database users will see the rows inserted by BCP after each batch
is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.
> 1. When using BCP to copy data into a table, which of the following
> statements apply? Choose 2.
> A) Database users will not be able to access the table because BCP
> will lock it.
>
The default behavior is row locking so other users can use the table.
However, a TABLOCK hint can also be specified.

> B) Database users will see the rows inserted by BCP after each batch
> is complete.
>
A BCP batch is a transaction so users can see data once committed.

> C) You must have INSERT permissions on the table.
>
Yes.

> D) Existing rows are replaced by BCP.
>
Nope.
Hope this helps.
Dan Guzman
SQL Server MVP
"docsql" <docsql@.noemail.nospam> wrote in message
news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
|||Not sure if this question related to SQL2000 or SQL2005? Some more
information on permissions for BCP in SQL2005. You will note there is now a
requirement to get ALTER table permission if you are doing DDL operations
transparently. This was not the case in SQL2000. However, you will also
require SELECT permission on the traget table both in SQL2000 and SQL2005
A bcp out operation requires SELECT permission on the source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any of the
following is true:
(1)Constraints are disabled, which is the default behavior. To keep
constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint.
(2) Triggers are disabled, which is the default behavior. To fire triggers,
use the -h option with the FIRE_TRIGGERS hint.
(3) You use the -E option to import identity values from a data file.
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:elgg6Cp2FHA.3592@.TK2MSFTNGP12.phx.gbl...
> The default behavior is row locking so other users can use the table.
> However, a TABLOCK hint can also be specified.
>
> A BCP batch is a transaction so users can see data once committed.
>
> Yes.
>
> Nope.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

BCP

1. When using BCP to copy data into a table, which of the following
statements apply? Choose 2.
A) Database users will not be able to access the table because BCP
will lock it.
B) Database users will see the rows inserted by BCP after each batch
is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.> 1. When using BCP to copy data into a table, which of the following
> statements apply? Choose 2.
> A) Database users will not be able to access the table because BCP
> will lock it.
>
The default behavior is row locking so other users can use the table.
However, a TABLOCK hint can also be specified.

> B) Database users will see the rows inserted by BCP after each batch
> is complete.
>
A BCP batch is a transaction so users can see data once committed.

> C) You must have INSERT permissions on the table.
>
Yes.

> D) Existing rows are replaced by BCP.
>
Nope.
Hope this helps.
Dan Guzman
SQL Server MVP
"docsql" <docsql@.noemail.nospam> wrote in message
news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...|||Not sure if this question related to SQL2000 or SQL2005? Some more
information on permissions for BCP in SQL2005. You will note there is now a
requirement to get ALTER table permission if you are doing DDL operations
transparently. This was not the case in SQL2000. However, you will also
require SELECT permission on the traget table both in SQL2000 and SQL2005
A bcp out operation requires SELECT permission on the source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any of the
following is true:
(1)Constraints are disabled, which is the default behavior. To keep
constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint.
(2) Triggers are disabled, which is the default behavior. To fire triggers,
use the -h option with the FIRE_TRIGGERS hint.
(3) You use the -E option to import identity values from a data file.
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:elgg6Cp2FHA.3592@.TK2MSFTNGP12.phx.gbl...
> The default behavior is row locking so other users can use the table.
> However, a TABLOCK hint can also be specified.
>
> A BCP batch is a transaction so users can see data once committed.
>
> Yes.
>
> Nope.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

Batch Processing

/**
l'm trying to improve my batch processing routines by
creating a generic method which l can apply on most of
my jobs.Reason Being l'm working with large Data Sets
This is what l'm trying to achieve
1.Get The Rowcount of the Source
2.Split It Into manageable batches
3.Loop through the batches Until the last one is Done
**/
How do l intergrate these two so it can automatically pick up the
batchsizes and process until the last batch is done.
--Outer Loop
Declare @.RowCount Int
Declare @.LoopIteration Int
Declare @.BatchSize Int
--Get The RowCount Of the Table
Select @.RowCount = (Select Count(*) From tbTest)
--
Select @.LoopIteration = @.RowCount/2000000
Select @.BatchSize = @.RowCount/@.LoopIteration
--Select Distinct Client,Convert(Varchar(15),'') As CltNo
-- ,Convert(Int,0) As Status Into tbtest
from tbAddress
--Inner Loop --Works when run Manually
SET ROWCOUNT @.BatchSize--100000 --54 Secs
WHILE 1 = 1
BEGIN
Begin Transaction
Update tbTest
Set CltNo = Client + 1
,Status = 1 Where Status = 0
Commit
if @.@.rowcount = 0
break
END
SET ROWCOUNT 0
--Select * from tbTest Where Status = 1
Message posted via http://www.webservertalk.comWhy do you care what the table size is? You should pick a batch size that
can be accomplished in a short enough time so that it does not interfere
with other users and can have minimal overhead. It usually doesn't matter
if the table is 10M rows or 100K rows. Your batch size should be the same.
What that size is can only be found with proper testing and adjustment.
Usually 10K is a good place to start as it is small enough to finish quickly
and large enough to nibble at the work load. IF you check the @.@.ROWCOUNT
and break when it is 0 then you don't have to worry about how many loops
there will be. Another comment is that there is no need to wrap the Update
in a Begin - Commit. Each update is ATOMIC all by itself. If you update
10K rows in a single update statement all 10K will either work or they wont.
There will not be any middle ground. By adding it in there you now have to
put error checking and handle possible open trans.
Andrew J. Kelly SQL MVP
"Raymond M via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:a911f5b29ab44d0b837051f840966d55@.SQ
webservertalk.com...
> /**
> l'm trying to improve my batch processing routines by
> creating a generic method which l can apply on most of
> my jobs.Reason Being l'm working with large Data Sets
> This is what l'm trying to achieve
> 1.Get The Rowcount of the Source
> 2.Split It Into manageable batches
> 3.Loop through the batches Until the last one is Done
> **/
> How do l intergrate these two so it can automatically pick up the
> batchsizes and process until the last batch is done.
> --Outer Loop
> Declare @.RowCount Int
> Declare @.LoopIteration Int
> Declare @.BatchSize Int
>
> --Get The RowCount Of the Table
> Select @.RowCount = (Select Count(*) From tbTest)
> --
> Select @.LoopIteration = @.RowCount/2000000
> Select @.BatchSize = @.RowCount/@.LoopIteration
>
>
> --Select Distinct Client,Convert(Varchar(15),'') As CltNo
> -- ,Convert(Int,0) As Status Into tbtest
> from tbAddress
> --Inner Loop --Works when run Manually
> SET ROWCOUNT @.BatchSize--100000 --54 Secs
> WHILE 1 = 1
> BEGIN
> Begin Transaction
> Update tbTest
> Set CltNo = Client + 1
> ,Status = 1 Where Status = 0
> Commit
> if @.@.rowcount = 0
> break
> END
> SET ROWCOUNT 0
> --Select * from tbTest Where Status = 1
> --
> Message posted via http://www.webservertalk.com