0

I have huge amount of data structured in two excel sheets with the following columns:

EXCEL DATA

Sheet1 [pod, client, address, ...etc] -one record per [pod]
Sheet2 [pod, pointofmeasure, typepct, ...etc] -one-to-many records per [pod]

-relationship is between sheet1.pod and sheet2.pod (one-to-many relationship)

I need a sql to insert data from that excel sheets into a access database with the following tables structure:

ACCESS DATABASE

Table1 [id, pod, client, address, ...etc]
Table2 [id, pod_id, pod, pointofmeasure, typepct, ...etc]

Where table2.pod_id = table1.id

Can be do it in one sql insert?

I came up with this mass insert...

cn.Open scn
ssql = "INSERT INTO table1 (pod, client, address, ...etc) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "].[sheet1$]"

cn.Execute ssql, cAffectedRows
cn.Close

Now how the hell i get id's to insert data to table2 ?

Adrian
  • 1
  • 1
  • There is no relationship between **id**, **pod** and **pod_id** in the original data so **no**, it cannot be done. See [mcve]. –  Sep 14 '16 at 07:00
  • **pod** in _sheet1_ contains unique values, and _sheet2_ contains multiple records linked to **pod** from first sheet - relationship is between **pod.sheet1** and **pod.sheet2** (one-to-many) – Adrian Sep 14 '16 at 07:07
  • please have a look at http://stackoverflow.com/questions/595132/how-to-get-id-of-newly-inserted-record-using-excel-vba?rq=1, what i did not know is, that access supports @@Identity. maybe this question helps you a little? – swe Sep 14 '16 at 07:49

3 Answers3

0

Assuming your pod-column in Table 1 is unique, you have to insert, select again with condition SELECT id FROM table1 WHERE pod = 'myCurrentPod' and there is your ID.

If pod is NOT unique and even pod with other known values is not unique, there is no chance to do it like that. If pod with other known values is unique, you can expand the upper statement with more Conditions.

In all Cases, you cannot do a mass-Insert, but insert one Row from sheet1, insert linked rows from sheet2, next sheet1-row and so on.

If you can design the tables, you should not go with an autoincrement-ID, but with an application-set-ID. Or, if your values in pod are really unique, use this as pk.

swe
  • 1,416
  • 16
  • 26
  • hmm... **pod** from table1 is not unique, can exists multiple records of the same **pod** from different sessions (imports from excel sheets) ... that's why i added a field **pod_id** to make a relation in access and keep a history of POD's if you know what i mean – Adrian Sep 14 '16 at 07:36
  • Only **pod** from excel sheet1 is unique in a single session – Adrian Sep 14 '16 at 07:40
0

It can be done in one insert when your columns are named correctly (i.e. each attribute is uniquely and consistently named throughout the schema) and foreign keys are set up as expected.

Here's a quick sketch using SQL DDL for Access's ANSI-92 Query Mode (you can create the same objects using the Access UI):

CREATE TABLE TableA
( ID IDENTITY NOT NULL UNIQUE, 
  a_col INTEGER NOT NULL );

CREATE TABLE TableB
( ID INTEGER NOT NULL UNIQUE
     REFERENCES TableA ( ID ),  
  b_col INTEGER NOT NULL );

CREATE VIEW TestAB
( a_ID, a_col, b_ID, b_col ) AS 
SELECT A1.ID, A1.a_col, B1.ID, B1.b_col
  FROM TableA AS A1
       INNER JOIN TableB AS B1 ON A1.ID = B1.ID;

If you then insert via the view, omitting the ID column (thus allowing it to be auto-generated) like this:

INSERT INTO TestAB ( a_col, b_col ) VALUES ( 55, 99 );

then a row will be inserted into each table for the single SQL command.

That said, it doesn't look like you have been strict enough when naming your attributes e.g. Table1.id changes name to pod_id in Table2 and id is not unique in your schema.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • I wasn't very explicit, i think, .... fields **id** of both tables (1 & 2) are unique (primary key set) and auto incrementing. Field **pod_id** of table2 is taking value of field **id** of table1 -that i want to do- and in a single query (i mean insert) write data from both sheet to both tables. – Adrian Sep 14 '16 at 11:48
  • @Adrian: OK so having fields names `id` in both tables is no good, rename them so they are consistent e.g. rename `Table1.id` to be `Table1.pod_id`. – onedaywhen Sep 19 '16 at 15:42
0

i found a this...

INSERT INTO [table1] ([data]) 
OUTPUT [inserted].[id], [external_table].[col2] 
INTO [table2] SELECT [col1] 
FROM [external_table]

and on last SELECT ... to do a JOIN between sheets to retrive all the data?

It's working that way?

Adrian
  • 1
  • 1