如何为CSV生成的INSERT语句追加固定值列并批量插入SQL Server？

Hey there! Let's work through this problem—you're halfway there since you've already added the column name to your cols tuple. The key missing piece is appending that fixed value to every data row you read from your CSV files, and making sure the SQL statement is set up correctly for this. Here's a robust, efficient solution tailored for your use case (handling hundreds of different text files and inserting into SQL Server):

Step 1: Define Your Fixed Column & Value #

First, set up the static column name and value you want to add to every row. Adjust these to match your actual column name and data type:

# Customize these to fit your table schema
fixed_col_name = "SourceFileIdentifier"  # Example column name
fixed_value = full_path  # Could be a hardcoded string, number, or dynamic value like the file path

Step 2: Update Column List & Build Safe SQL Query #

Modify your existing code to add the fixed column to your column list, then construct a parameterized INSERT query (critical to avoid SQL injection and syntax errors):

import csv
import pyodbc  # Assuming you're using pyodbc for SQL Server connectivity

# ... (your existing code for getting my_encoding, my_delim, my_quote, my_table_name, full_path)

with open(full_path, 'r', encoding=my_encoding) as f:
    reader = csv.reader(f, delimiter=my_delim, quotechar=my_quote)
    cols = next(reader)
    
    # Append the fixed column name to your existing columns
    cols.append(fixed_col_name)
    
    # Format column names with SQL Server brackets to handle special characters/spaces
    quoted_columns = [f"[{col}]" for col in cols]
    column_str = ", ".join(quoted_columns)
    
    # Create parameter placeholders (SQL Server uses ? for positional parameters)
    placeholders = ", ".join(["?"] * len(cols))
    
    # Build the final parameterized INSERT query
    insert_query = f"INSERT INTO {my_table_name} ({column_str}) VALUES ({placeholders})"

Step 3: Prepare Data Rows with Fixed Value #

Loop through each row from the CSV, append the fixed value to every row, and collect all rows for bulk insertion (way more efficient than inserting one row at a time):

# Collect all data rows with the fixed value appended
    data_rows = []
    for row in reader:
        # Append your fixed value to the end of each row
        row.append(fixed_value)
        data_rows.append(row)

Step 4: Execute Bulk Insert #

Use executemany to insert all rows in one go—this is optimized for performance, especially when dealing with large files or hundreds of files:

# Assuming you have an existing database connection (adjust this to your setup)
    with pyodbc.connect("your_sql_server_connection_string") as conn:
        with conn.cursor() as cursor:
            # Bulk insert all rows
            cursor.executemany(insert_query, data_rows)
            conn.commit()

Key Notes for Your Use Case #

• Handling Different File Formats: Since you're dealing with hundreds of varying text files, this approach works as long as your csv.reader parameters (my_delim, my_quote, my_encoding) are correctly configured for each file. You can add logic to detect these parameters dynamically if needed.
• Data Type Compatibility: Ensure your fixed_value matches the data type of the new column in SQL Server (e.g., use an integer if the column is INT, a string for VARCHAR, etc.).
• Error Handling: Consider adding try/except blocks to catch issues like mismatched column counts or database errors, especially when processing hundreds of files. For example:

  try:
      cursor.executemany(insert_query, data_rows)
      conn.commit()
  except Exception as e:
      print(f"Error inserting file {full_path}: {str(e)}")
      conn.rollback()
  

内容的提问来源于stack exchange，提问作者hecramsey