Define the CSV file and target database details

csv_filename = '' # Replace with your CSV file name target_server = '' # Replace with your target server name target_database = 'd1_sql_s_30' # Replace with your target database name target_table = 'SCCM_DEV_DATA' # Replace with the desired target table name

Define the connection string using SQLAlchemy format

connection_string = f'mssql+pyodbc:///?odbc_connect=DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={target_server};DATABASE={target_database};Trusted_Connection=yes;'

Create an engine with SQLAlchemy

engine = create_engine(connection_string, fast_executemany=True)

Read the CSV data with pandas

df = pd.read_csv(csv_filename)

Check data types and ensure consistency

print("Data types before conversion:") print(df.dtypes)

Convert columns to appropriate types if necessary

for col in df.columns: if 'float' in str(df[col].dtype) or 'int' in str(df[col].dtype): df[col] = pd.to_numeric(df[col], errors='coerce')

Ensure there are no NaNs or invalid values in numeric columns

df.fillna(0, inplace=True)

try:

Use the to_sql method for bulk insert

df.to_sql(target_table, con=engine, index=False, if_exists='replace', method='multi', chunksize=10000)
print(f"Data from CSV has been successfully inserted into '{target_table}'.")

except Exception as e: print(f"Error occurred: {e}")

finally: engine.dispose() print("Connection to target database closed.")

Ayush-projects commented 2 months ago

WITH RandomSelection AS ( SELECT DeviceID, LastHeartbeat, ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowNum, CASE WHEN ABS(CHECKSUM(NEWID())) % 2 = 0 THEN CONVERT(VARCHAR, GETDATE(), 120) -- Today ELSE CONVERT(VARCHAR, DATEADD(DAY, -1, GETDATE()), 120) -- Yesterday END AS NewHeartbeat FROM Devices ) UPDATE RandomSelection SET LastHeartbeat = NewHeartbeat WHERE RowNum <= (SELECT COUNT() 0.9 FROM Devices);