how to insert records from one table to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

how to insert records from one table to another

Author

Topic

ontljoshi
Starting Member

13 Posts

Posted - 2007-04-03 : 12:04:19

Insert into Sifa_Data(L1_CUST_REF, L1_TITLE, ...)
Select
Case when Field1 = 'L1_CUST_REF' then Field2 else NULL end as L1_CUST_REF,
Case when Field1 = 'L1_TITLE' then Field2 else NULL end as L1_TITLE
...
From
SHEET1

i am trying to use this as one of my good friend has advised to try this but as i m trying in ms access it did not worked, i am thankful of him.

kindly any one would like to help me...

om joshi

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-03 : 12:18:16

You can try replacing CASE with IIF() in Access as follows:

Insert into Sifa_Data(L1_CUST_REF, L1_TITLE, ...)
Select
	IIF(Field1 = 'L1_CUST_REF', Field2, ''),
	IIF(Field1 = 'L1_TITLE', Field2, ''),
...
From
SHEET1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ontljoshi
Starting Member

13 Posts

Posted - 2007-04-03 : 12:27:42

quote:
Originally posted by harsh_athalye

You can try replacing CASE with IIF() in Access as follows:

Insert into Sifa_Data(L1_CUST_REF, L1_TITLE, ...)
Select
IIF(Field1 = 'L1_CUST_REF', Field2, ''),
IIF(Field1 = 'L1_TITLE', Field2, ''),
...
From
SHEET1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

any thing like this but can i use nested IIF in one statement....because this inserts one record for one filed i want to insert all respective values in all fields at a time and then it can pick up another record

it did worked but was not properly inserting the data....into table.
its treating the title as a another velue but that value is related to l1_customer_reference but there is no relation in between them.

what happens here it inserts first l1_customer_reference as first record and leaves blank for other fields in sifa_data table and then as 2nd record it leaves l1_cust_ref blank and inserts the value of l1_title into it and several records are blank and and repeats the same task...

can you give me any hint for that...

let me give you proper look of tables...
table sheet1
Field1 Field2
L1_CUSTOMER_REFERENCE ---> AbreyCatherine
L1_TITLE -----> Ms
L1_SURNAME -----> Abrey
L1_FORENAME Catherine
L1_DOB 21002
L2_DOB 19914
L1_ADD1 165 Downhills Way

and table sifa_data

L1_CUSTOMER_REFERENCE L1_TITLE L1_SURNAME L1_FORENAME L1_DOB L2_DOB L1_ADD1

this is the scenario...

thanks.

om joshi

ontljoshi
Starting Member

13 Posts

Posted - 2007-04-04 : 10:46:00

hi...anyone could you please help me...
thanks

om joshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 13:34:46

The only time we can help you, is when you give us THE FULL PICTURE of what you are trying to accomplish.
Start all over again. Rephrase your goal. Post ALL relevant data, such as sample data, table DDL and expected result.

Peter Larsson
Helsingborg, Sweden

ontljoshi
Starting Member

13 Posts

Posted - 2007-04-09 : 09:24:50

quote:
Originally posted by Peso

The only time we can help you, is when you give us THE FULL PICTURE of what you are trying to accomplish.
Start all over again. Rephrase your goal. Post ALL relevant data, such as sample data, table DDL and expected result.

Peter Larsson
Helsingborg, Sweden

hi, i want to convert some below mentioned format into msaccess tables.

the exact scenario is that i have some text files from old applicaiton (pascal based) which is no longer working due to hardware compatibility problem...

which has generated some text files under below mentioned format...

[CLIENT]
L1_CUSTOMER_REFERENCE=AbreyCatherine
L1_TITLE=Ms
L1_SURNAME=Abrey
L1_FORENAME=Catherine
L1_DOB=1 Jul 1957
L2_DOB=9 Jul 1954
L1_ADD1=165 Downhills Way
L1_ADD2=Tottenham
L1_ADD3=London
L1_PCODE=N17 6AH
Q_L1_SEX=F
L1_CLCODE=CA1
L1_MSTATUS=M
L1_RELIGION=Christ.
L1_HASPARTNER=Y
L1_PARTNERNAME=Gian Paolo Caddeo
L1_PHONEH=0181 889 3870
L1_SALUTATION=Catherine
L1_OCC=Management Consultant
L1_EMP_STAT=S
L1_HEALTH=G
L1_SMOKE=Y
L1_NATIONALITY=British
L1_NRA=60
L2_OCC=Heating Engineer
L2_EMP_STAT=S
L2_HEALTH=G
L2_SMOKE=N
L2_NATIONALITY=Italian
L2_NRA=65
FactFindDate=30 Oct 98
TermsOfBusDate=30 Oct 98
LastVisitDate=18 Oct 95
LastLetterDate=14 Dec 99
Consultant=KP
AdminRef=AT
BestCallTime=Evenings
ClientType=UKCL
L1_WillDate=1993
L2_WillDate=1993
MailAllowed=-1
LastMailDone=0
L1_NINumber=YZ070258B
SeparateTax=
ContactFrequency=26
ManualSelect=
ClientLastUpdated=29 Jun100
[CLIENT]

each and every record starts from [CLIENT] and First part is the field and the part " = " is the value for the respective field.

now, i need help in converting that information into msaccess tables and its respective values...

like ...1) first part will become the field of table and
2) 2nd part after " = " will be the value for the respective field of the respective table.

i hope that this has given clear picture. where i want help from an expert...so, kindly help me. That will be great indeed to me.. thanking yours...om

om joshi

ontljoshi
Starting Member

13 Posts

Posted - 2007-04-09 : 09:38:30

SELECT IIf(Field1='L1_CUSTOMER_REFERENCE',Field2), IIf(Field1='L1_TITLE',Field2), IIF(FIELD1='L1_SURNAME',FIELD2), IIF(FIELD1='L1_FORENAME',FIELD2)
FROM SHEET1;
this one is giving me this sort of result....
Expr1000 Expr1001 Expr1002 Expr1003
AbreyCatherine [blank] [blank] [blank]
[blank] Ms [blank] [blank]
[blank] [blank] Abrey [blank]
[blank] [blank] [blank] Catherine
AhmadFaizan [blank] [blank] [blank]
[blank] Mr [blank] [blank]
[blank] [blank] Ahmad Faizan
AhmadH
Miss
Ahmad
H

ANY HINTS FOR THIS... which returns the values smae time....

REGARDS

om joshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 10:42:57

How many CLIENTS are there in the file?

Peter Larsson
Helsingborg, Sweden

ontljoshi
Starting Member

13 Posts

Posted - 2007-04-23 : 05:28:55

quote:
Originally posted by Peso

How many CLIENTS are there in the file?

Peter Larsson
Helsingborg, Sweden

there are arrond 700 hundred clients in the file...

can any one get me the solution....

do reply...

om joshi

ACushen
Starting Member

29 Posts

Posted - 2007-04-26 : 14:34:07

If this is a one-time conversion, I think you'd be better off using Access' Import tools:

File menu -> Get External Data -> Import

Choose "Text files" in the "Files of type" Drop-Down - you may have to re-name the file to have an .asc extension.

Then you will tell Access that the file is a Delimited textfile, and the delimiting character is the "=" character. You may have to play around with the options to get Access to recognize [CLIENT} as the record delimiter.

-Andrew

ontljoshi
Starting Member

13 Posts

Posted - 2007-04-30 : 08:34:36

quote:
Originally posted by ACushen

If this is a one-time conversion, I think you'd be better off using Access' Import tools:

File menu -> Get External Data -> Import

Choose "Text files" in the "Files of type" Drop-Down - you may have to re-name the file to have an .asc extension.

Then you will tell Access that the file is a Delimited textfile, and the delimiting character is the "=" character. You may have to play around with the options to get Access to recognize [CLIENT} as the record delimiter.
-Andrew

hi, that's the point where i am struggling...how can i tell ACCESS to recoginze [CLIENT] as the record delimiter...??
thanks...om

om joshi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources