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 ('$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$') where not exists (Select [Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?] from [STEP2$$] where [Vorhaben] = '$RecSqlDataset[0]$'))
  2. Insert into [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?]) values ('$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$') where (Select [Vorhaben] from [STEP2$$]) not in '$RecSqlDataset[0]$
  3. Insert into [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?]) values ('$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$') Where (Select Count([Vorhaben]) from [STEP2$$] where [Vorhaben]='xyz') = 0

I am able to run the 1st part of query successfully “Insert into [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?]) values (SELECT ('$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$')”

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 [STEP2$$]
    WHERE [Vorhaben] = '$RecSqlDataset[0]'
);
 

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.

Terms & ConditionsCookie settingsAccessibility statement