BCP problem

Hi,

I have downloaded data from a sybase table uing BCP without any row terminator and column terminater specified. Before "BCP in" I have used ";" for column terminator and "/n" and row terminator. If I will load data one row by row it is working fine. But if I will load more than one row, 1st row goes fine.. second row 1st column gets truncated. Please help..

Thanks,it would be easier if you should us the syntax of the commands...and this is a sql server forum btw|||give native mode a try (-n). I believe that is the default of Sybase if you do not specify row and field terminators.

BCP out, extended chars when field is null (sometimes)

I want to "BCP out" some tables. The format is to be delimited with "\r\n"
row terminator.
Now for whatever reason, when I look at the exported text file some rows
have extended characters. The extended characters appear in fields that
happen to be null. The kicker is for the same column some rows with nulls
come through just fine, and a few rows (same column) come in with the
extended characters.
I am aware of the issue of there being no way to represent a null in the bcp
generated text data file. It is ok, if the source is a null and then it
comes back in as an empy string. I am ok with this.
In a sense, it sort of sounds like data corruption, but the field value is
null. The extended chars appear between the delimiters. So how does NULL
turn into extended characters.
Thanks.
I just ran into this too.
I created a little C# console guy that opens the text file and strips out
the null chars that were in my file.
you run it from a command line passing in the source file name and an output
file name.
FileScrubber.exe IckyFile.txt Cleanfile.txt
I've attached the cs file for the Class if you can use it great.
(i'm no C# Guru so please be kind with the review)
Greg Jackson
PDX, Oregon
begin 666 Class1.cs
M=7-I;F<@.4WES=&5M.PT*=7-I;F<@.4WES=&5M+DE/.PT*#0IN86UE<W!A8V4@.
M1FEL95-C<G5B8F5R#0I[#0H)+R\O(#QS=6UM87)Y/@.T*"2\O+R!3=6UM87)Y
M(&1E<V-R:7!T:6]N(&9O<B!#;&%S<S$N#0H)+R\O(#PO<W5M;6%R>3X-"@.EC
M;&%S<R!&:6QE4V-R=6)B97(-"@.E[#0H)"2\O+R \<W5M;6%R>3X-"@.D)+R\O
M(%1H92!M86EN(&5N=')Y('!O:6YT(&9O<B!T:&4@.87!P;&EC8 71I;VXN#0H)
M"2\O+R \+W-U;6UA<GD^#0H)"5M35$%4:')E861=#0H)"7-T871I8R!V;VED
M($UA:6XH<W1R:6YG6UT@.87)G<RD-"@.D)>PT*"0D)<W1R:6YG('-);G!U=$9I
M;&4@./2!A<F=S6S!=.PT*"0D)<W1R:6YG('-/=71P=71&:6QE(#T@.87)G<ULQ
M73L-"@.D)"6EN="!I;G1">71E.PT*"0D)8GET92!B=$)Y=&4[#0 H-"@.D)"49I
M;&53=')E86T@.<W1R;4]U='!U=" ](&YU;&P[#0H)"0E&:6QE4W1R96%M('-T
M<FU);G!U=" ](&YU;&P[#0H-"@.D)"71R>0T*"0D)>PT*"0D)"49I;&5);F9O
M(&]B:DEN1FEL92 ](&YE=R!&:6QE26YF;RAS26YP=71&:6QE*3L-"@.D)"0E&
M:6QE26YF;R!O8FI/=71&:6QE(#T@.;F5W($9I;&5);F9O*'-/=71P=71&:6QE
M*3L-"@.T*"0D)"7-T<FU);G!U=" ](&]B:DEN1FEL92Y/<&5N4F5A9"@.I.PT*
M"0D)"7-T<FU/=71P=70@./2!O8FI/=71&:6QE+D]P96Y7<FET92@.I.PT*#0H)
M"0D)9F]R*&EN="!I(#T@.,#MI/'-T<FU);G!U="Y,96YG=&@.[:2LK*0T*"0D)
M"7L-"@.D)"0D):6YT0GET92 ]('-T<FU);G!U="Y296%D0GET92@.I.PT*"0D)
M"0EB=$)Y=&4@./2 H8GET92EI;G1">71E.PT*#0H)"0D)"2\O:68@.:70@.:7,@.
M82!N=6QL(&-H87)A8W1E<BP@.=V4@.9V]T=&$@.<VMI<"!I="XN+BY'04H-"@.D)
M"0D):68H8G1">71E(#X@.,"D-"@.D)"0D)>PT*"0D)"0D)<W1R;4]U='!U="Y7
M<FET94)Y=&4H8G1">71E*3L-"@.D)"0D)?0T*"0D)"7T-"@.D)"7T-"@.D)"6-A
M=&-H*%-Y<W1E;2Y%>&-E<'1I;VX@.97AP*0T*"0D)>PT*"0D)"4-O;G-O;&4N
M5W)I=&5,:6YE*")%<G(@.(B K(&5X<"Y-97-S86=E*3L-"@.D)"7T-"@.D)"69I
M;F%L;'D-"@.D)"7L-"@.D)"0ES=')M3W5T<'5T+D-L;W-E*"D[#0H)"0D)<W1R
M;4EN<'5T+D-L;W-E*"D[#0H)"0E]#0H-"@.D)"4-O;G-O;&4N5W)I=&5,:6YE
M*")0<F]C97-S960@.26YP=70Z("(@.*R!S26YP=71&:6QE*3L-"@.D)"4-O;G-O
M;&4N5W)I=&5,:6YE*")'96YE<F%T960@.3W5T<'5T.B B("L@.<T]U='!U=$9I
M;&4I.PT*"0D)0V]N<V]L92Y7<FET94QI;F4H(B(I.PT*"0D)0V]N<V]L92Y7
D<FET94QI;F4H(D=I9&1Y(%5P(2(I.PT*"0E]#0H)?0T*?0T*
`
end

bcp or oledb/ado?

I have very big tables. And I need to some computation for each row. Which
one is the best/fast way to implement it?
1. bcp the tables to text file. C++ code parse the csv file row by row.
Write results to text files. Then bulk insert back to Sql server.
2. C++ code use oledb/ado to get the rows, write results to text file and
bulk insert back.
3. C++ code use oledb/ado for both getting and writting back operations.What type of computation do you need to do?
Why not just do it within SQL using T-SQL?
create table #foo (col1 int, col2 int, col3 decimal(5,2))
insert into #foo (col1, col2, col3) values (1,2,3)
insert into #foo (col1, col2, col3) values (10,20,30)
insert into #foo (col1, col2, col3) values (5,6, NULL)
go
select * from #foo
update #foo set col3 = col1 * col2
select * from #foo
select * from #foo
update #foo set col3 = col1 * col2
select * from #foo
update #foo set col3 = col3 / 4
select * from #foo
update #foo set col3 = (col3 / 2) / 1
select * from #foo
go
drop table #foo
If your tables are huge and you don't have enough disk space you might fill
up the transaction log. If that is a concern you could update batches of
data (based on your primary key).
Keith Kratochvil
"nick" <nick@.discussions.microsoft.com> wrote in message
news:89811B8B-D867-4163-A4F9-132EB4BC6D69@.microsoft.com...
>I have very big tables. And I need to some computation for each row. Which
> one is the best/fast way to implement it?
> 1. bcp the tables to text file. C++ code parse the csv file row by row.
> Write results to text files. Then bulk insert back to Sql server.
> 2. C++ code use oledb/ado to get the rows, write results to text file and
> bulk insert back.
> 3. C++ code use oledb/ado for both getting and writting back operations.
>|||The computation is complex math, matrix mutilple, etc. Very difficult to
write in TSQL. And the code is provided from other group and I cannot modify
it too. I will definitely rewrite it in TSQL if it's possbile.
"Keith Kratochvil" wrote:

> What type of computation do you need to do?
> Why not just do it within SQL using T-SQL?
> create table #foo (col1 int, col2 int, col3 decimal(5,2))
> insert into #foo (col1, col2, col3) values (1,2,3)
> insert into #foo (col1, col2, col3) values (10,20,30)
> insert into #foo (col1, col2, col3) values (5,6, NULL)
> go
> select * from #foo
> update #foo set col3 = col1 * col2
> select * from #foo
> select * from #foo
> update #foo set col3 = col1 * col2
> select * from #foo
>
> update #foo set col3 = col3 / 4
> select * from #foo
> update #foo set col3 = (col3 / 2) / 1
> select * from #foo
> go
> drop table #foo
>
> If your tables are huge and you don't have enough disk space you might fil
l
> up the transaction log. If that is a concern you could update batches of
> data (based on your primary key).
>
> --
> Keith Kratochvil
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:89811B8B-D867-4163-A4F9-132EB4BC6D69@.microsoft.com...
>
>|||you will have to define what "very big tables" means, but if they are over 1
0
million rows, then exporting them to a text file and running a well written
C
object against it will probably be faster. You should make sure you make
only one pass through the data making your computations, then use sql's bulk
insert to get the finished product back into the db.
"nick" wrote:

> I have very big tables. And I need to some computation for each row. Which
> one is the best/fast way to implement it?
> 1. bcp the tables to text file. C++ code parse the csv file row by row.
> Write results to text files. Then bulk insert back to Sql server.
> 2. C++ code use oledb/ado to get the rows, write results to text file and
> bulk insert back.
> 3. C++ code use oledb/ado for both getting and writting back operations.
>|||the big tables vary from several thousand rows to several tens million rows.
bcp should be faster then client side cursor, however it involved more I/O i
n
the whole process...
"Carl Henthorn" wrote:
> you will have to define what "very big tables" means, but if they are over
10
> million rows, then exporting them to a text file and running a well writte
n C
> object against it will probably be faster. You should make sure you make
> only one pass through the data making your computations, then use sql's bu
lk
> insert to get the finished product back into the db.
> "nick" wrote:
>|||nick (nick@.discussions.microsoft.com) writes:
> I have very big tables. And I need to some computation for each row. Which
> one is the best/fast way to implement it?
> 1. bcp the tables to text file. C++ code parse the csv file row by row.
> Write results to text files. Then bulk insert back to Sql server.
> 2. C++ code use oledb/ado to get the rows, write results to text file and
> bulk insert back.
> 3. C++ code use oledb/ado for both getting and writting back operations.
Now, C++ programming is not my main business, but my gut feelings says
that 1 is not a good solution. It takes time to read a file as well.
So to get the data into the client program, I would get one huge rowset,
or possibly batchwise. Note: not a server-side cursor, but client side.
For writing data back, I would use either bulk copy or send down an
XML document that I unpack in SQL Server with OPENXML. I would proably
not insert into the target table - I would prefer to update it. (Unless
the computations also removes and add rows.) To this OPENXML is maybe a
little simpler, but you can easily bulk copy into a staging table
you update from.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, bcp seems involve more I/O. But will it still be faster then client-sid
e
cursor? And I am trying to avoid client side oledb programming, ado is said
easier but slower. And I cannot use ado.net since it's not clr program.
"Erland Sommarskog" wrote:

> nick (nick@.discussions.microsoft.com) writes:
> Now, C++ programming is not my main business, but my gut feelings says
> that 1 is not a good solution. It takes time to read a file as well.
> So to get the data into the client program, I would get one huge rowset,
> or possibly batchwise. Note: not a server-side cursor, but client side.
> For writing data back, I would use either bulk copy or send down an
> XML document that I unpack in SQL Server with OPENXML. I would proably
> not insert into the target table - I would prefer to update it. (Unless
> the computations also removes and add rows.) To this OPENXML is maybe a
> little simpler, but you can easily bulk copy into a staging table
> you update from.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||nick (nick@.discussions.microsoft.com) writes:
> Yes, bcp seems involve more I/O. But will it still be faster then
> client-side cursor? And I am trying to avoid client side oledb
> programming, ado is said easier but slower. And I cannot use ado.net
> since it's not clr program.
Admittedly, there is some overhead in a recordset/rowset. Maybe bulk
out to file, and then read the file into memory in one swoop?
The only way to find out is to benchmark - if you have the time.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP import error

I BCP out a table and then import back. While BCP IN, i get the following error:
#@. Row 303, Column 5: Invalid character value for cast specification @.#
#@. Row 296, Column 4: String data, right truncation @.#
Help plz!I think the data you try to insert into the table does not match the the ddl of the table itself. Are there any strange characters on row 303? What does row 303 look like anyway? The bcp-command you execute: does it consider strange characters and how does it map the file to the table? What's the layout of the table?|||Hi, thanx.
I don't suspect if there's any column binding problem, coz
I create a txt file from the same table, then truncate that table and import the data back from this text file.
The problem is multiple commas ",,," in the note-content column text.
Here's an example:

CREATE TABLE mydbo (id int, notedate datetime, note_content varchar(900), note_user char(3))
go
INSERT MYTAB
SELECT 1 , '2003-12-24 00:00:00' , 'AS PERINSTRUCTION ENTRD SC#@.12/16/03...71', 'SF2'
UNION ALL
SELECT 2 , '2004-02-07 00:00:00', 'CHECKED INF SCA@.#02/06/04.............17', 'SF2'
UNION ALL
SELECT 3 , '2004-03-26 00:00:00', 'GO TO, #, as per data .', 'SF2'

When i BCP out this table and then BCP into same table, i get the error for line 3. (right trucation)
How do i handle this, i have a CSV file that i have to import into a table, but data in single field contains multiple commas.

Howdy!|||Would it be possible for you to choose a different field seperator such as ; or [tab] ?

bcp has primary key contraint error

When running a bcp to insert data into a table, I get a violation of primary key contraint. Is there any way to tell what row is causing the problem? The error I'm getting is:
SQLState = 23000, NativeError = 2627
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'table_name'. Cannot insert duplicate key in object 'table_name'.
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
BCP copy in failed
Hi
Try the -e option. From Books online:
-e err_file
Specifies the full path of an error file used to store any rows bcp is
unable to transfer from the file to the database. Error messages from bcp go
to the user's workstation. If this option is not used, an error file is not
created.
John
"erika" <anonymous@.discussions.microsoft.com> wrote in message
news:9888AE94-88DC-499B-8EA9-E61C2A72F546@.microsoft.com...
> When running a bcp to insert data into a table, I get a violation of
primary key contraint. Is there any way to tell what row is causing the
problem? The error I'm getting is:
> SQLState = 23000, NativeError = 2627
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of
PRIMARY KEY constraint 'table_name'. Cannot insert duplicate key in object
'table_name'.
> SQLState = 01000, NativeError = 3621
> Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been terminated.
> BCP copy in failed
>

bcp has primary key contraint error

When running a bcp to insert data into a table, I get a violation of primary
key contraint. Is there any way to tell what row is causing the problem? Th
e error I'm getting is:
SQLState = 23000, NativeError = 2627
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation
of PRIMARY KEY constraint 'table_name'. Cannot insert duplicate key in obje
ct 'table_name'.
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The sta
tement has been terminated.
BCP copy in failedHi
Try the -e option. From Books online:
-e err_file
Specifies the full path of an error file used to store any rows bcp is
unable to transfer from the file to the database. Error messages from bcp go
to the user's workstation. If this option is not used, an error file is not
created.
John
"erika" <anonymous@.discussions.microsoft.com> wrote in message
news:9888AE94-88DC-499B-8EA9-E61C2A72F546@.microsoft.com...
> When running a bcp to insert data into a table, I get a violation of
primary key contraint. Is there any way to tell what row is causing the
problem? The error I'm getting is:
> SQLState = 23000, NativeError = 2627
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of[/v
bcol]
PRIMARY KEY constraint 'table_name'. Cannot insert duplicate key in object
'table_name'.[vbcol=seagreen]
> SQLState = 01000, NativeError = 3621
> Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement
has
been terminated.
> BCP copy in failed
>

BCP Export rowcount

Any idea why, when my BCP export completed that it stated it exported
2,698,467 rows, but when I do a row count in the table, the row count is
2,665,081?
The table is being used exclusively by myself, as currently I am the only
user that has permissions to it.
The error log from the BCP export reveals no errors.
This is the syntax of my BCP export:
bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out "e:\backups\
archive.bcp"
-n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[servername]
Message posted via http://www.sqlmonster.com
did you use "select count(*)" ?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d5932af1b4344880a6c310e39590d9f5@.SQLMonster.c om...
> Any idea why, when my BCP export completed that it stated it exported
> 2,698,467 rows, but when I do a row count in the table, the row count is
> 2,665,081?
> The table is being used exclusively by myself, as currently I am the only
> user that has permissions to it.
> The error log from the BCP export reveals no errors.
> This is the syntax of my BCP export:
> bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out
> "e:\backups\
> archive.bcp"
> -n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[servername]
> --
> Message posted via http://www.sqlmonster.com

BCP Export rowcount

Any idea why, when my BCP export completed that it stated it exported
2,698,467 rows, but when I do a row count in the table, the row count is
2,665,081?
The table is being used exclusively by myself, as currently I am the only
user that has permissions to it.
The error log from the BCP export reveals no errors.
This is the syntax of my BCP export:
bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out "e:\backups\
archive.bcp"
-n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[servername]
--
Message posted via http://www.sqlmonster.comdid you use "select count(*)" ?
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d5932af1b4344880a6c310e39590d9f5@.SQLMonster.com...
> Any idea why, when my BCP export completed that it stated it exported
> 2,698,467 rows, but when I do a row count in the table, the row count is
> 2,665,081?
> The table is being used exclusively by myself, as currently I am the only
> user that has permissions to it.
> The error log from the BCP export reveals no errors.
> This is the syntax of my BCP export:
> bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out
> "e:\backups\
> archive.bcp"
> -n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[servername]
> --
> Message posted via http://www.sqlmonster.com

BCP Export rowcount

Any idea why, when my BCP export completed that it stated it exported
2,698,467 rows, but when I do a row count in the table, the row count is
2,665,081?
The table is being used exclusively by myself, as currently I am the only
user that has permissions to it.
The error log from the BCP export reveals no errors.
This is the syntax of my BCP export:
bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out "e:\backups\
archive.bcp"
-n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[serv
ername]
Message posted via http://www.droptable.comdid you use "select count(*)" ?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:d5932af1b4344880a6c310e39590d9f5@.SQ
droptable.com...
> Any idea why, when my BCP export completed that it stated it exported
> 2,698,467 rows, but when I do a row count in the table, the row count is
> 2,665,081?
> The table is being used exclusively by myself, as currently I am the only
> user that has permissions to it.
> The error log from the BCP export reveals no errors.
> This is the syntax of my BCP export:
> bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out
> "e:\backups\
> archive.bcp"
> -n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[se
rvername]
> --
> Message posted via http://www.droptable.com

bcp command to give colums

Can someonte tell me that if i
bcp bda..mytable out c:\discounts.xls -c -p , how can I put the first row as my column names since I get only dataIt won't...but there are other ways aroung it..you can use query out and supply a union like

SELECT 'col1','col2','col3'...
UNION ALL
SELECT Col1,col2,col3 FROM myTable99

Just need to make sure you're dfatatypes are converted to varchar

What about DTS to an EXCEL, or csv?|||Thanks very much it worked like magi

Originally posted by Brett Kaiser
It won't...but there are other ways aroung it..you can use query out and supply a union like

SELECT 'col1','col2','col3'...
UNION ALL
SELECT Col1,col2,col3 FROM myTable99

Just need to make sure you're dfatatypes are converted to varchar

What about DTS to an EXCEL, or csv?

bcp a file into text column

Hi,
I have a table with 1 text column.
I need to bcp a .txt file spread over 6/7 lines into this
table.It should get inserted only in one row and not 6/7
rows.I am using sql2000.
Thanks
arOne way to do it would be:
1-. Bulk Insert the text file to a temp table or loading
table.
E.g.
CREATE TABLE TextTable (
Text varchar (8000)
)
bulk insert texttable
from 'Path of your text file'
2-. Use some procedure like the following to concatenate
all the data in one variable:
-- BEGIN SCRIPT --
declare @.text varchar(7000)
declare @.row varchar(7000)
declare cur cursor for
select Text from texttable
where text is not null
set @.text = ''
open cur
fetch next from cur into @.row
while @.@.fetch_status = 0
begin
set @.text = @.text+SUBSTRING(@.row, 1, LEN(@.row)+1)
fetch next from cur into @.row
end
close cur
deallocate cur
print @.text
-- END SCRIPT --
3-. Insert/update the value in @.text into the destiantion
table.
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>Hi,
>I have a table with 1 text column.
>I need to bcp a .txt file spread over 6/7 lines into this
>table.It should get inserted only in one row and not 6/7
>rows.I am using sql2000.
>Thanks
>ar
>.
>|||AR,
Add a unique row delimiter at the end of the text file like. Make sure this
delimiter doesn't exists in your text file. For example you can use a
delimiter like "*|*"
Also make sure that there is no other character exists in the file after the
delimiter, including newline, tab etc.
CREATE TABLE TextTable (
Text varchar (8000)
)
BULK INSERT TextTable
FROM 'C:\text.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '*|*'
)
select * from TextTable
HTH,
Praveen Maddali,
MCSD, MCDBA
"AR" <ambewadkarrakesh@.johndeere.com> wrote in message
news:038601c368e5$85341b90$a301280a@.phx.gbl...
> Hi,
> I have a table with 1 text column.
> I need to bcp a .txt file spread over 6/7 lines into this
> table.It should get inserted only in one row and not 6/7
> rows.I am using sql2000.
> Thanks
> ar

Batch Insert

I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
Thanx
Please post the DDL of both tables plus the error message.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
Thanx
|||Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
wh_idvarcharno10
order_numbervarcharno30
store_order_numbervarcharno40
type_idintno4
customer_idcharno15
cust_po_numbervarcharno30
customer_namevarcharno100
customer_phonevarcharno30
customer_faxvarcharno30
customer_emailvarcharno50
departmentcharno10
load_idvarcharno30
load_seqintno4
bol_numbercharno10
pro_numbervarcharno20
master_bol_numbercharno10
carriervarcharno30
carrier_scacvarcharno4
freight_termsvarcharno10
rushcharno5
priorityvarcharno3
order_datedatetimeno8
arrive_datedatetimeno8
actual_arrival_datedatetimeno8
date_pickeddatetimeno8
date_expecteddatetimeno8
promised_datedatetimeno8
weightfloatno8
cubic_volumefloatno8
containersintno4
backordercharno1
pre_paidcharno10
cod_amountfloatno8
insurance_amountfloatno8
pip_amountfloatno8
freight_costfloatno8
regionvarcharno5
bill_to_codecharno15
bill_to_namevarcharno30
bill_to_addr1varcharno30
bill_to_addr2varcharno30
bill_to_addr3varcharno30
bill_to_cityvarcharno30
bill_to_statevarcharno3
bill_to_zipvarcharno12
bill_to_country_codecharno5
bill_to_country_namevarcharno30
bill_to_phonevarcharno30
ship_to_codecharno15
ship_to_namevarcharno30
ship_to_addr1varcharno30
ship_to_addr2varcharno30
ship_to_addr3varcharno30
ship_to_cityvarcharno30
ship_to_statevarcharno3
ship_to_zipvarcharno12
ship_to_country_codecharno5
ship_to_country_namevarcharno30
ship_to_phonevarcharno30
delivery_namevarcharno30
delivery_addr1varcharno30
delivery_addr2varcharno30
delivery_addr3varcharno30
delivery_cityvarcharno30
delivery_statevarcharno3
delivery_zipvarcharno12
delivery_country_codecharno5
delivery_country_namevarcharno30
delivery_phonevarcharno30
bill_frght_to_codecharno15
bill_frght_to_namevarcharno30
bill_frght_to_addr1varcharno30
bill_frght_to_addr2varcharno30
bill_frght_to_addr3varcharno30
bill_frght_to_cityvarcharno30
bill_frght_to_statevarcharno3
bill_frght_to_zipvarcharno12
bill_frght_to_country_codecharno5
bill_frght_to_country_namevarcharno30
bill_frght_to_phonevarcharno30
return_to_codecharno30
return_to_namevarcharno30
return_to_addr1varcharno30
return_to_addr2varcharno30
return_to_addr3varcharno30
return_to_cityvarcharno30
return_to_statevarcharno3
return_to_zipvarcharno12
return_to_country_codecharno5
return_to_country_namevarcharno30
return_to_phonevarcharno30
rma_numbervarcharno40
rma_expiration_datedatetimeno8
carton_labelvarcharno10
ver_flagcharno4
full_palletsintno4
haz_flagcharno10
order_wgtfloatno8
statusvarcharno20
zonevarcharno10
drop_shipcharno1
lock_flagvarcharno10
partial_order_flagcharno1
earliest_ship_datedatetimeno8
latest_ship_datedatetimeno8
actual_ship_datedatetimeno8
earliest_delivery_datedatetimeno8
latest_delivery_datedatetimeno8
actual_delivery_datedatetimeno8
routevarcharno30
order_amountfloatno8
pick_typecharno1
invoiced_amountfloatno8
t_pick_detail definition
pick_idintno4
order_numbervarcharno20
line_numbervarcharno5
typecharno2
uomvarcharno10
work_q_idvarcharno30
work_typevarcharno2
label_numbervarcharno22
statusvarcharno10
item_numbervarcharno30
lot_numbervarcharno15
serial_numbervarcharno30
unplanned_quantityfloatno8
planned_quantityfloatno8
picked_quantityfloatno8
staged_quantityfloatno8
loaded_quantityfloatno8
pick_locationvarcharno10
picking_flowvarcharno10
staging_locationvarcharno10
zonevarcharno20
wave_idvarcharno20
load_idvarcharno30
load_sequenceintno4
stop_idvarcharno20
container_idvarcharno22
pick_categoryvarcharno10
user_assignedvarcharno10
bulk_pick_flagcharno1
stacking_sequenceintno4
pick_areavarcharno10
wh_idvarcharno10
requested_quantityfloatno8
request_returned_qtyfloatno8

>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>
|||1) You left out the definition for t_order_detail.
2) You did not give actual DDL - i.e. CREATE TABLE statements.
3) You did not post the error message.
4) Of the tables you did present, you have mismatched datatypes, which
may be the source of your problem.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
wh_id varchar no 10
order_number varchar no 30
store_order_number varchar no 40
type_id int no 4
customer_id char no 15
cust_po_number varchar no 30
customer_name varchar no 100
customer_phone varchar no 30
customer_fax varchar no 30
customer_email varchar no 50
department char no 10
load_id varchar no 30
load_seq int no 4
bol_number char no 10
pro_number varchar no 20
master_bol_number char no 10
carrier varchar no 30
carrier_scac varchar no 4
freight_terms varchar no 10
rush char no 5
priority varchar no 3
order_date datetime no 8
arrive_date datetime no 8
actual_arrival_date datetime no 8
date_picked datetime no 8
date_expected datetime no 8
promised_date datetime no 8
weight float no 8
cubic_volume float no 8
containers int no 4
backorder char no 1
pre_paid char no 10
cod_amount float no 8
insurance_amount float no 8
pip_amount float no 8
freight_cost float no 8
region varchar no 5
bill_to_code char no 15
bill_to_name varchar no 30
bill_to_addr1 varchar no 30
bill_to_addr2 varchar no 30
bill_to_addr3 varchar no 30
bill_to_city varchar no 30
bill_to_state varchar no 3
bill_to_zip varchar no 12
bill_to_country_code char no 5
bill_to_country_name varchar no 30
bill_to_phone varchar no 30
ship_to_code char no 15
ship_to_name varchar no 30
ship_to_addr1 varchar no 30
ship_to_addr2 varchar no 30
ship_to_addr3 varchar no 30
ship_to_city varchar no 30
ship_to_state varchar no 3
ship_to_zip varchar no 12
ship_to_country_code char no 5
ship_to_country_name varchar no 30
ship_to_phone varchar no 30
delivery_name varchar no 30
delivery_addr1 varchar no 30
delivery_addr2 varchar no 30
delivery_addr3 varchar no 30
delivery_city varchar no 30
delivery_state varchar no 3
delivery_zip varchar no 12
delivery_country_code char no 5
delivery_country_name varchar no 30
delivery_phone varchar no 30
bill_frght_to_code char no 15
bill_frght_to_name varchar no 30
bill_frght_to_addr1 varchar no 30
bill_frght_to_addr2 varchar no 30
bill_frght_to_addr3 varchar no 30
bill_frght_to_city varchar no 30
bill_frght_to_state varchar no 3
bill_frght_to_zip varchar no 12
bill_frght_to_country_code char no 5
bill_frght_to_country_name varchar no 30
bill_frght_to_phone varchar no 30
return_to_code char no 30
return_to_name varchar no 30
return_to_addr1 varchar no 30
return_to_addr2 varchar no 30
return_to_addr3 varchar no 30
return_to_city varchar no 30
return_to_state varchar no 3
return_to_zip varchar no 12
return_to_country_code char no 5
return_to_country_name varchar no 30
return_to_phone varchar no 30
rma_number varchar no 40
rma_expiration_date datetime no 8
carton_label varchar no 10
ver_flag char no 4
full_pallets int no 4
haz_flag char no 10
order_wgt float no 8
status varchar no 20
zone varchar no 10
drop_ship char no 1
lock_flag varchar no 10
partial_order_flag char no 1
earliest_ship_date datetime no 8
latest_ship_date datetime no 8
actual_ship_date datetime no 8
earliest_delivery_date datetime no 8
latest_delivery_date datetime no 8
actual_delivery_date datetime no 8
route varchar no 30
order_amount float no 8
pick_type char no 1
invoiced_amount float no 8
t_pick_detail definition
pick_id int no 4
order_number varchar no 20
line_number varchar no 5
type char no 2
uom varchar no 10
work_q_id varchar no 30
work_type varchar no 2
label_number varchar no 22
status varchar no 10
item_number varchar no 30
lot_number varchar no 15
serial_number varchar no 30
unplanned_quantity float no 8
planned_quantity float no 8
picked_quantity float no 8
staged_quantity float no 8
loaded_quantity float no 8
pick_location varchar no 10
picking_flow varchar no 10
staging_location varchar no 10
zone varchar no 20
wave_id varchar no 20
load_id varchar no 30
load_sequence int no 4
stop_id varchar no 20
container_id varchar no 22
pick_category varchar no 10
user_assigned varchar no 10
bulk_pick_flag char no 1
stacking_sequence int no 4
pick_area varchar no 10
wh_id varchar no 10
requested_quantity float no 8
request_returned_qty float no 8

>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>
|||Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.

>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>message.
-
>--
>message
>.
>
|||Reconsider your approach. It will take _much_ longer. It will be much
faster if you simply post the DDL and the error message.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<anonymous@.discussions.microsoft.com> wrote in message
news:0a5501c4e39e$f909e650$a401280a@.phx.gbl...
Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.

>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>message.
-
>--
>message
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Thanks for looking at it , Tom
> I have suggested the folks to use a loop method to insert
> rather than the current method.
> Thanks again.
>
Are you the OP? If so, are you suggesting that a looping construct
inserting a row at a time will be faster than a single insert statement?
Before commiting to that design, some testing is in order.
David Gugick
Imceda Software
www.imceda.com

Batch Insert

I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
ThanxPlease post the DDL of both tables plus the error message.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
Thanx|||Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
--
wh_id varchar no 10
order_number varchar no 30
store_order_number varchar no 40
type_id int no 4
customer_id char no 15
cust_po_number varchar no 30
customer_name varchar no 100
customer_phone varchar no 30
customer_fax varchar no 30
customer_email varchar no 50
department char no 10
load_id varchar no 30
load_seq int no 4
bol_number char no 10
pro_number varchar no 20
master_bol_number char no 10
carrier varchar no 30
carrier_scac varchar no 4
freight_terms varchar no 10
rush char no 5
priority varchar no 3
order_date datetime no 8
arrive_date datetime no 8
actual_arrival_date datetime no 8
date_picked datetime no 8
date_expected datetime no 8
promised_date datetime no 8
weight float no 8
cubic_volume float no 8
containers int no 4
backorder char no 1
pre_paid char no 10
cod_amount float no 8
insurance_amount float no 8
pip_amount float no 8
freight_cost float no 8
region varchar no 5
bill_to_code char no 15
bill_to_name varchar no 30
bill_to_addr1 varchar no 30
bill_to_addr2 varchar no 30
bill_to_addr3 varchar no 30
bill_to_city varchar no 30
bill_to_state varchar no 3
bill_to_zip varchar no 12
bill_to_country_code char no 5
bill_to_country_name varchar no 30
bill_to_phone varchar no 30
ship_to_code char no 15
ship_to_name varchar no 30
ship_to_addr1 varchar no 30
ship_to_addr2 varchar no 30
ship_to_addr3 varchar no 30
ship_to_city varchar no 30
ship_to_state varchar no 3
ship_to_zip varchar no 12
ship_to_country_code char no 5
ship_to_country_name varchar no 30
ship_to_phone varchar no 30
delivery_name varchar no 30
delivery_addr1 varchar no 30
delivery_addr2 varchar no 30
delivery_addr3 varchar no 30
delivery_city varchar no 30
delivery_state varchar no 3
delivery_zip varchar no 12
delivery_country_code char no 5
delivery_country_name varchar no 30
delivery_phone varchar no 30
bill_frght_to_code char no 15
bill_frght_to_name varchar no 30
bill_frght_to_addr1 varchar no 30
bill_frght_to_addr2 varchar no 30
bill_frght_to_addr3 varchar no 30
bill_frght_to_city varchar no 30
bill_frght_to_state varchar no 3
bill_frght_to_zip varchar no 12
bill_frght_to_country_code char no 5
bill_frght_to_country_name varchar no 30
bill_frght_to_phone varchar no 30
return_to_code char no 30
return_to_name varchar no 30
return_to_addr1 varchar no 30
return_to_addr2 varchar no 30
return_to_addr3 varchar no 30
return_to_city varchar no 30
return_to_state varchar no 3
return_to_zip varchar no 12
return_to_country_code char no 5
return_to_country_name varchar no 30
return_to_phone varchar no 30
rma_number varchar no 40
rma_expiration_date datetime no 8
carton_label varchar no 10
ver_flag char no 4
full_pallets int no 4
haz_flag char no 10
order_wgt float no 8
status varchar no 20
zone varchar no 10
drop_ship char no 1
lock_flag varchar no 10
partial_order_flag char no 1
earliest_ship_date datetime no 8
latest_ship_date datetime no 8
actual_ship_date datetime no 8
earliest_delivery_date datetime no 8
latest_delivery_date datetime no 8
actual_delivery_date datetime no 8
route varchar no 30
order_amount float no 8
pick_type char no 1
invoiced_amount float no 8
t_pick_detail definition
--
pick_id int no 4
order_number varchar no 20
line_number varchar no 5
type char no 2
uom varchar no 10
work_q_id varchar no 30
work_type varchar no 2
label_number varchar no 22
status varchar no 10
item_number varchar no 30
lot_number varchar no 15
serial_number varchar no 30
unplanned_quantity float no 8
planned_quantity float no 8
picked_quantity float no 8
staged_quantity float no 8
loaded_quantity float no 8
pick_location varchar no 10
picking_flow varchar no 10
staging_location varchar no 10
zone varchar no 20
wave_id varchar no 20
load_id varchar no 30
load_sequence int no 4
stop_id varchar no 20
container_id varchar no 22
pick_category varchar no 10
user_assigned varchar no 10
bulk_pick_flag char no 1
stacking_sequence int no 4
pick_area varchar no 10
wh_id varchar no 10
requested_quantity float no 8
request_returned_qty float no 8
>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>|||1) You left out the definition for t_order_detail.
2) You did not give actual DDL - i.e. CREATE TABLE statements.
3) You did not post the error message.
4) Of the tables you did present, you have mismatched datatypes, which
may be the source of your problem.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
--
wh_id varchar no 10
order_number varchar no 30
store_order_number varchar no 40
type_id int no 4
customer_id char no 15
cust_po_number varchar no 30
customer_name varchar no 100
customer_phone varchar no 30
customer_fax varchar no 30
customer_email varchar no 50
department char no 10
load_id varchar no 30
load_seq int no 4
bol_number char no 10
pro_number varchar no 20
master_bol_number char no 10
carrier varchar no 30
carrier_scac varchar no 4
freight_terms varchar no 10
rush char no 5
priority varchar no 3
order_date datetime no 8
arrive_date datetime no 8
actual_arrival_date datetime no 8
date_picked datetime no 8
date_expected datetime no 8
promised_date datetime no 8
weight float no 8
cubic_volume float no 8
containers int no 4
backorder char no 1
pre_paid char no 10
cod_amount float no 8
insurance_amount float no 8
pip_amount float no 8
freight_cost float no 8
region varchar no 5
bill_to_code char no 15
bill_to_name varchar no 30
bill_to_addr1 varchar no 30
bill_to_addr2 varchar no 30
bill_to_addr3 varchar no 30
bill_to_city varchar no 30
bill_to_state varchar no 3
bill_to_zip varchar no 12
bill_to_country_code char no 5
bill_to_country_name varchar no 30
bill_to_phone varchar no 30
ship_to_code char no 15
ship_to_name varchar no 30
ship_to_addr1 varchar no 30
ship_to_addr2 varchar no 30
ship_to_addr3 varchar no 30
ship_to_city varchar no 30
ship_to_state varchar no 3
ship_to_zip varchar no 12
ship_to_country_code char no 5
ship_to_country_name varchar no 30
ship_to_phone varchar no 30
delivery_name varchar no 30
delivery_addr1 varchar no 30
delivery_addr2 varchar no 30
delivery_addr3 varchar no 30
delivery_city varchar no 30
delivery_state varchar no 3
delivery_zip varchar no 12
delivery_country_code char no 5
delivery_country_name varchar no 30
delivery_phone varchar no 30
bill_frght_to_code char no 15
bill_frght_to_name varchar no 30
bill_frght_to_addr1 varchar no 30
bill_frght_to_addr2 varchar no 30
bill_frght_to_addr3 varchar no 30
bill_frght_to_city varchar no 30
bill_frght_to_state varchar no 3
bill_frght_to_zip varchar no 12
bill_frght_to_country_code char no 5
bill_frght_to_country_name varchar no 30
bill_frght_to_phone varchar no 30
return_to_code char no 30
return_to_name varchar no 30
return_to_addr1 varchar no 30
return_to_addr2 varchar no 30
return_to_addr3 varchar no 30
return_to_city varchar no 30
return_to_state varchar no 3
return_to_zip varchar no 12
return_to_country_code char no 5
return_to_country_name varchar no 30
return_to_phone varchar no 30
rma_number varchar no 40
rma_expiration_date datetime no 8
carton_label varchar no 10
ver_flag char no 4
full_pallets int no 4
haz_flag char no 10
order_wgt float no 8
status varchar no 20
zone varchar no 10
drop_ship char no 1
lock_flag varchar no 10
partial_order_flag char no 1
earliest_ship_date datetime no 8
latest_ship_date datetime no 8
actual_ship_date datetime no 8
earliest_delivery_date datetime no 8
latest_delivery_date datetime no 8
actual_delivery_date datetime no 8
route varchar no 30
order_amount float no 8
pick_type char no 1
invoiced_amount float no 8
t_pick_detail definition
--
pick_id int no 4
order_number varchar no 20
line_number varchar no 5
type char no 2
uom varchar no 10
work_q_id varchar no 30
work_type varchar no 2
label_number varchar no 22
status varchar no 10
item_number varchar no 30
lot_number varchar no 15
serial_number varchar no 30
unplanned_quantity float no 8
planned_quantity float no 8
picked_quantity float no 8
staged_quantity float no 8
loaded_quantity float no 8
pick_location varchar no 10
picking_flow varchar no 10
staging_location varchar no 10
zone varchar no 20
wave_id varchar no 20
load_id varchar no 30
load_sequence int no 4
stop_id varchar no 20
container_id varchar no 22
pick_category varchar no 10
user_assigned varchar no 10
bulk_pick_flag char no 1
stacking_sequence int no 4
pick_area varchar no 10
wh_id varchar no 10
requested_quantity float no 8
request_returned_qty float no 8
>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>|||Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.
>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>>--Original Message--
>>Please post the DDL of both tables plus the error
>message.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>>I am using a following statement in a sproc
>>insert into destination_table
>>select col1,col2,col3,col4 from source_table
>>For a certain row in the source_table the above insert
>>fails.
>>But its out of some 350 total records in source_table
>>How can I find which specific row/s is failing the
>>insert ?
>>Thanx
>>.
>.
>|||Reconsider your approach. It will take _much_ longer. It will be much
faster if you simply post the DDL and the error message.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<anonymous@.discussions.microsoft.com> wrote in message
news:0a5501c4e39e$f909e650$a401280a@.phx.gbl...
Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.
>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>>--Original Message--
>>Please post the DDL of both tables plus the error
>message.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>>I am using a following statement in a sproc
>>insert into destination_table
>>select col1,col2,col3,col4 from source_table
>>For a certain row in the source_table the above insert
>>fails.
>>But its out of some 350 total records in source_table
>>How can I find which specific row/s is failing the
>>insert ?
>>Thanx
>>.
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Thanks for looking at it , Tom
> I have suggested the folks to use a loop method to insert
> rather than the current method.
> Thanks again.
>
Are you the OP? If so, are you suggesting that a looping construct
inserting a row at a time will be faster than a single insert statement?
Before commiting to that design, some testing is in order.
--
David Gugick
Imceda Software
www.imceda.com

Basic SSIS Question

Lets say I want to create several flat files - one file for each row returned by a sql query. Source data resides in SQL.

In short, my problem is a "reverse" of the many examples out there where data originates from multiple flat files into a SQL database. I want to go from SQL to multiple flat files.

I suppose this means I need a dynamic flat file connection string ... but I'm really stuck. Please help.

Thanks in advance.

Do you want to create many files at the same time? Then create a Flat File Connection Manager for each file.

If you only want to create one file at a time, but use a different file name so as not to overwrite the existing file, like a daily sales file or something like that? The look at the expressions property on the Flat File Connection Manager. You can use a variable to alter the connection string property.

Try Google: "Using Property Expressions in Packages"

|||

Like TGnat says, you'll need to use a Flat File Destination Adapter in concert with a Flat File Connection Manager. There's loads of information out there if you google it!

-Jamie

|||

Thanks, I'll check it out.

John

Basic SQL Connection

Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row.

When it gets to the third line the page crashes with an error.

SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True");

SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection);

SqlDataReader reader = cmd.ExecuteReader();

is there somewhere i need to put in my username or password? or is this code just wrong

Many thanks burnside.

-- Edited by longhorn2005

What is your error message?|||

Try the below connection string

SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;Integrated Security=True");

And if your database require Username and password check the below link

Building Connection String C#

HC

|||

Thanks for the replys,

The page seems to crash when i uncomment out the third line of sql code "// SqlDataReader rdr = cmd.ExecuteReader();"
"System.Data.SqlClient" %> and i get this slightly unhelpful error message @.http://s152182516.websitehome.co.uk/test/default2.aspx

<%

@.PageLanguage="C#" %>

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SqlClient" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">protectedvoid Page_Load(object sender,EventArgs e)

{

// Define database connectionSqlConnection conn =newSqlConnection("uid=xxxxx;password=xxxxx;Server=mssql07.oneandone.co.uk;Database=db198704784;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select UserName from Users", conn);SqlDataReader rdr = cmd.ExecuteReader();

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:TextBoxID="TextBox1"runat="server"OnTextChanged="TextBox1_TextChanged"Height="176px"Width="338px"></asp:TextBox><asp:LabelID="Label1"runat="server"Text="Test Of SQL"></asp:Label></div></form>

</

body>

</

html>|||

Try to open the connection before you call ExecuteReader() example below

SqlCommand cmd =newSqlCommand("select UserName from Users", conn);

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();

HC

--

Mark this post as ANSWERED if it helped you

|||

I've tried the Open command but still no joy. I think i might have to hire someone to go into my MS hosting and check it and the SQL is setup, and make a little Logon aspx with the sql database so i can see the code to access it. If anyone is interested please email me.

|||

Hello,

These are some tutorial for you:

http://www.codeproject.com/aspnet/SQLConnect.asp

http://samples.gotdotnet.com/quickstart/aspplus/doc/adoplusoverview.aspx

HTH

|||

I tried to access the page you provided but unfortunately its unavailable. What do you mean by the page crashes? Can you post the complete error you are getting? Make sure the database exists and the table you trying to query exists in thedatabase.

Note: Never give out the username/password in the posts. replace them with XXXXX

Thanks