Importing csv file into SQL Server - best approach

The Import/Export wizard creates a SSIS package in background and execute it.

You can save that package at the end of the wizard instead of executing it immediately, that you can reuse the package, also on prod.

Or you create manually a package, that's more flexible:

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

A SSIS package can also be executed from an Agent job, if you want to automize the process.