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

Query not inserting both the columns into MS Access tables

Query not inserting both the columns into MS Access tables

(OP)
I have imported a table into MS Access from Excel.its called Sheet1

I have a query below

INSERT INTO LOAN_LIST ( NEW_LOAN_NUMBER,OLD_LOAN_NUMBER )
SELECT TOP 100 Sheet1.[ACCT_NBR] AS NEW_LOAN_NUMBER,Sheet1.[V_Acct_Nbr] as OLD_LOAN_NUMBER
FROM Sheet1;


for some reason it doesnt insert the first column ACCT_NBR into Loan List table..when i just run this query

SELECT TOP 100 Sheet1.[ACCT_NBR] AS NEW_LOAN_NUMBER,Sheet1.[V_Acct_Nbr] as OLD_LOAN_NUMBER
FROM Sheet1;

it does pull two columns data.
there are no triggers or any constraint in the linked table Loan_List

what could be wrong here?

RE: Query not inserting both the columns into MS Access tables

I would check constraints. Are there required records in a related table? Are any of the fields "lookup" fields?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Query not inserting both the columns into MS Access tables

How are the 2 columns defined in LOAN_LIST table and in Sheet1 table?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query not inserting both the columns into MS Access tables

(OP)
In Loan_List Table the two columns are number, sheet1 table both the columns are numbers,also i didnt see any constraints or dependencies in the table Loan_list

RE: Query not inserting both the columns into MS Access tables

What happens when you do:
INSERT INTO LOAN_LIST ( NEW_LOAN_NUMBER, OLD_LOAN_NUMBER )
VALUES (123, 678)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query not inserting both the columns into MS Access tables

(OP)
Tried

INSERT INTO LOAN_LIST ( NEW_LOAN_NUMBER, OLD_LOAN_NUMBER )
VALUES (123, 678)


inserts 678 into old_loan_number

RE: Query not inserting both the columns into MS Access tables

What about the NEW_LOAN_NUMBER field? Nothing? Is it NULL? Any errors? Do you allow NULL to be in this field?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query not inserting both the columns into MS Access tables

(OP)
new loan number is just null,it just doesnt insert into it ,there are no constraints or conditions so yes null must be allowed

RE: Query not inserting both the columns into MS Access tables

Can you update the NEW_LOAN_NUMBER field by hand? Can you type a number in there? And does it take it/does the number stay in the field?

Grasping straws here... smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query not inserting both the columns into MS Access tables

(OP)
Actually i did try that and it wouldn't let me insert values that way ....this is very strange.like i said there are no triggers on the linked table in Oracle,no contraints,no primary key,but the allow null is checked as Yes,but thats for both columns,dont know why it would prevent only this one to enter data

RE: Query not inserting both the columns into MS Access tables

(OP)
I tried this

create table LOAN_LIST_Test as select * from LOAN_LIST_VLS;

INSERT INTO LSDMGR.LOAN_LIST_Test(NEW_LOAN_NUMBER,OLD_LOAN_NUMBER) VALUES(7400760339,410011260339);

select * from LOAN_LIST_Test;

It showed that it has both the column values

How could this work when the original table doesnt let the data not entered into New_Loan_NUmber

RE: Query not inserting both the columns into MS Access tables

You can spend days tracking this issue.

Create a new table
Transfer all data from the old table to the new table
Drop the old table
Rename the new table

Problem solved.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query not inserting both the columns into MS Access tables

(OP)
Can oracle read CSV file thru pl sql and insert two columns into a oracle table?i can do this manually i know that but all this needs to be run in a script in production that is why..

RE: Query not inserting both the columns into MS Access tables

Yes.
You can have a simple Stored Procedure or a Package written in PL/SQL in Oracle where you can read a csv file and write the data into an Oracle's table.

From your other post: thread1662-1780658: How to move a excel file into oracle

Quote:

What would be the code to insert the data from two columns in the excel file into the oracle table..

First you need to determine if you want to read from Excel file or from CSV file - 2 completely different 'animals' smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query not inserting both the columns into MS Access tables

(OP)

Andrzejek

Sorry i pasted the request to get the code to read from CSV into Oracle table in the wrong posting,but i am assuming you can help me on this,so i need the code to

INSERT two columns data in a TABLE in Oracle from a CSV file, i need this done using code and not thru wizard or any package.just thru Pl/SQL Code

RE: Query not inserting both the columns into MS Access tables

(OP)
Yes Oracle 11 ,can i have the code for it?

RE: Query not inserting both the columns into MS Access tables

You may try this approach smile
One of the places with some good information is here

Have fun.

---- Andy

There is a great need for a sarcasm font.

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