INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Fails to Inserts two columns but no error given

Fails to Inserts two columns but no error given

(OP)
There are two tables in MS Access that are identical,they are linked tables,but the structure is same even though their database is different,so i tried

INSERT INTO TableA
SELECT *
FROM TableB

but it inserts three columns and two columns data is not inserted and left blank,but it doesnt give any error so what should i do to make sure all the 5 columns are inserted

Thanks

RE: Fails to Inserts two columns but no error given

Are there any lookup fields? Are there any constraints with primary/foreign keys?

I never create a statement like this without specifying the field names.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Fails to Inserts two columns but no error given

(OP)
Just because i didnt give the field names shouldn't stop it from inserting those rows into the table right?there are indexes but the point is it didnt enter a single row for those two columns,they are name columns by the way,like Last name and first name,they are completely blank

RE: Fails to Inserts two columns but no error given

It doesn't make sense to me that it doesn't work but you haven't provided actual fields or data for us to help troubleshoot. TableA and TableB don't tell us much.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Fails to Inserts two columns but no error given

@Kalyan,

Do you want a working solution, or do you want to be justified in your approch?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Fails to Inserts two columns but no error given

(OP)
Ok you guys dont know the database i use i dont understand what difference would it make to give the field names,.anyway here is the exact query as it is,this time i tried with the exact field names

INSERT INTO Reciever ( Reciever_Sys_Id, Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active )
SELECT Reciever_Sys_Id, Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active
FROM Recievers_Prod
WHERE Reciever_Lname is not null;

Also there is only one trigger,which uses a sequence to insert values into the Reciever_Sys_Id,like a auto increment on Reciever_Sys_Id..so i am still very frustrated as to why it would't enter the two columns Reciever_Lname and Reciever_Fname

RE: Fails to Inserts two columns but no error given

So are these linked from Access or a SQL Server or other? If the Reciever_Sys_Id is auto increment, why are you attempting to force a value into it?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Fails to Inserts two columns but no error given

(OP)
Well i tried without forcing value into Reciever_Sys_Id this time i just thought id give all the column names(as per the suggestion of someone here) ..
One table is a Development table that is linked to Oracle and another(the source table)is a production table also linked...
If it can enter the values of three columns why not those two?linked tables shouldnt cause it right?

Thanks anyways for your effort.i really need to figure this out ..

RE: Fails to Inserts two columns but no error given

Try skipping Reciever_Sys_Id field in 2 places in your statement.

Since "one trigger,which uses a sequence to insert values into the Reciever_Sys_Id,like a auto increment on Reciever_Sys_Id"

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Fails to Inserts two columns but no error given

(OP)
You mean write the query like this?

INSERT INTO Reciever ( Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active )
SELECT Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active
FROM Recievers_Prod
WHERE Reciever_Lname is not null;

RE: Fails to Inserts two columns but no error given

(OP)
Tried this and still no luck :(

INSERT INTO Reciever ( Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active )
SELECT Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active
FROM Recievers_Prod
WHERE Reciever_Lname is not null;

327 Rows added but not the Lname and Fname

RE: Fails to Inserts two columns but no error given

Quote (Andy)

Try skipping Reciever_Sys_Id

CODE

INSERT INTO Reciever ( Reciever_LName, Reciever_Fname, Reciever_Active )
SELECT Reciever_LName, Reciever_Fname, Reciever_Active
FROM Recievers_Prod
WHERE Reciever_Lname is not null; 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Fails to Inserts two columns but no error given

(OP)
But i need Reciever_ID, Reciever_Sys_ID is the autoincrement but not Reciever_ID..i cant skip that

RE: Fails to Inserts two columns but no error given

Weird...

How about:
... SELECT Reciever_ID, 'Smith', 'John', Reciever_Active
...


Just hard-code the name and see if that will fly.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Fails to Inserts two columns but no error given

(OP)
Tried SELECT Reciever_ID, 'Smith', 'John', Reciever_Active

still not inserting...this is ridiculous, i have never encountered anything like this..

I even tried copying the column from the source and highlighting and pasting in the table but it keeps giving the write conflict error

RE: Fails to Inserts two columns but no error given

So the only thing left is to compare how the fields Reciever_LName and Reciever_Fname are declared in both tables. And they cannot be declared the same, there must be a difference.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Fails to Inserts two columns but no error given

(OP)
They both are text fields..thats something i compared when the first time the inserts failed..

RE: Fails to Inserts two columns but no error given

(OP)
Ok i tried manually entering data into the Name fields and its giving the Write Conflict error,

It says This Record has been changed by another user since you started editing it.If you save the record ,you will overwrite the changes the other user made

RE: Fails to Inserts two columns but no error given

You have an existing Reciever_ID

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Fails to Inserts two columns but no error given

(OP)
What do you mean?the table is empty when i try to do the insert?

RE: Fails to Inserts two columns but no error given

Do ANY of the Receiver_ID values exist in BOTH tables?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Fails to Inserts two columns but no error given

(OP)
When i load the data into the Destination table it is empty,so there are no values for anything ,so no value for Receiver_ID

RE: Fails to Inserts two columns but no error given

Quote:

Ok i tried manually entering data into the Name fields and its giving the Write Conflict error,

It says This Record has been changed by another user since you started editing it.If you save the record ,you will overwrite the changes the other user made

Quote:

When i load the data into the Destination table it is empty,so there are no values for anything ,so no value for Receiver_ID

???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Fails to Inserts two columns but no error given

(OP)
That was when i tried in MS Access,like open the table and just enter the Last Name field

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close