How to insert data from a .csv file and join it to two other tables and create an insert into the two tables script.

MR BILL 256 Reputation points
2020-12-24T14:50:43.84+00:00

I have a .csv that has 4 columns that I need to insert data from two of those columns into two different tables. Two of those columns I need to join to two different tables to do insert statements into the two tables. Anyone know how to do this?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,706 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2020-12-24T15:52:02.887+00:00

    The simplest way to do what you describe it to import the CSV into a "staging" table, and then write TSQL to do what you need to do.

    0 comments
  2. Erland Sommarskog 101K Reputation points MVP
    2020-12-24T16:31:57.787+00:00

    Yes, as Tom says, for a solution without SSIS (which I don't know myself), you need to import the file into a table, and then work from the table.

    If the file is located so that you can access it from SQL Server, you can use the BULK INSERT command.

    If it is not, you may be able to import it with the command-line utility BCP.

    We also need which version of SQL Server you are using. What's the output of "SELECT @@version"?

  3. Monalv-MSFT 5,891 Reputation points
    2020-12-25T06:47:53.903+00:00

    Hi @MR BILL ,

    1. Could you please share the example data in your .csv file and your desired output data?
    2. May I know if you installed the SQL Server Data Tools (SSDT) for Visual Studio ?
      If so, please create new Integration Services project and package.
      51188-downloadssdtwithssis.png
    3. Then we can drag a Data Flow Task in ssis package. We can use Flat File Source, Multicast Transformation and OLEDB Destination in Data Flow Task to load data from a .csv file to two sql tables.
      51200-cf.png
      51177-df.png
      51204-flatfilesource.png
      51157-table1.png 51158-re1.png
      51205-table2.png 51159-re2.png

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?