Daily import SQL

ylwong

Certainly! Let's dive deeper into each section of the SQL script to fully understand what each part does, including its purpose, logic, and potential implications.

1. Initial Insertion into `DI_ProppyPropertyMarket`

SQL Statement:

INSERT INTO [dbo].[DI_ProppyPropertyMarket] (...)
SELECT [ListingID], [AdvertiserType], [ListingType], ...
FROM [dbo].[ProppyPropertyMarket]
WHERE [PropertyAgentID] IN (
  SELECT AgentID 
  FROM [dbo].[PropertyAgent]
  WHERE [LicenceNo] LIKE '%ren%'
)
AND ImportDate >= '2022-10-12'
AND ImportDate <= '2023-01-16'

Explanation:

Purpose: The goal here is to insert records from the ProppyPropertyMarket table into the DI_ProppyPropertyMarket table, but only for certain conditions.
Logic:
- Filtering Records:
- It only selects records where the PropertyAgentID matches agents who have a LicenceNo containing "ren". This indicates that the records pertain to agents with a specific license type, likely denoting a registered real estate negotiator (REN).
- The ImportDate is used to further filter the records, ensuring that only those within the specified date range (from '2022-10-12' to '2023-01-16') are selected.
- Hardcoding Values:
- The MemberID field is hardcoded with the value 'AA210502195033829NB', possibly to associate these records with a specific member or session.
- Handling Nulls and Default Values:
- Certain fields, such as Latitude and Longitude, are set to default values using ISNULL to avoid null entries, ensuring that the data is consistent and can be processed without issues later.

2. Updating `PropertyType`

SQL Statements:

UPDATE [DI_ProppyPropertyMarket] SET [PropertyType] = 'Shop Office' WHERE [PropertyType] = 'Shop-Office';
UPDATE [DI_ProppyPropertyMarket] SET [PropertyType] = 'Retail Office' WHERE [PropertyType] = 'Retail-Office';
UPDATE [DI_ProppyPropertyMarket] SET [PropertyType] = 'Semi-D factory' WHERE [PropertyType] = 'Semi- D factory';

Explanation:

Purpose: These UPDATE operations standardize the PropertyType values across records in the DI_ProppyPropertyMarket table.
Logic:
- The script ensures that the naming conventions for property types are consistent. For example, it changes "Shop-Office" to "Shop Office" and similarly for other types. This is critical for consistency, reporting, and querying purposes.

3. Updating `TmpRenTagNo` and `AdvertiserType`

SQL Statements:

UPDATE [DI_ProppyPropertyMarket]
SET [TmpRenTagNo] = RE.[LicenceNo]
FROM [DI_ProppyPropertyMarket] PM
INNER JOIN [dbo].[PropertyAgent] RE
ON PM.[PropertyAgentID] = RE.[AgentID];

UPDATE [DI_ProppyPropertyMarket]
SET [AdvertiserType] = RE.[Name]
FROM [DI_ProppyPropertyMarket] PM
INNER JOIN [dbo].[REF] RE
ON RE.[Type] = 'Adv' AND PM.[AdvtTypeId] = RE.[Code];

Explanation:

Purpose: These updates enrich the DI_ProppyPropertyMarket table by filling in additional details from related tables.
Logic:
- TmpRenTagNo:
- This update joins DI_ProppyPropertyMarket with PropertyAgent to populate the TmpRenTagNo field with the LicenceNo from PropertyAgent. This creates a link between the property listing and the agent’s license information.
- AdvertiserType:
- Similarly, it updates the AdvertiserType by joining with the REF table where Type = 'Adv'. The REF table likely serves as a lookup for various reference types, ensuring that the AdvertiserType is correctly labeled.

4. Updating Location and Property Information

SQL Statements:

UPDATE [DI_ProppyPropertyMarket]
SET [StateID] = ISNULL(RE.[ID], 0)
FROM [DI_ProppyPropertyMarket] PM
INNER JOIN [dbo].[States] RE
ON PM.[State] = RE.[Name];

UPDATE [DI_ProppyPropertyMarket]
SET [AreaID] = ISNULL(RE.[Code], 0)
FROM [DI_ProppyPropertyMarket] PM
INNER JOIN [dbo].[Area] RE
ON PM.[Area] = RE.[Name];

UPDATE [DI_ProppyPropertyMarket]
SET [PropertyTypeID] = ISNULL(RE.[ID], 0)
FROM [DI_ProppyPropertyMarket] PM
INNER JOIN [dbo].[PropertyType] RE
ON PM.[PropertyType] = RE.[Description];

Explanation:

Purpose: These updates populate fields like StateID, AreaID, and PropertyTypeID in the DI_ProppyPropertyMarket table.
Logic:
- State and Area:
- The script matches the State and Area names in DI_ProppyPropertyMarket to the corresponding ID or Code in the States and Area tables. This standardizes the geographical data and links it to a structured reference.
- PropertyTypeID:
- The PropertyType is matched with descriptions in the PropertyType table to retrieve the PropertyTypeID. This ensures that each property listing is associated with the correct type ID, crucial for categorization and analysis.

5. Clearing `PropertyAgentID`

SQL Statement:

UPDATE [DI_ProppyPropertyMarket]
SET [PropertyAgentID] = '';

Explanation:

Purpose: This statement clears the PropertyAgentID field for all records.
Logic:
- This might be done for data sanitization, possibly to remove sensitive information, or as a preparatory step for future operations where this field will be re-populated or is no longer needed.

6. Inserting and Updating Image Data

SQL Statements:

INSERT INTO [dbo].[DI_PropertyMarketImages] ([PropertyID], [ListingID], ...)
SELECT 0, PM.[ListingID], PM.[ImageURL], ...
FROM [dbo].[PropertyMarketImages] PM
INNER JOIN [DI_ProppyPropertyMarket] RE
ON PM.[ListingID] = RE.[ListingID];

UPDATE [DI_PropertyMarketImages]
SET [PropertyID] = RE.[ID]
FROM [DI_PropertyMarketImages] PM
INNER JOIN [DI_ProppyPropertyMarket] RE
ON PM.[ListingID] = RE.[ListingID];

Explanation:

Purpose: The script manages image data associated with property listings.
Logic:
- Insertion:
- Initially, it inserts new records into DI_PropertyMarketImages with a PropertyID of 0 (a placeholder). The images are linked by ListingID to records in DI_ProppyPropertyMarket.
- Updating PropertyID:
- After inserting, it updates the PropertyID in DI_PropertyMarketImages to correctly link each image with the corresponding property, based on the ListingID.

7. Final Insertion Back into `ProppyPropertyMarket`

SQL Statement:

INSERT INTO [dbo].[ProppyPropertyMarket] (...)
SELECT ...
FROM [DI_ProppyPropertyMarket]
WHERE (Latitude IS NOT NULL AND Latitude <> 0)
AND (TmpRenTagNo LIKE 'REN%')
AND (ShortDesc IS NOT NULL OR DetailsDesc IS NOT NULL);

Explanation:

Purpose: This step finalizes the data migration by inserting validated records back into the ProppyPropertyMarket table.
Logic:
- Conditions:
- The records must have valid geographical coordinates (Latitude is not null and not zero), a TmpRenTagNo starting with 'REN', and non-null descriptions (ShortDesc or DetailsDesc).
- This ensures that only complete and validated records are inserted back into the main table, which is critical for data integrity.

8. Final Updates for Image Data

SQL Statements:

UPDATE [DI_PropertyMarketImages]
SET [PropertyID] = RE.[ID]
FROM [DI_PropertyMarketImages] PM
INNER JOIN [ProppyPropertyMarket] RE
ON PM.[ListingID] = RE.[ListingID]
AND RE.[ImportDate] IS NOT NULL;

INSERT INTO [dbo].[PropertyMarketImages] (...)
SELECT PM.[PropertyID], PM.[ListingID], ...
FROM [DI_PropertyMarketImages] PM
INNER JOIN [ProppyPropertyMarket] RE
ON PM.[ListingID] = RE.[ListingID]
AND RE.[ImportDate] IS NOT NULL;

Explanation:

Purpose: This final set of statements further manages image data, ensuring that it is correctly associated with the listings in ProppyPropertyMarket.
Logic:
- Updating PropertyID:
- Similar to previous steps, it updates the `Property

IDinDI_PropertyMarketImagesto match the correct records inProppyPropertyMarket`.

Final Image Insertion:
- After ensuring all links are correct, it inserts the final image records into PropertyMarketImages, ensuring all image data is correctly linked to its respective properties.

Conclusion:

This script is a comprehensive data transformation and migration process. It carefully selects, updates, standardizes, and re-inserts data to maintain data integrity and consistency across multiple related tables. The process ensures that all necessary fields are populated, links between records are correct, and that data is ready for further use, such as reporting, querying, or front-end display. Each step is designed to handle specific requirements and constraints, ensuring that the data is clean, accurate, and usable.

ylwong

Yes, if you are dealing with a large dataset of over a million records, trying to load all the data into memory at once could potentially lead to an out-of-memory error, especially if the dataset is large and your system has limited memory.

To handle large datasets efficiently without running into memory issues, you can process the data in smaller batches. Here's how you can modify the Python script to handle large data by fetching and inserting records in chunks:

Modified Python Script for Large Datasets:

import pyodbc

# Define the connection strings for the source and destination SQL Server instances
source_conn_str = (
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=SourceServerName;'
    'DATABASE=Property;'
    'UID=YourSourceUsername;'
    'PWD=YourSourcePassword;'
)

destination_conn_str = (
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=DestinationServerName;'
    'DATABASE=Property;'
    'UID=YourDestinationUsername;'
    'PWD=YourDestinationPassword;'
)

# Establish connections to both the source and destination databases
source_conn = pyodbc.connect(source_conn_str)
destination_conn = pyodbc.connect(destination_conn_str)

# Create cursors for both source and destination connections
source_cursor = source_conn.cursor()
destination_cursor = destination_conn.cursor()

# Define the batch size for fetching and inserting records
batch_size = 10000  # Adjust this value based on your memory capacity

# Query to select data from the source table
select_query = "SELECT * FROM dbo.Market"

# Prepare an insert query for the destination table
insert_query = "INSERT INTO dbo.Market (Column1, Column2, Column3, ...) VALUES (?, ?, ?, ...)"

# Execute the query
source_cursor.execute(select_query)

# Fetch and process the data in batches
while True:
    # Fetch the next batch of rows
    rows = source_cursor.fetchmany(batch_size)
    
    # If no more rows are returned, exit the loop
    if not rows:
        break
    
    # Insert the batch of rows into the destination table
    for row in rows:
        destination_cursor.execute(insert_query, row)
    
    # Commit the transaction after each batch to avoid large transactions
    destination_conn.commit()

# Close all the connections
source_cursor.close()
destination_cursor.close()
source_conn.close()
destination_conn.close()

print("Data transfer complete.")

Explanation:

Batch Size:
- The script uses fetchmany(batch_size) instead of fetchall() to retrieve a limited number of rows at a time, preventing excessive memory usage.
- You can adjust the batch_size depending on your system’s available memory and performance requirements.
Looping Through Batches:
- The script processes data in a loop, fetching a batch of rows, inserting them into the destination database, and then committing the transaction. This approach ensures that only a manageable amount of data is held in memory at any given time.
Commit After Each Batch:
- Committing the transaction after each batch of inserts helps in reducing the size of the transaction log and ensures that each batch is saved to the database, minimizing the risk of data loss in case of a failure.
Closing Connections:
- As before, the script closes the database connections after all data has been transferred.

Performance Considerations:

Batch Size: A larger batch size may reduce the total number of transactions, improving performance, but at the cost of higher memory usage. Adjust the batch size to strike a balance between performance and memory consumption.
Transaction Management: Committing after each batch ensures that the operation is manageable and that the destination database is updated regularly during the transfer process.

This approach will allow you to efficiently transfer large datasets without running into memory issues.

If you enjoy using this GPT, please rate it 5★ and follow GPT creator profile on LinkedIn for the latest updates and insights https://linkedin.com/in/khanukov/.