build sql script: pretty

Showing posts with label pretty. Show all posts

Sunday, March 11, 2012

Thursday, March 8, 2012

Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
instance of SQL on my desktop. I create an SQL Login named BITest and allow
him into my Pubs DB. I then add him to the BulkAdmin fixed server role. When
I have him try to Bulk Insert, I get:
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table
'pubs.dbo.t_access'. Cannot perform SET operation.
Which sort of indicates he needs to be in the db_owner role? Even though I
think thats crap, I add him to db_owner, and then try to run my Bulk Insert
again. This time I get:
Server: Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the BULK INSERT statement.
So, not only does it really appear that he nees to be part of db_owner, it
still doesnt work. What gives?
--
TIA,
ChrisRChris,
From BOL:
Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute
BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
> instance of SQL on my desktop. I create an SQL Login named BITest and
> allow
> him into my Pubs DB. I then add him to the BulkAdmin fixed server role.
> When
> I have him try to Bulk Insert, I get:
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table
> 'pubs.dbo.t_access'. Cannot perform SET operation.
> Which sort of indicates he needs to be in the db_owner role? Even though
> I
> think thats crap, I add him to db_owner, and then try to run my Bulk
> Insert
> again. This time I get:
> Server: Msg 4834, Level 16, State 1, Line 1
> You do not have permission to use the BULK INSERT statement.
> So, not only does it really appear that he nees to be part of db_owner, it
> still doesnt work. What gives?
> --
> TIA,
> ChrisR|||Jerry, as indicated in my post, I added him to that role.
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> Chris,
> From BOL:
> Permissions
> Only members of the sysadmin and bulkadmin fixed server roles can execute
> BULK INSERT.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
> > instance of SQL on my desktop. I create an SQL Login named BITest and
> > allow
> > him into my Pubs DB. I then add him to the BulkAdmin fixed server role.
> > When
> > I have him try to Bulk Insert, I get:
> >
> > Server: Msg 8104, Level 16, State 2, Line 1
> > The current user is not the database or object owner of table
> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >
> > Which sort of indicates he needs to be in the db_owner role? Even though
> > I
> > think thats crap, I add him to db_owner, and then try to run my Bulk
> > Insert
> > again. This time I get:
> >
> > Server: Msg 4834, Level 16, State 1, Line 1
> > You do not have permission to use the BULK INSERT statement.
> >
> > So, not only does it really appear that he nees to be part of db_owner, it
> > still doesnt work. What gives?
> >
> > --
> > TIA,
> > ChrisR
>
>|||Chris,
I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
Do you really need to use BCP/BULK INSERT or would DTS possibly work?
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> Jerry, as indicated in my post, I added him to that role.
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> Chris,
>> From BOL:
>> Permissions
>> Only members of the sysadmin and bulkadmin fixed server roles can execute
>> BULK INSERT.
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
>> > local
>> > instance of SQL on my desktop. I create an SQL Login named BITest and
>> > allow
>> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
>> > role.
>> > When
>> > I have him try to Bulk Insert, I get:
>> >
>> > Server: Msg 8104, Level 16, State 2, Line 1
>> > The current user is not the database or object owner of table
>> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >
>> > Which sort of indicates he needs to be in the db_owner role? Even
>> > though
>> > I
>> > think thats crap, I add him to db_owner, and then try to run my Bulk
>> > Insert
>> > again. This time I get:
>> >
>> > Server: Msg 4834, Level 16, State 1, Line 1
>> > You do not have permission to use the BULK INSERT statement.
>> >
>> > So, not only does it really appear that he nees to be part of db_owner,
>> > it
>> > still doesnt work. What gives?
>> >
>> > --
>> > TIA,
>> > ChrisR
>>|||I have 48 reconds. DTS may work, but I'd really like to try to get this
going. You're user is NOT a member of either SA of Admins, right?
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> Chris,
> I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> > Jerry, as indicated in my post, I added him to that role.
> > --
> > TIA,
> > ChrisR
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Chris,
> >>
> >> From BOL:
> >>
> >> Permissions
> >> Only members of the sysadmin and bulkadmin fixed server roles can execute
> >> BULK INSERT.
> >>
> >> HTH
> >>
> >> Jerry
> >>
> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
> >> > local
> >> > instance of SQL on my desktop. I create an SQL Login named BITest and
> >> > allow
> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
> >> > role.
> >> > When
> >> > I have him try to Bulk Insert, I get:
> >> >
> >> > Server: Msg 8104, Level 16, State 2, Line 1
> >> > The current user is not the database or object owner of table
> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >> >
> >> > Which sort of indicates he needs to be in the db_owner role? Even
> >> > though
> >> > I
> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
> >> > Insert
> >> > again. This time I get:
> >> >
> >> > Server: Msg 4834, Level 16, State 1, Line 1
> >> > You do not have permission to use the BULK INSERT statement.
> >> >
> >> > So, not only does it really appear that he nees to be part of db_owner,
> >> > it
> >> > still doesnt work. What gives?
> >> >
> >> > --
> >> > TIA,
> >> > ChrisR
> >>
> >>
> >>
>
>|||I'd recommend DTS but it is your choice. No...created new login and user to
test for your example.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
>I have 48 reconds. DTS may work, but I'd really like to try to get this
> going. You're user is NOT a member of either SA of Admins, right?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> Chris,
>> I got it to work with BULKADMIN and DB_OWNER. How many records to you
>> have?
>> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
>> > Jerry, as indicated in my post, I added him to that role.
>> > --
>> > TIA,
>> > ChrisR
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Chris,
>> >>
>> >> From BOL:
>> >>
>> >> Permissions
>> >> Only members of the sysadmin and bulkadmin fixed server roles can
>> >> execute
>> >> BULK INSERT.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >>
>> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
>> >> > local
>> >> > instance of SQL on my desktop. I create an SQL Login named BITest
>> >> > and
>> >> > allow
>> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
>> >> > role.
>> >> > When
>> >> > I have him try to Bulk Insert, I get:
>> >> >
>> >> > Server: Msg 8104, Level 16, State 2, Line 1
>> >> > The current user is not the database or object owner of table
>> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >> >
>> >> > Which sort of indicates he needs to be in the db_owner role? Even
>> >> > though
>> >> > I
>> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
>> >> > Insert
>> >> > again. This time I get:
>> >> >
>> >> > Server: Msg 4834, Level 16, State 1, Line 1
>> >> > You do not have permission to use the BULK INSERT statement.
>> >> >
>> >> > So, not only does it really appear that he nees to be part of
>> >> > db_owner,
>> >> > it
>> >> > still doesnt work. What gives?
>> >> >
>> >> > --
>> >> > TIA,
>> >> > ChrisR
>> >>
>> >>
>> >>
>>|||Curious, why DTS? Also, Im running SQL2K, and you?
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> I'd recommend DTS but it is your choice. No...created new login and user to
> test for your example.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
> >I have 48 reconds. DTS may work, but I'd really like to try to get this
> > going. You're user is NOT a member of either SA of Admins, right?
> > --
> > TIA,
> > ChrisR
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Chris,
> >>
> >> I got it to work with BULKADMIN and DB_OWNER. How many records to you
> >> have?
> >> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
> >>
> >> HTH
> >>
> >> Jerry
> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> >> > Jerry, as indicated in my post, I added him to that role.
> >> > --
> >> > TIA,
> >> > ChrisR
> >> >
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Chris,
> >> >>
> >> >> From BOL:
> >> >>
> >> >> Permissions
> >> >> Only members of the sysadmin and bulkadmin fixed server roles can
> >> >> execute
> >> >> BULK INSERT.
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >>
> >> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> >> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
> >> >> > local
> >> >> > instance of SQL on my desktop. I create an SQL Login named BITest
> >> >> > and
> >> >> > allow
> >> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
> >> >> > role.
> >> >> > When
> >> >> > I have him try to Bulk Insert, I get:
> >> >> >
> >> >> > Server: Msg 8104, Level 16, State 2, Line 1
> >> >> > The current user is not the database or object owner of table
> >> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >> >> >
> >> >> > Which sort of indicates he needs to be in the db_owner role? Even
> >> >> > though
> >> >> > I
> >> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
> >> >> > Insert
> >> >> > again. This time I get:
> >> >> >
> >> >> > Server: Msg 4834, Level 16, State 1, Line 1
> >> >> > You do not have permission to use the BULK INSERT statement.
> >> >> >
> >> >> > So, not only does it really appear that he nees to be part of
> >> >> > db_owner,
> >> >> > it
> >> >> > still doesnt work. What gives?
> >> >> >
> >> >> > --
> >> >> > TIA,
> >> >> > ChrisR
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Its fast, friendly (permissions issue in your case) and more functional.
SQL 2000 as well.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B4925F9E-6BBB-477E-B4B7-9817D10CB89B@.microsoft.com...
> Curious, why DTS? Also, Im running SQL2K, and you?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> I'd recommend DTS but it is your choice. No...created new login and user
>> to
>> test for your example.
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
>> >I have 48 reconds. DTS may work, but I'd really like to try to get this
>> > going. You're user is NOT a member of either SA of Admins, right?
>> > --
>> > TIA,
>> > ChrisR
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Chris,
>> >>
>> >> I got it to work with BULKADMIN and DB_OWNER. How many records to you
>> >> have?
>> >> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
>> >> > Jerry, as indicated in my post, I added him to that role.
>> >> > --
>> >> > TIA,
>> >> > ChrisR
>> >> >
>> >> >
>> >> > "Jerry Spivey" wrote:
>> >> >
>> >> >> Chris,
>> >> >>
>> >> >> From BOL:
>> >> >>
>> >> >> Permissions
>> >> >> Only members of the sysadmin and bulkadmin fixed server roles can
>> >> >> execute
>> >> >> BULK INSERT.
>> >> >>
>> >> >> HTH
>> >> >>
>> >> >> Jerry
>> >> >>
>> >> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> >> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I
>> >> >> > have a
>> >> >> > local
>> >> >> > instance of SQL on my desktop. I create an SQL Login named BITest
>> >> >> > and
>> >> >> > allow
>> >> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed
>> >> >> > server
>> >> >> > role.
>> >> >> > When
>> >> >> > I have him try to Bulk Insert, I get:
>> >> >> >
>> >> >> > Server: Msg 8104, Level 16, State 2, Line 1
>> >> >> > The current user is not the database or object owner of table
>> >> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >> >> >
>> >> >> > Which sort of indicates he needs to be in the db_owner role?
>> >> >> > Even
>> >> >> > though
>> >> >> > I
>> >> >> > think thats crap, I add him to db_owner, and then try to run my
>> >> >> > Bulk
>> >> >> > Insert
>> >> >> > again. This time I get:
>> >> >> >
>> >> >> > Server: Msg 4834, Level 16, State 1, Line 1
>> >> >> > You do not have permission to use the BULK INSERT statement.
>> >> >> >
>> >> >> > So, not only does it really appear that he nees to be part of
>> >> >> > db_owner,
>> >> >> > it
>> >> >> > still doesnt work. What gives?
>> >> >> >
>> >> >> > --
>> >> >> > TIA,
>> >> >> > ChrisR
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Wednesday, March 7, 2012

Bulk Insert Data in Millions - Lock Issue

Hi all,
Hope there is a quick fix for this:

I am inserting data from one table to another on the same DB. The
insert is pretty simple as in:

insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable...

This inserts about 4 millions rows in one go. And since I had the
'cannot obtain lock resources' problem, several methods were suggested
by some web sites:
1) one to split the insert into smaller chunks (I have no idea how I
can spit a insert to insert only n records at a time..)

2)to use waitfor - which I did but did not fix the error.

3)use bulk insert (in t-sql) - I dont know how to do this?

As I see I am simply trying to move data from one table to another
(ofcourse lots of data) in SQL Server 2000 and I dont see one simple
solution to the locking problem.

any ideas on how best I can do this will save my day!

thanks all.Hi,

Adding in smaller chunks doesn't solve the problem. Adding small chunk is
the same as adding big chunk. Size doesn't matter.

Looks like you are out of option.

How about adding the records one by one in the middle of the night instead?

"adi" <Adityanad@.gmail.com> wrote in message
news:1129063850.247250.54770@.g49g2000cwa.googlegro ups.com...
> Hi all,
> Hope there is a quick fix for this:
>
> I am inserting data from one table to another on the same DB. The
> insert is pretty simple as in:
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable...
>
> This inserts about 4 millions rows in one go. And since I had the
> 'cannot obtain lock resources' problem, several methods were suggested
> by some web sites:
> 1) one to split the insert into smaller chunks (I have no idea how I
> can spit a insert to insert only n records at a time..)
>
> 2)to use waitfor - which I did but did not fix the error.
>
> 3)use bulk insert (in t-sql) - I dont know how to do this?
>
> As I see I am simply trying to move data from one table to another
> (ofcourse lots of data) in SQL Server 2000 and I dont see one simple
> solution to the locking problem.
>
> any ideas on how best I can do this will save my day!
>
> thanks all.|||adi (Adityanad@.gmail.com) writes:
> I am inserting data from one table to another on the same DB. The
> insert is pretty simple as in:
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable...
>
> This inserts about 4 millions rows in one go. And since I had the
> 'cannot obtain lock resources' problem, several methods were suggested
> by some web sites:
> 1) one to split the insert into smaller chunks (I have no idea how I
> can spit a insert to insert only n records at a time..)

There are a couple of techniques. But for best performance, you need
a clustered index in the table. This does not have to be unqiue though.
Say that you have a date column in the table that has a decent distribution.
Create a clustered index on tbat column:

CREATE CLUSTERED INDEX ON temptable(datecol)

Then you can do:

DECLARE @.this_date datetime,
@.next_date datetim
SELECT @.this_date = MIN(datecol) FROM temptable
WHILE @.this_date <= (SELECT datecol FROM temptable)
BEGIN
SELECT @.next_date = dateadd(MONTH, 1, @.this_date)
INSERT dataable (...)
SELECT ...
FROM temptable
WHERE datecol >= @.this_date
AND daetcol < @.next_date
SELECT @.this_date = @.next_date
END

It's important that the index is clustered to minimize seek times.
In the example I used month, but this can be changed as you see fit.

If you don't have a datetime column, maybe there is some id column or
similar. But you could even use a value like max temperature, as long
there is a decent disitribution.

You can also use SET ROWCOUNT to TOP to batch, but it's messier to
keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
with could have poor performance.)

> 2)to use waitfor - which I did but did not fix the error.

I will have to admit that I don't see the point with WAITFOR here.

> 3)use bulk insert (in t-sql) - I dont know how to do this?

This requires you to first unload the temptable to file with BCP, and
then load it to the target table with BCP or BULK INSERT. I skip
examples, as this would be a last resort for me.

Then again, your also try:

INSERT datatable (...) WITH (TABLOCKX)
SELECT ...

I have never tried it, but I expect it to lead to a single table lock
in the table, which which address the locking issue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Tom (nospam@.yahoo.com) writes:
> Adding in smaller chunks doesn't solve the problem. Adding small chunk is
> the same as adding big chunk. Size doesn't matter.

Huh? Care to elaborate?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There is no guarantee that the record in a smaller chunk will not be locked
by another user.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96ED17409A33Yazorman@.127.0.0.1...
> Tom (nospam@.yahoo.com) writes:
>> Adding in smaller chunks doesn't solve the problem. Adding small chunk
>> is
>> the same as adding big chunk. Size doesn't matter.
> Huh? Care to elaborate?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Tom (nospam@.yahoo.com) writes:
> There is no guarantee that the record in a smaller chunk will not be
> locked by another user.

I did not really recognize the error messages that Adi got, but it seemed
to me that the problem was to get locks on the table he is inserting to,
because of resource constraints, not because he was locked by another
user.

And even in a blocking scenario, smaller batches help as it reduces the
risk for exposure for blocking.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Just a thought, its too early :). What would happen if he used NOLOCK
option? He doesnt really need locks around the place does he?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96ECF2B98CD3FYazorman@.127.0.0.1...
> adi (Adityanad@.gmail.com) writes:
>> I am inserting data from one table to another on the same DB. The
>> insert is pretty simple as in:
>>
>> insert into datatable(field1, field2, field3)
>> select a1, a2, a3 from temptable...
>>
>>
>> This inserts about 4 millions rows in one go. And since I had the
>> 'cannot obtain lock resources' problem, several methods were suggested
>> by some web sites:
>> 1) one to split the insert into smaller chunks (I have no idea how I
>> can spit a insert to insert only n records at a time..)
> There are a couple of techniques. But for best performance, you need
> a clustered index in the table. This does not have to be unqiue though.
> Say that you have a date column in the table that has a decent
> distribution.
> Create a clustered index on tbat column:
> CREATE CLUSTERED INDEX ON temptable(datecol)
> Then you can do:
> DECLARE @.this_date datetime,
> @.next_date datetim
> SELECT @.this_date = MIN(datecol) FROM temptable
> WHILE @.this_date <= (SELECT datecol FROM temptable)
> BEGIN
> SELECT @.next_date = dateadd(MONTH, 1, @.this_date)
> INSERT dataable (...)
> SELECT ...
> FROM temptable
> WHERE datecol >= @.this_date
> AND daetcol < @.next_date
> SELECT @.this_date = @.next_date
> END
> It's important that the index is clustered to minimize seek times.
> In the example I used month, but this can be changed as you see fit.
> If you don't have a datetime column, maybe there is some id column or
> similar. But you could even use a value like max temperature, as long
> there is a decent disitribution.
> You can also use SET ROWCOUNT to TOP to batch, but it's messier to
> keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
> with could have poor performance.)
>> 2)to use waitfor - which I did but did not fix the error.
> I will have to admit that I don't see the point with WAITFOR here.
>> 3)use bulk insert (in t-sql) - I dont know how to do this?
> This requires you to first unload the temptable to file with BCP, and
> then load it to the target table with BCP or BULK INSERT. I skip
> examples, as this would be a last resort for me.
> Then again, your also try:
> INSERT datatable (...) WITH (TABLOCKX)
> SELECT ...
> I have never tried it, but I expect it to lead to a single table lock
> in the table, which which address the locking issue.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Just a thought, its too early :). What would happen if he used NOLOCK
> option? He doesnt really need locks around the place does he?

If the problem is on the table he is reading from yes. But I suspect that
it's on the table he is inserting to.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Having used this approach I still want to improve its performance.
My DBA says that splitting the insert statement into smaller chunks
will be very helpful - that is insert n number of records at a time.
so if I have 1 million rows to insert, he says its good to insert 50k
at a time.

Can someone help me on how to split the below insert to do that?

insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable.|||adi (Adityanad@.gmail.com) writes:
> Having used this approach I still want to improve its performance.
> My DBA says that splitting the insert statement into smaller chunks
> will be very helpful - that is insert n number of records at a time.
> so if I have 1 million rows to insert, he says its good to insert 50k
> at a time.
> Can someone help me on how to split the below insert to do that?
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable.

Which approach?

I did suggest a method for batching earlier in the thread. For your con-
venience I repost this here:

........................

CREATE CLUSTERED INDEX ON temptable(datecol)

Then you can do:

It's important that the index is clustered to minimize seek times.
In the example I used month, but this can be changed as you see fit.

If you don't have a datetime column, maybe there is some id column or
similar. But you could even use a value like max temperature, as long
there is a decent disitribution.

You can also use SET ROWCOUNT to TOP to batch, but it's messier to
keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
with could have poor performance.)

> 2)to use waitfor - which I did but did not fix the error.

I will have to admit that I don't see the point with WAITFOR here.

> 3)use bulk insert (in t-sql) - I dont know how to do this?

This requires you to first unload the temptable to file with BCP, and
then load it to the target table with BCP or BULK INSERT. I skip
examples, as this would be a last resort for me.

Then again, your also try:

INSERT datatable (...) WITH (TABLOCKX)
SELECT ...

I have never tried it, but I expect it to lead to a single table lock
in the table, which which address the locking issue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, February 24, 2012

bulk insert

Hi there !
a pretty silly question about bulk insert...
i'm trying to insert data into a table, but the first field is
quotation marked..so I wrote this in the fileformat
8.0
2
1 SQLCHAR 0 0 "" 0
dum1
2 SQLCHAR 0 50 "\"" 1
px_orig SQL_Latin1_General_CP1_CI_AS
where px_orig is the name of my field..
Whan I try to bulk insert using this, I get this error:
Cannot perform bulk insert. Invalid collation name for source 1 in
format file '...etc'
any idea'
thanks a lot...
++
VinceYou need to specify the leading quote as well as the row terminator in the
format file. Try something like:
8.0
3
1 SQLCHAR 0 0 "\"" 0 quote ""
2 SQLCHAR 0 50 "\"" 1 px_orig
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\r\n" 0 crlf ""
Hope this helps.
Dan Guzman
SQL Server MVP
"Vince .>" <vincent@.<remove> wrote in message
news:guai41dtlfg3qfi3ad7o6s9rpmt7f5afq4@.
4ax.com...
> Hi there !
> a pretty silly question about bulk insert...
> i'm trying to insert data into a table, but the first field is
> quotation marked..so I wrote this in the fileformat
> 8.0
> 2
> 1 SQLCHAR 0 0 "" 0
> dum1
> 2 SQLCHAR 0 50 "\"" 1
> px_orig SQL_Latin1_General_CP1_CI_AS
> where px_orig is the name of my field..
> Whan I try to bulk insert using this, I get this error:
> Cannot perform bulk insert. Invalid collation name for source 1 in
> format file '...etc'
> any idea'
> thanks a lot...
> ++
> Vince|||thanks :)

Sunday, March 11, 2012

Bulk Insert for non admins

Thursday, March 8, 2012

Bulk Insert for non admins

Bulk Insert for non admins

Wednesday, March 7, 2012

Bulk Insert Data in Millions - Lock Issue

Friday, February 24, 2012

bulk insert

build sql script

Blog Archive

About Me