Append to SQL Driving Me Nuts

[NewOldFella]

Been playing with this on and off for a few weeks thinking I am simply missing something...

In frustration I decided to make a table with the data I am trying to append. I thought this would help me spot the issue.

I have 25 records only in this table, to make this run a bit faster as the SQL database is on the net.

However and so very annoyingly the append returns a version of:



This would be fine if it made any sense. However I have very carefully made a table of the old data, made the data types identical and then tried to append only to get the same error (actually made it worse, went from 12 to 19 as in the sample).

The tables seem to match in data type:




So I am at a loss on where to look next?

[orange]

Welcome.
How about stepping back and telling us in plain English--no jargon, no database terms -- what subject matter is involved?
We can't help or advise until we understand the issue in context. What is the business involved? How do watertestPerson, User, Active, FilterNumber etc fit in "a day at the company"?

Perhaps you could also tell us a little about this set up "SQL database is on the net."

[CJ_London]

The tables seem to match in data type:

numbers can be one of many types - so you need to check you are matching to the same (or bigger) number type. In size they go from

byte
integer
long
single
double

And, not sure it would just truncate but text fields have a number of characters specified

Also - it is 19 fields, not records, which implies your destination table will have some blanks in it - perhaps all in one column, so a visual inspection might give a clue

[Micron]

What's missing from the pics is the properties of the fields. While you might have every field matching in type, you might have Required set to yes in one or more destination fields, and you're trying to append Null values, which of course, you cannot and will raise that error. I would look for that or a similar situation first, because IIRC, appending one type of number into another (e.g. decimal into long) will work but the target field will accept only what fits. Thus, 123.456 appended into a long data type field will result in 123 as the decimal value won't fit, and the append won't raise that error.

EDIT: pretty sure it the number specified is for records and not fields. At least that's what my experiment indicates:

INSERT INTO test ( qty, amount ) SELECT "test" AS qty, "apple" AS amount
FROM test;

One field append or two (as shown) the message gave the same count.

[CJ_London]

pretty sure it the number specified is for records and not fields

the message clearly says 19 fields - and further on it says 'it didn't add 0 records …..' which means all records were appended

[Micron]

I know what the message says. Rather than me get snippy, I'll just post the relevant pics.

There are 8 records to be appended.




when attempting to append text into 2 numeric fields, the message says 8 fields were set to Null because of conversion errors




when attempting to append text into 1 numeric field, the message still says 8 fields were set to Null because of conversion errors



and it doesn't just say "didn't add 0 records". It says it didn't add 0 records due to key violations (and/or lock violations and/or validation rule violations). Each reason is independent and has no bearing on any of the others. It could still add 6 records (not with the example shown) and say 0 for any other reason and you won't know how many were actually added. The OP's first post suggests that out of 25 records in the table, 6 were added because 19 were not. That is contrary to

which means all records were appended

[CJ_London]

Didn't mean to question - was just trying to clarify

OP hasn't said what (if anything) was actually appended