We use bcp a lot in production and redistributable software.
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
Thanks.
Tom
Thomas Brown wrote:
> We use bcp a lot in production and redistributable software.
> We'd like to get bcp to terminate early if it runs for over a certain
> amount of time due to locking/deadlock/...
> Thus, I'm trying something like and it's not working - ideas?
> bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2"
> queryout "s24.txt" -c -U abc -P ewe
A lock timeout causes an automatic attention of the query if it runs
into a situation where the query is waiting on a locked resource, in
this case, for more than 2 seconds. It is not the same thing as a query
timeout. That is, it does not terminate the query if the query runs for
more than 2 seconds even if it encounters locks along the way less than
2 seconds each.
OSQL has the -t<timeout> parameter to tell OSQL that max time for the
query. But BCP has no such parameter as far as I can see, probably
because you wouldn't want to stop a BCP operation in the middle.
If BCP encounters a deadlock situation (probably unlikely given that it
is accessing a single table in most cases), the transaction is
terminated. In a locking situation, the BCP operation could slow down
assuming none of the locks exceed the lock timeout. I'm not even sure a
lock timeout like that would work in BCP.
You can use the TABLOCK hint for BCP which gives you a lock on the table
during the operation. That should eliminate any blocking
For data extraction, you can use try using OSQL which supports what you
want.
David Gugick
Imceda Software
www.imceda.com
|||Thomas Brown wrote:
> We use bcp a lot in production and redistributable software.
> We'd like to get bcp to terminate early if it runs for over a certain
> amount of time due to locking/deadlock/...
> Thus, I'm trying something like and it's not working - ideas?
> bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2"
> queryout "s24.txt" -c -U abc -P ewe
A lock timeout causes an automatic attention of the query if it runs
into a situation where the query is waiting on a locked resource, in
this case, for more than 2 seconds. It is not the same thing as a query
timeout. That is, it does not terminate the query if the query runs for
more than 2 seconds even if it encounters locks along the way less than
2 seconds each.
OSQL has the -t<timeout> parameter to tell OSQL that max time for the
query. But BCP has no such parameter as far as I can see, probably
because you wouldn't want to stop a BCP operation in the middle.
If BCP encounters a deadlock situation (probably unlikely given that it
is accessing a single table in most cases), the transaction is
terminated. In a locking situation, the BCP operation could slow down
assuming none of the locks exceed the lock timeout. I'm not even sure a
lock timeout like that would work in BCP.
You can use the TABLOCK hint for BCP which gives you a lock on the table
during the operation. That should eliminate any blocking.
For data extraction, you can use try using OSQL which supports what you
want.
David Gugick
Imceda Software
www.imceda.com
2012年3月25日星期日
bcp timeout
We use bcp a lot in production and redistributable software.
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
Thanks.
Tom
Why don't you try the otherway?
Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
can improve performance of the bulk copy operation due to reduced lock
contention on the table".
Since this may ensure faster bcp, there will be less locking due to users
transaction.
Thanks
GYK
|||We do use TABLOCK in our production code, and we do see perf. gains and a
reduction in locking contention. However, on our larger tables(10 GB) we see
MS SQL fall over into a state of blocking and deadlock.
We have partitioned our larger tables several times over and can't really do
that anymore to meet business requirements.
"GYK" wrote:
> Why don't you try the otherway?
> Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
> can improve performance of the bulk copy operation due to reduced lock
> contention on the table".
> Since this may ensure faster bcp, there will be less locking due to users
> transaction.
> Thanks
> GYK
sql
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
Thanks.
Tom
Why don't you try the otherway?
Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
can improve performance of the bulk copy operation due to reduced lock
contention on the table".
Since this may ensure faster bcp, there will be less locking due to users
transaction.
Thanks
GYK
|||We do use TABLOCK in our production code, and we do see perf. gains and a
reduction in locking contention. However, on our larger tables(10 GB) we see
MS SQL fall over into a state of blocking and deadlock.
We have partitioned our larger tables several times over and can't really do
that anymore to meet business requirements.
"GYK" wrote:
> Why don't you try the otherway?
> Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
> can improve performance of the bulk copy operation due to reduced lock
> contention on the table".
> Since this may ensure faster bcp, there will be less locking due to users
> transaction.
> Thanks
> GYK
sql
bcp timeout
We use bcp a lot in production and redistributable software.
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
--
Thanks.
TomWhy don't you try the otherway?
Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
can improve performance of the bulk copy operation due to reduced lock
contention on the table".
Since this may ensure faster bcp, there will be less locking due to users
transaction.
Thanks
GYK|||We do use TABLOCK in our production code, and we do see perf. gains and a
reduction in locking contention. However, on our larger tables(10 GB) we see
MS SQL fall over into a state of blocking and deadlock.
We have partitioned our larger tables several times over and can't really do
that anymore to meet business requirements.
"GYK" wrote:
> Why don't you try the otherway?
> Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
> can improve performance of the bulk copy operation due to reduced lock
> contention on the table".
> Since this may ensure faster bcp, there will be less locking due to users
> transaction.
> Thanks
> GYK
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
--
Thanks.
TomWhy don't you try the otherway?
Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
can improve performance of the bulk copy operation due to reduced lock
contention on the table".
Since this may ensure faster bcp, there will be less locking due to users
transaction.
Thanks
GYK|||We do use TABLOCK in our production code, and we do see perf. gains and a
reduction in locking contention. However, on our larger tables(10 GB) we see
MS SQL fall over into a state of blocking and deadlock.
We have partitioned our larger tables several times over and can't really do
that anymore to meet business requirements.
"GYK" wrote:
> Why don't you try the otherway?
> Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
> can improve performance of the bulk copy operation due to reduced lock
> contention on the table".
> Since this may ensure faster bcp, there will be less locking due to users
> transaction.
> Thanks
> GYK
2012年3月19日星期一
BCP -k option - Very urgent
Hi Everyone,
I tried to bcp in the data file to a table in sqlserver 2000.
The data file has got few blanks in certain columns.
My requirement is all those blanks has to be converted null while bcp
in the data.
1. I have tried giving the option -k - Didn't work
2. I have tried creating a format file with prefix length 2 i.e as the
column is character.
3. Initially it was giving string data truncation error due to the
service pack3. Then I updated the service pack 3 also.
Pls someone help me out as this is very critical.
Thanks,
GaneshHi
An empty string is not the same as a null value. The -k option will not help
you with that. If you want to ignore the whole column then you can use the
format file to ignore the column. If you want to change these values after
loading use a subsequent update statement.
John
"Ganesh Babu" <ganesh.kaliaperumal@.wipro.com> wrote in message
news:35f45fe8.0407130502.37f14ad0@.posting.google.c om...
> Hi Everyone,
> I tried to bcp in the data file to a table in sqlserver 2000.
> The data file has got few blanks in certain columns.
> My requirement is all those blanks has to be converted null while bcp
> in the data.
> 1. I have tried giving the option -k - Didn't work
> 2. I have tried creating a format file with prefix length 2 i.e as the
> column is character.
> 3. Initially it was giving string data truncation error due to the
> service pack3. Then I updated the service pack 3 also.
> Pls someone help me out as this is very critical.
> Thanks,
> Ganesh
2012年2月16日星期四
Batch update and trigger
Hi All,
I have a trigger on a table tracking changes to certain fields.
However, when I do a batch update on that field, it looks like the
trigger only gets fired once. However, I do want to track changes on
every record that the update statement touches. So, is there anyway to
make it work or is there any work around? Do I have to create a cursor
and update one record a time?
Thanks a lot,
blueyep, triggers in SQL Server are fired once per statement, not once per
row. when you describe how do you need to track changes, we might be
able to help|||what I want to do is very simple. I want to track the change to a field
in one table. if the value changes, I will insert a row in the change
log table. Basically if old field value <> new field value, insert a
row in the change log.
However, in another script, I sometimes update the field for many rows
if they meet the criteria. Therefore, I have something like UPDATE
table1 SET field1= 'Y' WHERE field2>field3. When this statement is
executed, the trigger only fired once and therefore, only one row gets
inserted into the changelog table.
I just wonder if there is anyway I can make the trigger fired for each
row without abandoning the batch update and use cursor to do update for
each individual row
Thanks a lot.|||if you don't modify the PK, that's easy:
create table seq(s_id int identity, i int)
insert into seq(i) values(1)
insert into seq(i) values(2)
insert into seq(i) values(3)
insert into seq(i) values(4)
go
create table seq_audit(s_id int, old_i int, new_i int)
go
create trigger seq_upd
on seq
for update
as
insert into seq_audit
select inserted.s_id, deleted.i, inserted.i from inserted, deleted
where inserted.s_id = deleted.s_id
go
select * from seq
s_id i
-- --
1 1
2 2
3 3
4 4
go
update seq set i=i+1 where s_id > 1
go
select * from seq_audit
s_id old_i new_i
-- -- --
2 2 3
3 3 4
4 4 5
go
drop table seq
drop table seq_audit
I have a trigger on a table tracking changes to certain fields.
However, when I do a batch update on that field, it looks like the
trigger only gets fired once. However, I do want to track changes on
every record that the update statement touches. So, is there anyway to
make it work or is there any work around? Do I have to create a cursor
and update one record a time?
Thanks a lot,
blueyep, triggers in SQL Server are fired once per statement, not once per
row. when you describe how do you need to track changes, we might be
able to help|||what I want to do is very simple. I want to track the change to a field
in one table. if the value changes, I will insert a row in the change
log table. Basically if old field value <> new field value, insert a
row in the change log.
However, in another script, I sometimes update the field for many rows
if they meet the criteria. Therefore, I have something like UPDATE
table1 SET field1= 'Y' WHERE field2>field3. When this statement is
executed, the trigger only fired once and therefore, only one row gets
inserted into the changelog table.
I just wonder if there is anyway I can make the trigger fired for each
row without abandoning the batch update and use cursor to do update for
each individual row
Thanks a lot.|||if you don't modify the PK, that's easy:
create table seq(s_id int identity, i int)
insert into seq(i) values(1)
insert into seq(i) values(2)
insert into seq(i) values(3)
insert into seq(i) values(4)
go
create table seq_audit(s_id int, old_i int, new_i int)
go
create trigger seq_upd
on seq
for update
as
insert into seq_audit
select inserted.s_id, deleted.i, inserted.i from inserted, deleted
where inserted.s_id = deleted.s_id
go
select * from seq
s_id i
-- --
1 1
2 2
3 3
4 4
go
update seq set i=i+1 where s_id > 1
go
select * from seq_audit
s_id old_i new_i
-- -- --
2 2 3
3 3 4
4 4 5
go
drop table seq
drop table seq_audit
2012年2月13日星期一
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
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
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
2012年2月9日星期四
Basic select statement question
If I want to select certain values from a table where date is equal to something and the second field is null,would I go.
select * from table where date = 'something' and Sent = null or something else because if I do it this way I get nothing as a result but if I scroll down I can see that I have null values.
thanksNULL is a tricky beast, because nothing ever equals NULL, not even NULL itself. You need to modify your statement slightly to use a semantically different test, like:SELECT *
FROM table
WHERE date = 'something'
AND Sent IS nullThis seems like a trivial difference, and from a coding perspective it is. From the logical perspective however, the difference is huge.
-PatP|||Ooh,that made all the difference, I always say, it is easy once you know how to do it.
Thanks for you help once again|||Since you're dealing with this, you might want to look up ANSI_NULLS in Book Online and memorize it. :) It will save you a lot of pain down the road. Make sure you use SET ANSI_NULLS ON when creating tables and procedures.|||Will do so,thanks
select * from table where date = 'something' and Sent = null or something else because if I do it this way I get nothing as a result but if I scroll down I can see that I have null values.
thanksNULL is a tricky beast, because nothing ever equals NULL, not even NULL itself. You need to modify your statement slightly to use a semantically different test, like:SELECT *
FROM table
WHERE date = 'something'
AND Sent IS nullThis seems like a trivial difference, and from a coding perspective it is. From the logical perspective however, the difference is huge.
-PatP|||Ooh,that made all the difference, I always say, it is easy once you know how to do it.
Thanks for you help once again|||Since you're dealing with this, you might want to look up ANSI_NULLS in Book Online and memorize it. :) It will save you a lot of pain down the road. Make sure you use SET ANSI_NULLS ON when creating tables and procedures.|||Will do so,thanks
订阅：
博文 (Atom)