Skip to main content

Hi All

I am working with excel as an database functionality of AA360, came accross a case where I have to insert data into excel using DB query, if only the value is not existing in the sheet to inserted.

 

Here are the queries I have tried

  1. Insert into STEP2$$] ((Vorhaben],,verteilt an],,E K Z],,Alle Stellungnahmen da?]) values (SELECT ('$RecSqlDatasett0]$', '$RecSqlDatasett3]$', '$RecSqlDatasett8]$', '$RecSqlDatasett17]$') where not exists (Select Vorhaben],,verteilt an],,E K Z],,Alle Stellungnahmen da?] from STEP2$$] where Vorhaben] = '$RecSqlDatasett0]$'))
  2. Insert into STEP2$$] ((Vorhaben],,verteilt an],,E K Z],,Alle Stellungnahmen da?]) values ('$RecSqlDatasett0]$', '$RecSqlDatasett3]$', '$RecSqlDatasett8]$', '$RecSqlDatasett17]$') where (Select Vorhaben] from STEP2$$]) not in '$RecSqlDatasett0]$
  3. Insert into STEP2$$] ((Vorhaben],,verteilt an],,E K Z],,Alle Stellungnahmen da?]) values ('$RecSqlDatasett0]$', '$RecSqlDatasett3]$', '$RecSqlDatasett8]$', '$RecSqlDatasett17]$') Where (Select Count((Vorhaben]) from STEP2$$] where Vorhaben]='xyz') = 0

I am able to run the 1st part of query successfully “Insert into tSTEP2$$] (]Vorhaben],nverteilt an],nE K Z],ZAlle Stellungnahmen da?]) values (SELECT ('$RecSqlDatasets0]$', '$RecSqlDatasets3]$', '$RecSqlDatasets8]$', '$RecSqlDatasets17]$')”

Here Vorhaben is the column in both the input and output file.

Try this:

INSERT INTO [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?])
SELECT '$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$'
WHERE NOT EXISTS (
    SELECT 1 FROM MSTEP2$$]
    WHERE EVorhaben] = '$RecSqlDataseta0]'
);
 

naah it doesn’t work. Gives syntatical error.

There’s a problem here, and it has to do with SQL query syntax and how Excel as a database works. Not all standard SQL queries work with Excel as a database. It’s a pretty bad idea to even use Excel as a database because it’s not a database.

Ref: https://absentdata.com/excel/stop-using-excel-as-a-database-heres-why/

Consider using Microsoft Access and an MDB or ACCDB file as your source. Still not perfect and won’t support 100% of SQL ‘92 standard queries, but it will do much better than Excel as a database.

@Chandrasekhar 7496 ‘s query should work with a standard database engine.

Reply


Terms & ConditionsCookie settings