Convert Code from Access (1 Viewer)

[tucker61]

I have the code below in a access query to update the JOb_ID fields that are blank,
This is approx 600,000 rows, and takes forever to run - i have to keep stopping the query.

What do i need to do to convert this, so i can run in SQL Server and not through access ?

Thanks in advance.

UPDATE tblQCIntake INNER JOIN tblQCIntakeDetail ON (tblQCIntake.Entered_By = tblQCIntakeDetail.Enterd_By) AND (tblQCIntake.Input_Date = tblQCIntakeDetail.Input_Date) SET tblQCIntakeDetail.Job_ID = [tblQCIntake].[Job_ID]
WHERE (((tblQCIntakeDetail.Job_ID) Is Null));

 

[Josef P.]

This is a job for ChatGPT & co.

Instruction to ChatGPT:

Convert this access query to TSQL:
UPDATE tblQCIntake INNER JOIN tblQCIntakeDetail ON (tblQCIntake.Entered_By = tblQCIntakeDetail.Enterd_By) AND (tblQCIntake.Input_Date = tblQCIntakeDetail.Input_Date) SET tblQCIntakeDetail.Job_ID = [tblQCIntake].[Job_ID]
WHERE (((tblQCIntakeDetail.Job_ID) Is Null));

Result:

UPDATE td
SET td.Job_ID = ti.Job_ID
FROM tblQCIntakeDetail td
INNER JOIN tblQCIntake ti
   ON td.Entered_By = ti.Entered_By
   AND td.Input_Date = ti.Input_Date
WHERE td.Job_ID IS NULL;

Check: TSQL code is created correctly.

Update syntax: https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16

 

[tucker61]

This is a job for ChatGPT & co.

Instruction to ChatGPT:

Result:

UPDATE td
SET td.Job_ID = ti.Job_ID
FROM tblQCIntakeDetail td
INNER JOIN tblQCIntake ti
   ON td.Entered_By = ti.Entered_By
   AND td.Input_Date = ti.Input_Date
WHERE td.Job_ID IS NULL;

Check: TSQL code is created correctly.

Update syntax: https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16

Perfect - Wish i had asked Hours ago - that took 10 seconds SQL Server

Never Used ChatGPT - Did not know it could convert code.

 

[The_Doc_Man]

Be aware that while it is possible to ask ChatGPT and get a decent-looking answer, it is ALSO possible that you will get back a wrong answer. We have had a few reports here of ChatGPT code that didn't work and would never work. Not saying to avoid ChatGPT. Just set yourself up for a test before committing to running your entire data set on something generated that way.

 

[CJ_London]

there is also this resource here

SQL Converter - AccessUserGroups.org

Convert your Access/Jet SQL queries into T-SQL to run on Microsoft SQL Server. It supports converting the most commonly used functions.

accessusergroups.org

link was sent to me by someone today

result was pretty much the same

UPDATE tblQCIntakeDetail
SET tblQCIntakeDetail.Job_ID = [tblQCIntake].[Job_ID]
FROM tblQCIntake
    INNER JOIN tblQCIntakeDetail ON (tblQCIntake.Entered_By = tblQCIntakeDetail.Enterd_By)
    AND (tblQCIntake.Input_Date = tblQCIntakeDetail.Input_Date)
WHERE (((tblQCIntakeDetail.Job_ID) Is Null))

 

[Uncle Gizmo]

Never Used ChatGPT - Did not know it could convert code.

I started a thread about Chat GPT, it might be of interest.

Not only can it generate code but it can also help you with blogging, writing letters, writing emails. I've even asked it to help me navigate the school system to get my grandson some extra help. This is the thread:-

I'm in Love with ChatGPT

I'm writing a sci-fi story. Last week whilst on holiday I organised the chapters and included a brief summary of each chapter. After my recent success with chatGPT in finding "Fractoluminescence" I was itching to test its abilities once more! I copied my summary in to ChatGPT and asked chatGPT...

www.access-programmers.co.uk

 

[Minty]

It's worth noting that the T-SQL version doesn't need the myriad of brackets that Access uses.

Large recordset update queries in particular are nearly always much better handled directly in SQL Server, I haven't been able to really fathom out why though, but assume its transcational logs/rollback issues?

 

[sonic8]

It's worth noting that the T-SQL version doesn't need the myriad of brackets that Access uses.

Even Access itself doesn't need the myriad of brackets that the Access visual query designer is adding to the queries.

Large recordset update queries in particular are nearly always much better handled directly in SQL Server, I haven't been able to really fathom out why though, but assume its transcational logs/rollback issues?

A huge part of the performance problem with Access queries on linked ODBC tables is covered in my text Microsoft Access - ODBC Linked Tables – Mechanisms and Performance.
With update queries this might be aggravated by Access sending an update statement for every single row it wants to update instead of a single statement for all of them.

 

[Pat Hartman]

I haven't been able to really fathom out why though, but assume its transcational logs/rollback issues?

You will see the difference if you run an Access query vs a passthrough query. Notice how Access asks if you want to add x records, etc but the pass through does not. While all my forms are bound and the majority of my queries are Access querydefs, some action queries are faster as passthrough queries. I have some temp tables that hold data imported from an application and we are not allowed to link directly to the tables so I have no choice but to import the data every morning before the day starts. In this case, I use truncate to get rid of the data and then append to copy the .csv into a table for us to use that day.

 

[Minty]

Because we mainly use Azure SQL backends, almost all our action queries are pass-throughs.
We also use back-end temporary tables to join to when running complex queries to increase response speed on big tables (Millions of records) .
It makes a significant difference.