When I execute an INSERT statement no changes have been made, although the message states (146689 rows affected). What am I doing wrong?

Question

Here is the statement I executed:

INSERT INTO Import_LAND_2 (BegEffDate) 
    SELECT BegEFFDate 
    FROM Import_AppSite;

Message after execution:

(146689 rows affected)

I checked the Import_Land_2 table and the BegEffDate column is still NULL, although the SQL message states that (146689 rows affected).

You appear to be missing something. Do tables `Import_LAND_2` and `Import_AppSite` only have the single column `BegEffDate`? You are going to get a new record in `Import_LAND_2` for each record in `Import_AppSite` and only the column `BegEffDate` will have data. It will be `NULL` if it is `NULL` in each record of `Import_AppSite`. — daShier, Sep 17 '19 at 18:35
If you don't want to insert rows with null values in BegEFFDate, then don't select them. And it is difficult to believe that you reviewed all 146k rows that were inserted to verify that every row had a null value for BegEffDate. Did you verify all of them? How exactly did you do this? Did the table Import_LAND_2 already have rows before the insert statement was executed? — SMor, Sep 17 '19 at 18:38
@daShier The BegEffDate column in the Import_AppSite table is populated with the data I need in my table, that's why I'm trying to transfer that data by using the insert statement into my Import_Land_2 table, BegEffDate column. — TechieGirlinCali, Sep 17 '19 at 18:38
@SMor, No I did not check all rows, you're correct about that. I did scroll the top rows and bottom and the whole row is NULL. The BegEffDate column in the Import_AppSite table is populated, I spoke with the DBA and he told me it's okay for me to go in there and pull that data- he manages it, so it's ready for me to pull into my table. I want to figure this out first before asking my co-workers for help. — TechieGirlinCali, Sep 17 '19 at 18:40
I'm under the impression you're confused about `INSERT` - this will insert **NEW ROWS** into your table - but it will **NOT AFFECT** your existing rows! You seem to expect to get **new values** in your **existing rows** by this command - for that, you would need to use an **`UPDATE`** statement - not an `INSERT` ..... — marc_s, Sep 17 '19 at 18:46
@marc_s I tried the UPDATE statement and I'm now getting (440067 rows affected), but when I reference back to the table in which i'm trying to update the column is still NULL. I guess at this point, it's best to ask my co-workers for help. I wanted to do it independently, but that didn't work out. Thanks for your help. — TechieGirlinCali, Sep 17 '19 at 19:01
@Eric I referred to this --> https://stackoverflow.com/questions/2002500/what-are-differences-between-insert-and-update-in-mysql — TechieGirlinCali, Sep 17 '19 at 20:13
I figured it out by doing some research and scanning this site! I used this example as a benchmark: UPDATE table1 SET table1.Price = table2.price FROM table1 INNER JOIN table2 ON table1.id = table2.id — TechieGirlinCali, Sep 17 '19 at 20:30
The answer was here in this feed. I had to do an INNER JOIN --> https://stackoverflow.com/questions/1746125/update-columns-values-with-column-of-another-table-based-on-condition/1746161 — TechieGirlinCali, Sep 17 '19 at 20:31

score 0 · Answer 1 · answered Sep 17 '19 at 18:44

0

Try:

INSERT INTO Import_LAND_2 (BegEffDate) 
SELECT BegEFFDate FROM Import_AppSite
WHERE BegEFFDate IS NOT NULL;

See if you get the records you want, if you get no records, there's something else going on.

answered Sep 17 '19 at 18:44

daShier

2,056
2
8
14

score -1 · Answer 2 · answered Sep 17 '19 at 18:33

-1

Sometime, there is a roll back done by the DBengine. Please check if this is the case with your request. Tx

answered Sep 17 '19 at 18:33

F M

1

score -1 · Answer 3 · answered Sep 17 '19 at 19:08

-1

Kindly check trigger on this table. I think there is instead of trigger.

answered Sep 17 '19 at 19:08

Somendra Kanaujia

727
5
12

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. – Dale K Sep 18 '19 at 20:50

When I execute an INSERT statement no changes have been made, although the message states (146689 rows affected). What am I doing wrong?

3 Answers3