What is the most efficient way to write an SQL statement?

Question

What is the most efficient way to write an SQL statement?

asked

newbie 81

HI, teams,

In SQL in Excel or SQL in Access,update statements are used to update multiple data sets in batches.
If table A and table B have the same structure, as follows:ID field, data field 1, data field 2, data field N

Where, the ID field primary key (set index in access). The data field has N columns,N is greater than 2 and less than 20
Suppose that table A now has 100,000 entries;There are 50,000 pieces of data in table B, 10,000 of which are new ID fields that table A does not have, 20,000 pieces of which are existing in table A but at least one corresponding data field has changed, and 20,000 pieces of which are existing in table A but all the data fields have not changed at all.

Thanks！

Yitzhak Khabinsky 19,846 Reputation points

2022-06-19T14:19:43.873+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2022-06-19T16:45:33.53+00:00

I can only echo what Yitzhak says. The question as it stands is not very easy to understand.

In addition, if your question is about Access, you should add a tag for Access. Right now you have only included the tag sql-server-transact-sql. What might be a good answer on SQL Server, may not work equally well on Access - and vice versa.

Yitzhak Khabinsky 19,846 Reputation points

2022-06-19T14:19:43.873+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2022-06-19T16:45:33.53+00:00

I can only echo what Yitzhak says. The question as it stands is not very easy to understand.

In addition, if your question is about Access, you should add a tag for Access. Right now you have only included the tag sql-server-transact-sql. What might be a good answer on SQL Server, may not work equally well on Access - and vice versa.

Answer 1

answered

Bert Zhou-msft 3,381

Hi,@newbie

From your description alone, it seems that the demand is not clear，If you are using SQL server, have you tried merge into, try to design code like：

 INSERT tbl_A (col, col2)  
SELECT col, col2  
FROM tbl_B  
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

If it doesn't work, tag access for more precise help.

Bert Zhou

newbie 81 Reputation points

2022-06-20T11:38:57.1+00:00

Thank you for your reply. I have solved this problem.

What is the most efficient way to write an SQL statement?

0 additional answers

Activity