INSERT using row fields and a static value

[cc4mail]

Im trying to do the following INSERT from my us_business DB (12 fields) to service_providers_que DB (60+fields) and add some static values along with the INSERT at the end. 611612 is the test value that is to go into all shop_naics fields

the INSERT works, but the last added value '611612' is inserted into shop_naics as a totally different value - 32767 - no idea where this is coming from (nope not the last field in the DB). all the other fields are correct

INSERT INTO service_providers_que(
name, address, city, shop_name, email, fax, phone, state, website, zip_code, shop_about, shop_naics )
SELECT
name, address, city, shop_name, email, fax, phone, state, website, zip_code, type_bus, '611612' AS shop_naics
FROM us_business
WHERE sic = '829909'


the alternative is to do an INSERT...

then a second run doing an UPDATE with all the static values (probably about 20+ more fields), but it seems this could be done in one run

 

[DonQuichote]

This looks like an integer overflow. Please do not quote numbers if it is not necessary. What happens is (I think) the string is cast to an integer because the column is numeric. But the column is defined as a two-byte integer somehow and some bits are dropped. If you do not check the warnings, any such message is lost.

The only remedy in that case is to change the column definition. Otherwise, you won't be able to get that number into that column.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

... the column is defined as a two-byte integer somehow

spot on


it's probably defined as SMALLINT

change it to INTEGER

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[cc4mail]

wonderful, it was smallint(6) - INT solved it - I see the limit 32767 gave it away... I missed it.

appreciate the help!