Insert transaction batch size

I have insert statements that inserts 1.7 million rows from one server to
another. Half way through the insert, the connection gets lost. I am thinking
that because of the batch size the connection gets cut-off. How can I
implement a commit in T-SQL after say every 500 rows inserted ?
Thanks.
DXC,
You will have to manage this yourself in a stored procedure or sql batch.
Have you considered DTS - you can set the commit batch size there.
-- Bill
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:B8409B51-1A56-4CB5-946B-234102EDFA27@.microsoft.com...
>I have insert statements that inserts 1.7 million rows from one server to
> another. Half way through the insert, the connection gets lost. I am
> thinking
> that because of the batch size the connection gets cut-off. How can I
> implement a commit in T-SQL after say every 500 rows inserted ?
> Thanks.

Insert transaction batch size

I have insert statements that inserts 1.7 million rows from one server to
another. Half way through the insert, the connection gets lost. I am thinkin
g
that because of the batch size the connection gets cut-off. How can I
implement a commit in T-SQL after say every 500 rows inserted ?
Thanks.DXC,
You will have to manage this yourself in a stored procedure or sql batch.
Have you considered DTS - you can set the commit batch size there.
-- Bill
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:B8409B51-1A56-4CB5-946B-234102EDFA27@.microsoft.com...
>I have insert statements that inserts 1.7 million rows from one server to
> another. Half way through the insert, the connection gets lost. I am
> thinking
> that because of the batch size the connection gets cut-off. How can I
> implement a commit in T-SQL after say every 500 rows inserted ?
> Thanks.

Insert syntax error

I'm not quite sure if this is the right forum. I am trying to execute a insert statement. Using an ADO connection with Jet 4 (MS Access and VBA).

"INSERT INTO tblLocking (Table, Record, User) VALUES ('tblDiscrepancy','70','mnewheiser')"

I am getting a syntax error on the above statement. All fields that i am inserting to (Table, Record and User) are all text types, and the table does exist.

There is a fourth column in the table (LockID) which is a Access [Auto-Number] type(why i'm not sure on the forum) do i need to declare this in the statement.

Any help would be much appreciated as its is beginning to drive me mad.

CheersOk, so it appears the solutions wasn't as complicated as i thought it would be.

Although 'Table' and 'User' where valid columns in the table, they are also reserved words (or something like that). So in the statement they needs [ and ] around them (i.e. [Table])

Don't have to slit my wrists now. :)