Hi,
I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine.
I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option.
my command looks something like this,
bcp pub..employee in C:\data.txt -b1 -m100 -c -t, \n -Sdatabase -Uuser -Ppassword
here -b1 is, processing 1 row per batch transaction
-m100 is, ignoring first 100 errors
please help.
thanks
madhuWhy not use BULK INSERT where it accept the CHECK_CONSTRAINTS hint and CHECK_CONSTRAINT clause, respectively, which allows the user to specify whether constraints are checked during a bulk load.
2012年3月29日星期四
2012年3月25日星期日
Bcp to insert the records from .dbf file into a sql database table
Hi
i need to inser the .dbf file records into sql table.
How should i go about it?
regards,
Kiran
Care to expand on what a .dbf file is and what it looks like inside?
/Kenneth
|||Hi Kiran, Have you used BCP before? Is your question concerning how to use BCP or do you need instructions on using the FoxPro and Visual FoxPro OLE DB data provider to set up your BCP? What have you tried to do so far? What troubles have you had? -- Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP cindy_winegarden@.msn.com www.cindywinegarden.com> i need to inser the .dbf file records into sql table. >
> How should i go about it?|||Hi KeWin, DBF-extension tables can be created by several different apps, but in general they are FoxPro or Visual FoxPro tables. They are readable via the FoxPro and Visual FoxPro OLE DB data provider, and older tables are also readable via the FoxPro and Visual FoxPro ODBC drivers. -- Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP cindy_winegarden@.msn.com www.cindywinegarden.com
> Care to expand on what a .dbf file is and what it looks like inside? >
> /Kenneth >
>|||
Hi,
yes i have used bcp to export data in a csv file to a remote machine. But now i want to make a bulk insert of data from a dbf file to sql server 2000 database table.
This can be done through DTS but my question is that is it possible by bcp? if yes then how?
~Kiran
|||Hi Kiran, Rather than BCP you could set up a linked server and then use Insert Into ..... Select. -- Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP cindy_winegarden@.msn.com www.cindywinegarden.com> Hi, >
> yes i have used bcp to export data in a csv file to a remote machine.
> But now i want to make a bulk insert of data from a dbf file to sql
> server 2000 database table. >
> This can be done through DTS but my question is that is it possible by
> bcp? if yes then how? >
> ~Kiran > > >
>
BCP taking very long time.
I have a table which has near 50million records.
The .dat file created by BCP queryout is of around 51GB.
I am forming the BCP queryout and BCP IN commands in a stored proc and executing the same using xp_cmdshell.
I am using the below BCP options :
/T /b60000000 /a16384 /w /t}@.!{ /r}^#{\n /h"TABLOCK"
With above the QueryOut is taking around 8 hrs and BCP IN another 10 hrs. These figures are not very healthy.
The database recovery mode is set as simple.
Server config: Windows server 2003 X64 edition, with 16 gb RAM.
Any pointers on what could be the reason for this?
The table structure is as below:
CREATE TABLE XXX.dbo.ZZZ (
A nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,B nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,C nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,D datetime
,E numeric(22,7)
,F nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,G nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,H nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,I nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,J nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
,K nvarchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS
,L nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,M nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,N nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,O nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,P nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,Q datetime
,R nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,S nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,T nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,U datetime
,V datetime
,W nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,X nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Y nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Z nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,AA nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,BB nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,CC nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,DD nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,EE Datetime
,FF Datetime
,GG Datetime
,HH Datetime
)
According to the numbers, the data throughput for BCP OUT was ~1.8 MB/sec, for BCP IN - 1.4 MB/sec. Assuming that no blocking is involved the fact that the difference between BCP IN and BCP OUT throughput is only about 20% might be an indication that there is something wrong with the disk IO (or the network IO if you ran BCP from a remote system or if the BCP data files were located on a remote share) since you have plenty of CPU power to provide much higher import/export rates (for a reasonably balanced modern commodity system with gigabit network it can range from 20 to 70 MB/sec). The amount of available memory is not that critical thanks to the amount of data you import/export.
In other words, you need to indentify the bottleneck - the available data strongly suggests that it's the IO, so I would start with inspecting the disk subsystem where you store the BCP data files and/or the network if you store the files on a remote share.
|||Tenzig,
Thanks for your response !
You are right in guessing that I am running BCP from a remote system. However my data files are stored locally.
However what is intriguing is that the same set up is giving higher throughput for similar process on other tables. So the network can't be blamed as the bottleneck.
I am inspecting the disk subsystem where the data files are stored.
One more thing I observed that the M/c behaves like crazy while running few similar processes simultaneously, with 100% CPU usage and max. PF usage.
What could trigger that?
Did you use the same disk to place the BCP data files for the case where you observed higher throughput when importing/exporting data from other tables? Could you also collect the CPU and IO performance counter logs for the disk where the BCP data files are located for both the "slow" and the "fast" cases? Without the actual numbers it's hard to make any conclusions.
BCP taking very long time.
I have a table which has near 50million records.
The .dat file created by BCP queryout is of around 51GB.
I am forming the BCP queryout and BCP IN commands in a stored proc and executing the same using xp_cmdshell.
I am using the below BCP options :
/T /b60000000 /a16384 /w /t}@.!{ /r}^#{\n /h"TABLOCK"
With above the QueryOut is taking around 8 hrs and BCP IN another 10 hrs. These figures are not very healthy.
The database recovery mode is set as simple.
Server config: Windows server 2003 X64 edition, with 16 gb RAM.
Any pointers on what could be the reason for this?
The table structure is as below:
CREATE TABLE XXX.dbo.ZZZ (
A nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,B nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,C nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,D datetime
,E numeric(22,7)
,F nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,G nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,H nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,I nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,J nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
,K nvarchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS
,L nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,M nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,N nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,O nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,P nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,Q datetime
,R nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,S nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,T nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,U datetime
,V datetime
,W nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,X nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Y nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Z nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,AA nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,BB nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,CC nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,DD nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,EE Datetime
,FF Datetime
,GG Datetime
,HH Datetime
)
According to the numbers, the data throughput for BCP OUT was ~1.8 MB/sec, for BCP IN - 1.4 MB/sec. Assuming that no blocking is involved the fact that the difference between BCP IN and BCP OUT throughput is only about 20% might be an indication that there is something wrong with the disk IO (or the network IO if you ran BCP from a remote system or if the BCP data files were located on a remote share) since you have plenty of CPU power to provide much higher import/export rates (for a reasonably balanced modern commodity system with gigabit network it can range from 20 to 70 MB/sec). The amount of available memory is not that critical thanks to the amount of data you import/export.
In other words, you need to indentify the bottleneck - the available data strongly suggests that it's the IO, so I would start with inspecting the disk subsystem where you store the BCP data files and/or the network if you store the files on a remote share.
|||Tenzig,
Thanks for your response !
You are right in guessing that I am running BCP from a remote system. However my data files are stored locally.
However what is intriguing is that the same set up is giving higher throughput for similar process on other tables. So the network can't be blamed as the bottleneck.
I am inspecting the disk subsystem where the data files are stored.
One more thing I observed that the M/c behaves like crazy while running few similar processes simultaneously, with 100% CPU usage and max. PF usage.
What could trigger that?
Did you use the same disk to place the BCP data files for the case where you observed higher throughput when importing/exporting data from other tables? Could you also collect the CPU and IO performance counter logs for the disk where the BCP data files are located for both the "slow" and the "fast" cases? Without the actual numbers it's hard to make any conclusions.
2012年3月22日星期四
BCP process
I have a question about BCP in
When i do a BCP in from a file which has 3 mill records, the BCP insert goes
in very fast with notification showing 1000 records at a time but when all
rows are inserted after 3 mill records it takes considerable time for BCP in
to get over.
What is it doing at each stage '
Ps- Table has a primary key so i assume its a slow BCP
SanjaySanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...
goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.
But why is it taking that long i thought a commit is just like including a m
arker
Checkpoint is actuallly the process which writes committed transaction to th
e disk so that should be the one taking time.
Also which one is better in speed using -b1000 or doing it in one big transa
ction
Sanjay|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...
marker
the disk so that should be the one taking time.
transaction
I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
When i do a BCP in from a file which has 3 mill records, the BCP insert goes
in very fast with notification showing 1000 records at a time but when all
rows are inserted after 3 mill records it takes considerable time for BCP in
to get over.
What is it doing at each stage '
Ps- Table has a primary key so i assume its a slow BCP
SanjaySanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...
quote:
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.
quote:|||You mean its doin a COMMIT right...
> What is it doing at each stage '
> Ps- Table has a primary key so i assume its a slow BCP
> Sanjay
>
But why is it taking that long i thought a commit is just like including a m
arker
Checkpoint is actuallly the process which writes committed transaction to th
e disk so that should be the one taking time.
Also which one is better in speed using -b1000 or doing it in one big transa
ction
Sanjay|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...
quote:
> You mean its doin a COMMIT right...
> But why is it taking that long i thought a commit is just like including a
marker
quote:
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
quote:
> Also which one is better in speed using -b1000 or doing it in one big
transaction
quote:|||examnotes (sanjayg@.hotmail.com) writes:
> Sanjay
>
quote:
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
> goes in very fast with notification showing 1000 records at a time but
> when all rows are inserted after 3 mill records it takes considerable
> time for BCP in to get over.
> What is it doing at each stage '
I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
BCP process
I have a question about BCP i
When i do a BCP in from a file which has 3 mill records, the BCP insert goes in very fast with notification showing 1000 records at a time but when all rows are inserted after 3 mill records it takes considerable time for BCP in to get over
What is it doing at each stage ?
Ps- Table has a primary key so i assume its a slow BC
SanjaSanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.
> What is it doing at each stage '
> Ps- Table has a primary key so i assume its a slow BCP
> Sanjay
>|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...
> You mean its doin a COMMIT right...
> But why is it taking that long i thought a commit is just like including a
marker
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> Also which one is better in speed using -b1000 or doing it in one big
transaction
> Sanjay
>|||=?Utf-8?B?U2FuamF5?= (sanjayg@.hotmail.com) writes:
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
> goes in very fast with notification showing 1000 records at a time but
> when all rows are inserted after 3 mill records it takes considerable
> time for BCP in to get over.
> What is it doing at each stage '
I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
When i do a BCP in from a file which has 3 mill records, the BCP insert goes in very fast with notification showing 1000 records at a time but when all rows are inserted after 3 mill records it takes considerable time for BCP in to get over
What is it doing at each stage ?
Ps- Table has a primary key so i assume its a slow BC
SanjaSanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.
> What is it doing at each stage '
> Ps- Table has a primary key so i assume its a slow BCP
> Sanjay
>|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...
> You mean its doin a COMMIT right...
> But why is it taking that long i thought a commit is just like including a
marker
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> Also which one is better in speed using -b1000 or doing it in one big
transaction
> Sanjay
>|||=?Utf-8?B?U2FuamF5?= (sanjayg@.hotmail.com) writes:
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
> goes in very fast with notification showing 1000 records at a time but
> when all rows are inserted after 3 mill records it takes considerable
> time for BCP in to get over.
> What is it doing at each stage '
I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
BCP problem.Pls solve anyone immediately
Hi all,
I tried with that but i am able to inserting abtable(this has 4 cols and 12000 records) with bcp.that is working very good.but i'm getting problem with other table bibtable(this has 52 cols and 73000 records) with bcp but it is inserting all rows with dts.i want to insert both of the tables either of one bcp or dts to insert data into sqlserver 7.0
my bcp commands are:
bcp master..BIBDATA in c:\BIB20031006.TXT -fc:\MSSQL7\Binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
this is working properly with abtable but not with bibtable
bcp master..bibdata in c:\medsite\idocfile\new\bib20031006.txt -c -F2 -t\t -r\n -e c:\mssql7\binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
this is not working with anyone
in dts:
.delimited
filetype:ANSI SKIP ROWS:0
ROW DELIMITER:LF FIRST ROW HAS COLNAMES CHECKED
TEXT QUALIFIER:''
this is inserting bibtable perfectly but not abtable(inserting few records only)Try removing the space after the -e in the string -e c:\mssql7\binn\bib52.fmt so it looks like -ec:\mssql7\binn\bib52.fmt and put spaces in this string -SJAVADEV2-PC-NJ so it looks like -SJAVADEV2 -PC -NJ|||Try removing the -F2, this is telling the bcp utility to only copy the first two rows.
I tried with that but i am able to inserting abtable(this has 4 cols and 12000 records) with bcp.that is working very good.but i'm getting problem with other table bibtable(this has 52 cols and 73000 records) with bcp but it is inserting all rows with dts.i want to insert both of the tables either of one bcp or dts to insert data into sqlserver 7.0
my bcp commands are:
bcp master..BIBDATA in c:\BIB20031006.TXT -fc:\MSSQL7\Binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
this is working properly with abtable but not with bibtable
bcp master..bibdata in c:\medsite\idocfile\new\bib20031006.txt -c -F2 -t\t -r\n -e c:\mssql7\binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
this is not working with anyone
in dts:
.delimited
filetype:ANSI SKIP ROWS:0
ROW DELIMITER:LF FIRST ROW HAS COLNAMES CHECKED
TEXT QUALIFIER:''
this is inserting bibtable perfectly but not abtable(inserting few records only)Try removing the space after the -e in the string -e c:\mssql7\binn\bib52.fmt so it looks like -ec:\mssql7\binn\bib52.fmt and put spaces in this string -SJAVADEV2-PC-NJ so it looks like -SJAVADEV2 -PC -NJ|||Try removing the -F2, this is telling the bcp utility to only copy the first two rows.
2012年3月11日星期日
Bcp in problem
Hi,
I am loading a table with 7 million records using bcp in, however, what i noticed it that the bcp in process terminates when it reaches around 2.5 million rows.
I don't feel there should be any such limitation on bcp, however would like to know if anyone have come across this problem and how they have worked around to resolve this problem.
Thanks.
Hi,
Can you do the BCP IN with '-b' option. This option specifies the number of
rows per batch of data copied. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back ( in the case of failure)
after the completion of each batch.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.
|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number of
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number of
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
|||There must be some problem that is specific to your environment. have you
tried using the -e (for error file) and -o (for output file)?
Most likely some details should be written to one of these files that would
help you diagnose the problem.
--TJTODD
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.
|||Yes i use the -e option however the error file is empty. It runs without error however terminates after 2 million records. Any suggestions
Thanks.
"TJTODD" wrote:
> There must be some problem that is specific to your environment. have you
> tried using the -e (for error file) and -o (for output file)?
> Most likely some details should be written to one of these files that would
> help you diagnose the problem.
> --TJTODD
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
I am loading a table with 7 million records using bcp in, however, what i noticed it that the bcp in process terminates when it reaches around 2.5 million rows.
I don't feel there should be any such limitation on bcp, however would like to know if anyone have come across this problem and how they have worked around to resolve this problem.
Thanks.
Hi,
Can you do the BCP IN with '-b' option. This option specifies the number of
rows per batch of data copied. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back ( in the case of failure)
after the completion of each batch.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.
|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number of
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number of
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
|||There must be some problem that is specific to your environment. have you
tried using the -e (for error file) and -o (for output file)?
Most likely some details should be written to one of these files that would
help you diagnose the problem.
--TJTODD
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.
|||Yes i use the -e option however the error file is empty. It runs without error however terminates after 2 million records. Any suggestions
Thanks.
"TJTODD" wrote:
> There must be some problem that is specific to your environment. have you
> tried using the -e (for error file) and -o (for output file)?
> Most likely some details should be written to one of these files that would
> help you diagnose the problem.
> --TJTODD
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
Bcp in problem
Hi,
I am loading a table with 7 million records using bcp in, however, what i no
ticed it that the bcp in process terminates when it reaches around 2.5 milli
on rows.
I don't feel there should be any such limitation on bcp, however would like
to know if anyone have come across this problem and how they have worked aro
und to resolve this problem.
Thanks.Hi,
Can you do the BCP IN with '-b' option. This option specifies the number of
rows per batch of data copied. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back ( in the case of failure)
after the completion of each batch.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number o
f
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number o
f
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>|||There must be some problem that is specific to your environment. have you
tried using the -e (for error file) and -o (for output file)?
Most likely some details should be written to one of these files that would
help you diagnose the problem.
--TJTODD
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.|||Yes i use the -e option however the error file is empty. It runs without e
rror however terminates after 2 million records. Any suggestions
Thanks.
"TJTODD" wrote:
> There must be some problem that is specific to your environment. have you
> tried using the -e (for error file) and -o (for output file)?
> Most likely some details should be written to one of these files that woul
d
> help you diagnose the problem.
> --TJTODD
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
I am loading a table with 7 million records using bcp in, however, what i no
ticed it that the bcp in process terminates when it reaches around 2.5 milli
on rows.
I don't feel there should be any such limitation on bcp, however would like
to know if anyone have come across this problem and how they have worked aro
und to resolve this problem.
Thanks.Hi,
Can you do the BCP IN with '-b' option. This option specifies the number of
rows per batch of data copied. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back ( in the case of failure)
after the completion of each batch.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number o
f
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number o
f
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>|||There must be some problem that is specific to your environment. have you
tried using the -e (for error file) and -o (for output file)?
Most likely some details should be written to one of these files that would
help you diagnose the problem.
--TJTODD
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.|||Yes i use the -e option however the error file is empty. It runs without e
rror however terminates after 2 million records. Any suggestions
Thanks.
"TJTODD" wrote:
> There must be some problem that is specific to your environment. have you
> tried using the -e (for error file) and -o (for output file)?
> Most likely some details should be written to one of these files that woul
d
> help you diagnose the problem.
> --TJTODD
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
2012年2月25日星期六
BCP column insert question
HI,
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:
Text file data:
1234 abc def ghi jkl mno
Expected result:
column1 1234
column2 abc def ghi jkl mno
column3 null
column4 N
My table has four columns (column1, column2, column3, column4). I
would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
column3 is blank and column4 is always 'N' as shown above. I setup the
column4 to be defaulted to 'N'. However, I'm getting an error
regarding string truncation because, I think, SQL server is trying to
insert the 'abc def ghi jkl mno' into different columns. So the
question is what can I do to tell the bcp utility that 'abc def ghi
jkl mno' belongs to column2?
Appreciated any help.
Thanks,
TeresaTeresa,
Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
because then if there is a comma in the data it won't hose your bulk insert.
Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
should map correctly once it is delimited.
Here is an example from the Transact SQL online:
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
)
Hope this helps!
Barry
"TThai" <tpthai@.pepco.com> wrote in message
news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> HI,
> I'm trying to insert records to a table using bcp command. The
> problem is the input file to the bcp is a text file that looks like
> this:
> Text file data:
> 1234 abc def ghi jkl mno
> Expected result:
> column1 1234
> column2 abc def ghi jkl mno
> column3 null
> column4 N
> My table has four columns (column1, column2, column3, column4). I
> would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> column3 is blank and column4 is always 'N' as shown above. I setup the
> column4 to be defaulted to 'N'. However, I'm getting an error
> regarding string truncation because, I think, SQL server is trying to
> insert the 'abc def ghi jkl mno' into different columns. So the
> question is what can I do to tell the bcp utility that 'abc def ghi
> jkl mno' belongs to column2?
> Appreciated any help.
> Thanks,
> Teresa|||"Barry Young" <youngbar@.insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@.attbi_s54>...
> Teresa,
> Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
> because then if there is a comma in the data it won't hose your bulk insert.
> Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
> should map correctly once it is delimited.
> Here is an example from the Transact SQL online:
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> )
> Hope this helps!
> Barry
> "TThai" <tpthai@.pepco.com> wrote in message
> news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> > HI,
> > I'm trying to insert records to a table using bcp command. The
> > problem is the input file to the bcp is a text file that looks like
> > this:
> > Text file data:
> > 1234 abc def ghi jkl mno
> > Expected result:
> > column1 1234
> > column2 abc def ghi jkl mno
> > column3 null
> > column4 N
> > My table has four columns (column1, column2, column3, column4). I
> > would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> > column3 is blank and column4 is always 'N' as shown above. I setup the
> > column4 to be defaulted to 'N'. However, I'm getting an error
> > regarding string truncation because, I think, SQL server is trying to
> > insert the 'abc def ghi jkl mno' into different columns. So the
> > question is what can I do to tell the bcp utility that 'abc def ghi
> > jkl mno' belongs to column2?
> > Appreciated any help.
> > Thanks,
> > Teresa
Hi Barry,
Appreciated your response. I'll try it and keep you posted. Have a good day.
Thanks,
Teresa|||"Barry Young" <youngbar@.insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@.attbi_s54>...
> Teresa,
> Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
> because then if there is a comma in the data it won't hose your bulk insert.
> Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
> should map correctly once it is delimited.
> Here is an example from the Transact SQL online:
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> )
> Hope this helps!
Hi Barry,
I just tried and am getting an error 'Server: Msg 4860, Level 16,
State 1, Line 1
Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
there any preliminary setup that I have to do to recognize the file?
Here is what I executed in SQL analyzer.
BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
FROM 'C:\transactions.txt'
with
(FIELDTERMINATOR = '|')
Thanks,
Teresa
> Barry
> "TThai" <tpthai@.pepco.com> wrote in message
> news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> > HI,
> > I'm trying to insert records to a table using bcp command. The
> > problem is the input file to the bcp is a text file that looks like
> > this:
> > Text file data:
> > 1234 abc def ghi jkl mno
> > Expected result:
> > column1 1234
> > column2 abc def ghi jkl mno
> > column3 null
> > column4 N
> > My table has four columns (column1, column2, column3, column4). I
> > would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> > column3 is blank and column4 is always 'N' as shown above. I setup the
> > column4 to be defaulted to 'N'. However, I'm getting an error
> > regarding string truncation because, I think, SQL server is trying to
> > insert the 'abc def ghi jkl mno' into different columns. So the
> > question is what can I do to tell the bcp utility that 'abc def ghi
> > jkl mno' belongs to column2?
> > Appreciated any help.
> > Thanks,
> > Teresa|||TThai (tpthai@.pepco.com) writes:
> Hi Barry,
> I just tried and am getting an error 'Server: Msg 4860, Level 16,
> State 1, Line 1
> Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
> there any preliminary setup that I have to do to recognize the file?
> Here is what I executed in SQL analyzer.
> BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
> FROM 'C:\transactions.txt'
> with
> (FIELDTERMINATOR = '|')
BULK INSERT operates on the server, so it is looking a C:\ at your server.
If your file is on a client machine, you are better off with BCP. You
can specify field terminator with the -t options. Since | is a meta-
character for the command shell, you need to quote it:
bcp db..tbl in yourfile.txt -c -t "|" -S ...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns958BF277E1611Yazorman@.127.0.0.1>...
> TThai (tpthai@.pepco.com) writes:
> > Hi Barry,
> > I just tried and am getting an error 'Server: Msg 4860, Level 16,
> > State 1, Line 1
> > Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
> > there any preliminary setup that I have to do to recognize the file?
> > Here is what I executed in SQL analyzer.
> > BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
> > FROM 'C:\transactions.txt'
> > with
> > (FIELDTERMINATOR = '|')
> BULK INSERT operates on the server, so it is looking a C:\ at your server.
> If your file is on a client machine, you are better off with BCP. You
> can specify field terminator with the -t options. Since | is a meta-
> character for the command shell, you need to quote it:
> bcp db..tbl in yourfile.txt -c -t "|" -S ...
Thank you very much. It worked!
Teresa
2012年2月23日星期四
BCP and Function
Hi, I have 2 questions:
1. Is it possible that I can filter out the records and only load those that I wanted during BCP loading?
I am using SQL Server 2005 and doing the bulk copying of the data from a .dat file into a SQL table.
The BCP process is working fine, but one of the column in this .dat file indicate the type of data I am getting.
One is 'T' for text, the other is 'N' for numeric. So, is it possible that I can import the data of these 2 types
separately, meaning I want to load the 'N' type first, then 'T' after, but it does not have to go in that order.
Whichever type goes first is fine, but can I filter out like we could in SQL with the where clause?
2. Is it possible to have the value returns from a function as a list of items, instead of an individual item.
for example, in query, i can say I wanted a list of cities ....where city in(select city from sometable), but
i know function only allows me to return one value (New York) for example, not the whole list of cities within the US.
so, is this possible with a function or I can only do this with stored procedure?
i have both scalar and in-line/variable table function, but none of them give me what I need.
the scalar only returns 1 value, and the table function I would still only can put in specific parameter for it to return
a list. For instance, from using my table function, I have "select * from ::fn_test(ID, city)', this mean I still
can only put in one id and one city at a time with all the columns that I specified, not a list of ID and cities.
do I make any sense? please help/advise if you can, appreciated much.
1. Not really. Better methods include using SSIS -or loading into a work table, and then having a script or stored procedure handle the data in the work table, making whatever alterations/validations/cleanups are necessary before moving the data into the production table.
2. You can create a Table Valued Function (TVF) that will return a single column table (which is a list). I suspect that you are not completely clear about how you can use that TVF. Perhaps if you created a new post, and you provided more concete information, including the table DDL, sample data in the form of INSERT statements, and a clear explanation of your desired results, we may be better able to help you. (See this link for more information about how to prepare your submission.)
bcp and exporting
Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>
bcp and exporting
Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>
2012年2月18日星期六
BCP
Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?
It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?
It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
BCP
Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
BCP
Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) on
ly
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) on
ly
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
2012年2月16日星期四
BatchOptimistic
Hi everybody,
I want to read dataset records from other dataset before updatebatch
process. Is this possible ? If this is possible how can i do ?
-- all DataSets locktype is BatchOptimistic
Thanks for adviseAre you talking about ADO? I see no reason why you can't read records from
one recordset and update records on another recordset using optimistic
locking, but try getting an answer from the microsoft.public.data.ado group.
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:%23TfwfaG9FHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi everybody,
> I want to read dataset records from other dataset before updatebatch
> process. Is this possible ? If this is possible how can i do ?
> -- all DataSets locktype is BatchOptimistic
> Thanks for advise
>|||DataSets LockType is BatchOptimistic.
Yes ADO and Database server is SQLServer
I want to read dataset records from other dataset before updatebatch
process. Is this possible ? If this is possible how can i do ?
-- all DataSets locktype is BatchOptimistic
Thanks for adviseAre you talking about ADO? I see no reason why you can't read records from
one recordset and update records on another recordset using optimistic
locking, but try getting an answer from the microsoft.public.data.ado group.
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:%23TfwfaG9FHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi everybody,
> I want to read dataset records from other dataset before updatebatch
> process. Is this possible ? If this is possible how can i do ?
> -- all DataSets locktype is BatchOptimistic
> Thanks for advise
>|||DataSets LockType is BatchOptimistic.
Yes ADO and Database server is SQLServer
2012年2月13日星期一
Batch Insert into a Table
I have a large volume of records I need to insert into a table, but am
running into issues with my transaction log filling up. I want to
create a commit point at every 50-100k records, but don't know how to
do that in SQL Server. I was thinking it has something to do with the
@.@.TRANCOUNT or @.@.ROWCOUNT system variables, but again, not sure how to
use them.
Does anyone have any code or suggestions on an approach?
Many thanks,
DanYou're transaction log will fill up when doing large inserts, regardless of
whether you do it in small batches or all at once.
You could use ALTER DATABASE to put your db into bulked-logged recovery
mode, then do the insert
ALTER DATABASE [YourDb] SET RECOVERY BULK_LOGGED
--do large insert, then set recovery model back to full
ALTER DATABASE [YourDb] SET RECOVERY FULL
"Dan" wrote:
> I have a large volume of records I need to insert into a table, but am
> running into issues with my transaction log filling up. I want to
> create a commit point at every 50-100k records, but don't know how to
> do that in SQL Server. I was thinking it has something to do with the
> @.@.TRANCOUNT or @.@.ROWCOUNT system variables, but again, not sure how to
> use them.
> Does anyone have any code or suggestions on an approach?
> Many thanks,
> Dan
>|||I should also note that the data is already in the database and I'm
trying to perform an insert from a staging/temp table into another
table:
insert into final_table (field1, field2,...fieldN)
select field1, field2,...fieldN
from staging_table
running into issues with my transaction log filling up. I want to
create a commit point at every 50-100k records, but don't know how to
do that in SQL Server. I was thinking it has something to do with the
@.@.TRANCOUNT or @.@.ROWCOUNT system variables, but again, not sure how to
use them.
Does anyone have any code or suggestions on an approach?
Many thanks,
DanYou're transaction log will fill up when doing large inserts, regardless of
whether you do it in small batches or all at once.
You could use ALTER DATABASE to put your db into bulked-logged recovery
mode, then do the insert
ALTER DATABASE [YourDb] SET RECOVERY BULK_LOGGED
--do large insert, then set recovery model back to full
ALTER DATABASE [YourDb] SET RECOVERY FULL
"Dan" wrote:
> I have a large volume of records I need to insert into a table, but am
> running into issues with my transaction log filling up. I want to
> create a commit point at every 50-100k records, but don't know how to
> do that in SQL Server. I was thinking it has something to do with the
> @.@.TRANCOUNT or @.@.ROWCOUNT system variables, but again, not sure how to
> use them.
> Does anyone have any code or suggestions on an approach?
> Many thanks,
> Dan
>|||I should also note that the data is already in the database and I'm
trying to perform an insert from a staging/temp table into another
table:
insert into final_table (field1, field2,...fieldN)
select field1, field2,...fieldN
from staging_table
Batch Failing
I run two batches at night time:
1) A batch that updates all the records one by one to
calculate special interest on accounts.
2) The databas optimization batch that cleans up pages,
free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column
information is insufficient or incorrect. Too many rows
were affected by update." I do know that batch 2 is
running in tbe background still. They both take around 3-4
hours each. It's hard to schedual them apart enough to
keep them from running at the same time because their run
lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason RoozeeThis is a multi-part message in MIME format.
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
My guess is that batch 1 is using a cursor or raw ADO to do updates on a =table that does not have a primary key. Ensure that all tables have =primary keys and that you make every effort to replace row-by-row =processing with set-level processing.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
I run two batches at night time:
1) A batch that updates all the records one by one to calculate special interest on accounts.
2) The databas optimization batch that cleans up pages, free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column information is insufficient or incorrect. Too many rows were affected by update." I do know that batch 2 is running in tbe background still. They both take around 3-4 hours each. It's hard to schedual them apart enough to keep them from running at the same time because their run lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
My guess is that batch 1 is using a =cursor or raw ADO to do updates on a table that does not have a primary key. =Ensure that all tables have primary keys and that you make every effort to replace row-by-row processing with set-level processing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee" wrote in =message news:2bb701c373b9$29=99dfc0$a401280a@.phx.gbl...I run two batches at night time:1) A batch that updates all the =records one by one to calculate special interest on accounts.2) The =databas optimization batch that cleans up pages, free space, rebuilds =indexes, etc.My batch 1 is failing with this error: "Key column =information is insufficient or incorrect. Too many rows were affected by =update." I do know that batch 2 is running in tbe background still. They both take =around 3-4 hours each. It's hard to schedual them apart enough to keep =them from running at the same time because their run lengths are so unpredictable. Any ideas?My updates are using ado 2.7Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00--|||This is a multi-part message in MIME format.
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ste-level process avoids the use of cursors and loops. Here, you would =use, say, a single UPDATE statement and apply the calculation across all =of the target rows.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2c0e01c373bb$e9832330$a401280a@.phx.gbl...
Yes, all the tables have a primary key and it's included in the select list of fields in my ADO record set...
"SET LEVEL" processing' Explain.
Jason
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jason Roozee" <jason@.camcoinc.net> wrote in message news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
>I run two batches at night time:
>1) A batch that updates all the records one by one to >calculate special interest on accounts.
>2) The databas optimization batch that cleans up pages, >free space, rebuilds indexes, etc.
>My batch 1 is failing with this error: "Key column >information is insufficient or incorrect. Too many rows >were affected by update." I do know that batch 2 is >running in tbe background still. They both take around 3-
4 >hours each. It's hard to schedual them apart enough to >keep them from running at the same time because their run >lengths are so unpredictable. >
>Any ideas?
>My updates are using ado 2.7
>Jason Roozee
>
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ste-level process avoids the use of =cursors and loops. Here, you would use, say, a single UPDATE statement and =apply the calculation across all of the target rows.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee" wrote in =message news:2c0e01c373bb$e9=832330$a401280a@.phx.gbl...Yes, all the tables have a primary key and it's included in the select =list of fields in my ADO record set..."SET LEVEL" processing' Explain.Jason>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Jaso=n Roozee" wrote in message news:2bb701c373b9$29=99dfc0$a401280a@.phx.gbl...>>I run two batches at night time:>>1) A batch that updates =all the records one by one to >calculate special interest on accounts.>>2) The databas optimization batch that cleans =up pages, >free space, rebuilds indexes, etc.>>My batch 1 is =failing with this error: "Key column >information is insufficient or =incorrect. Too many rows >were affected by update." I do know that batch 2 =is >running in tbe background still. They both take around 3-4 >hours each. It's hard to schedual them apart enough to =>keep them from running at the same time because their run >lengths are so unpredictable. >>>Any ideas?>>My =updates are using ado 2.7>>Jason Roozee>
--=_NextPart_000_0166_01C3739A.E6D14CA0--
1) A batch that updates all the records one by one to
calculate special interest on accounts.
2) The databas optimization batch that cleans up pages,
free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column
information is insufficient or incorrect. Too many rows
were affected by update." I do know that batch 2 is
running in tbe background still. They both take around 3-4
hours each. It's hard to schedual them apart enough to
keep them from running at the same time because their run
lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason RoozeeThis is a multi-part message in MIME format.
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
My guess is that batch 1 is using a cursor or raw ADO to do updates on a =table that does not have a primary key. Ensure that all tables have =primary keys and that you make every effort to replace row-by-row =processing with set-level processing.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
I run two batches at night time:
1) A batch that updates all the records one by one to calculate special interest on accounts.
2) The databas optimization batch that cleans up pages, free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column information is insufficient or incorrect. Too many rows were affected by update." I do know that batch 2 is running in tbe background still. They both take around 3-4 hours each. It's hard to schedual them apart enough to keep them from running at the same time because their run lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
My guess is that batch 1 is using a =cursor or raw ADO to do updates on a table that does not have a primary key. =Ensure that all tables have primary keys and that you make every effort to replace row-by-row processing with set-level processing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee"
--=_NextPart_000_012C_01C37398.83003D00--|||This is a multi-part message in MIME format.
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ste-level process avoids the use of cursors and loops. Here, you would =use, say, a single UPDATE statement and apply the calculation across all =of the target rows.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2c0e01c373bb$e9832330$a401280a@.phx.gbl...
Yes, all the tables have a primary key and it's included in the select list of fields in my ADO record set...
"SET LEVEL" processing' Explain.
Jason
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jason Roozee" <jason@.camcoinc.net> wrote in message news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
>I run two batches at night time:
>1) A batch that updates all the records one by one to >calculate special interest on accounts.
>2) The databas optimization batch that cleans up pages, >free space, rebuilds indexes, etc.
>My batch 1 is failing with this error: "Key column >information is insufficient or incorrect. Too many rows >were affected by update." I do know that batch 2 is >running in tbe background still. They both take around 3-
4 >hours each. It's hard to schedual them apart enough to >keep them from running at the same time because their run >lengths are so unpredictable. >
>Any ideas?
>My updates are using ado 2.7
>Jason Roozee
>
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ste-level process avoids the use of =cursors and loops. Here, you would use, say, a single UPDATE statement and =apply the calculation across all of the target rows.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee"
--=_NextPart_000_0166_01C3739A.E6D14CA0--
2012年2月12日星期日
Batch execution
Hello All,
I have a table TableX with one column SQL varchar(8000). This column
contains about 50 records (rows) like
insert into tablename1 select datetime,...From Tbl1 Where...
insert into tablename2 select datetime,...From Tbl2 Where...
insert into tablename3 select datetime,...From Tbl3 Where...
...
My question is, is there any way to run these dml statements from tableX all
at one time as a batch rather than looping through TableX and executing them
one by one.
Thanks
RickyHere goes:
set nocount on;
use tempdb;
go
drop table t1, tablex;
go
create table t1(col1 int);
go
create table tablex(sql varchar(8000) not null);
insert into tablex values('insert into t1 values(1);');
insert into tablex values('insert into t1 values(2);');
insert into tablex values('insert into t1 values(3);');
go
exec master..xp_execresultset
N'select sql from tablex;',
N'tempdb'
go
select * from t1;
-- Output:
col1
--
1
2
3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
> Hello All,
> I have a table TableX with one column SQL varchar(8000). This column
> contains about 50 records (rows) like
> insert into tablename1 select datetime,...From Tbl1 Where...
> insert into tablename2 select datetime,...From Tbl2 Where...
> insert into tablename3 select datetime,...From Tbl3 Where...
> ...
> My question is, is there any way to run these dml statements from tableX
> all
> at one time as a batch rather than looping through TableX and executing
> them
> one by one.
> Thanks
> Ricky|||Thank You Itzik Ben-Gan.
"Brilliant as always...!!"
"Itzik Ben-Gan" wrote:
> Here goes:
> set nocount on;
> use tempdb;
> go
> drop table t1, tablex;
> go
> create table t1(col1 int);
> go
> create table tablex(sql varchar(8000) not null);
> insert into tablex values('insert into t1 values(1);');
> insert into tablex values('insert into t1 values(2);');
> insert into tablex values('insert into t1 values(3);');
> go
> exec master..xp_execresultset
> N'select sql from tablex;',
> N'tempdb'
> go
> select * from t1;
> -- Output:
> col1
> --
> 1
> 2
> 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
>
>|||Anybody know how to get the value of an output parameter from a proc
executed with xp_ExecuteResultSet?
For Example:
set nocount on
use tempdb
go
create table T (t text default '')
go
create proc P (@.T1 text, @.Message varchar(255) Out)
as
begin
select @.T1
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
DECLARE @.ErrorMessage nvarchar(4000)
SET @.SQL = 'exec p ''This is a test.'', @.ErrorMessage'
exec master..xp_execresultset @.SQL, N'tempdb'
select @.ErrorMessage
go
drop table T
drop proc P
Message posted via http://www.webservertalk.com|||--It would go something like this.
set nocount on
use tempdb
go
Create Table ##ErrorMessage (F1 [varchar] (8000))
go
create proc P (@.T1 nvarchar(4000), @.Message varchar(255) Out)
as
begin
select
T1 = @.T1,
Message = @.Message
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
SET @.SQL = 'EXECUTE(''' + char(13) + char(10) +
'DECLARE @.ErrorMessage nvarchar(4000) ' + char(13) + char(10) +
'exec p ''''This is a test.'''', @.ErrorMessage OUT' + char(13) + char(10)
+
'insert ##ErrorMessage values(@.ErrorMessage)' + char(13) + char(10) +
''')'
--exec sp_executesql @.SQL
SET @.SQL = 'SELECT ''' + Replace(@.SQL, '''', ''') + ''''
exec master..xp_execresultset @.SQL, N'tempdb'
select Message = F1 from ##ErrorMessage
go
drop table ##ErrorMessage
drop proc P
Message posted via http://www.webservertalk.com
I have a table TableX with one column SQL varchar(8000). This column
contains about 50 records (rows) like
insert into tablename1 select datetime,...From Tbl1 Where...
insert into tablename2 select datetime,...From Tbl2 Where...
insert into tablename3 select datetime,...From Tbl3 Where...
...
My question is, is there any way to run these dml statements from tableX all
at one time as a batch rather than looping through TableX and executing them
one by one.
Thanks
RickyHere goes:
set nocount on;
use tempdb;
go
drop table t1, tablex;
go
create table t1(col1 int);
go
create table tablex(sql varchar(8000) not null);
insert into tablex values('insert into t1 values(1);');
insert into tablex values('insert into t1 values(2);');
insert into tablex values('insert into t1 values(3);');
go
exec master..xp_execresultset
N'select sql from tablex;',
N'tempdb'
go
select * from t1;
-- Output:
col1
--
1
2
3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
> Hello All,
> I have a table TableX with one column SQL varchar(8000). This column
> contains about 50 records (rows) like
> insert into tablename1 select datetime,...From Tbl1 Where...
> insert into tablename2 select datetime,...From Tbl2 Where...
> insert into tablename3 select datetime,...From Tbl3 Where...
> ...
> My question is, is there any way to run these dml statements from tableX
> all
> at one time as a batch rather than looping through TableX and executing
> them
> one by one.
> Thanks
> Ricky|||Thank You Itzik Ben-Gan.
"Brilliant as always...!!"
"Itzik Ben-Gan" wrote:
> Here goes:
> set nocount on;
> use tempdb;
> go
> drop table t1, tablex;
> go
> create table t1(col1 int);
> go
> create table tablex(sql varchar(8000) not null);
> insert into tablex values('insert into t1 values(1);');
> insert into tablex values('insert into t1 values(2);');
> insert into tablex values('insert into t1 values(3);');
> go
> exec master..xp_execresultset
> N'select sql from tablex;',
> N'tempdb'
> go
> select * from t1;
> -- Output:
> col1
> --
> 1
> 2
> 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
>
>|||Anybody know how to get the value of an output parameter from a proc
executed with xp_ExecuteResultSet?
For Example:
set nocount on
use tempdb
go
create table T (t text default '')
go
create proc P (@.T1 text, @.Message varchar(255) Out)
as
begin
select @.T1
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
DECLARE @.ErrorMessage nvarchar(4000)
SET @.SQL = 'exec p ''This is a test.'', @.ErrorMessage'
exec master..xp_execresultset @.SQL, N'tempdb'
select @.ErrorMessage
go
drop table T
drop proc P
Message posted via http://www.webservertalk.com|||--It would go something like this.
set nocount on
use tempdb
go
Create Table ##ErrorMessage (F1 [varchar] (8000))
go
create proc P (@.T1 nvarchar(4000), @.Message varchar(255) Out)
as
begin
select
T1 = @.T1,
Message = @.Message
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
SET @.SQL = 'EXECUTE(''' + char(13) + char(10) +
'DECLARE @.ErrorMessage nvarchar(4000) ' + char(13) + char(10) +
'exec p ''''This is a test.'''', @.ErrorMessage OUT' + char(13) + char(10)
+
'insert ##ErrorMessage values(@.ErrorMessage)' + char(13) + char(10) +
''')'
--exec sp_executesql @.SQL
SET @.SQL = 'SELECT ''' + Replace(@.SQL, '''', ''') + ''''
exec master..xp_execresultset @.SQL, N'tempdb'
select Message = F1 from ##ErrorMessage
go
drop table ##ErrorMessage
drop proc P
Message posted via http://www.webservertalk.com
订阅：
博文 (Atom)