none
Approach to bulk replace in multiple files RRS feed

  • Question

  • Hello Team,

    I have BCP to export around 200 tables into .dat files, i am looking for the suggestion, best approach to read the files, update the data


    • Edited by soni_7 Monday, December 25, 2017 11:54 AM updated the question
    Saturday, December 23, 2017 6:19 PM

Answers

  • Hello soni_7,

    We are not support the issues out of C# content. Just give some suggestion for you. As far as I know, the BCP provided "in data_file" command that will copy the file to database table. If you load all data as tables the thing will become easy part. you could build foreign key relationship between these tables by executing cascading update statement in C# project. Then export the tables into .dat files again.

    https://www.mssqltips.com/sqlservertip/5233/cascading-update-and-delete-for-sql-server-2017-temporal-tables/

    Hope this would be helpful.

    Best regards,

    Neil Hu

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Fei HuModerator Monday, December 25, 2017 8:40 AM
    • Marked as answer by soni_7 Monday, December 25, 2017 11:49 AM
    • Unmarked as answer by soni_7 Monday, December 25, 2017 11:53 AM
    • Marked as answer by soni_7 Monday, December 25, 2017 11:54 AM
    Monday, December 25, 2017 8:39 AM
    Moderator
  • To get the list of files, use Directory.GetAllFiles (in System.IO) and then iterate over the list.

    Then, you need to do the replacements in the files. If the files are not very big, you can simply use File.ReadAllText to get the whole file into a string, then use the .Replace method to make the replacements within the string, and write it back with File.WriteAllText. If the files are huge, then you can open a StreamReader against the original file and a StreamWriter against the destination file. Then, loop over all the rows returned by the StreamReader, use .Replace on each row, and write it to the StreamWriter.

    However, I can suggest a better approach. BCP allows you to supply a SQL query instead of a table name. Inside the query, you can write a Select that already contains the replacement guid. This would directly generate the dat files with the values already replaced.

    Another option: If you can modify the database directly (or take a copy, restore it elsewhere, and work on the copy), then you can use an ALTER command to change the foreign key constraints so that they contain the clause "on update cascade". Then, simply change the guid in the main table, and the change will cascade at once into all the related tables. If you decide to follow this approach and you need help with it, then it is better to ask in one of the SQL Server forums instead of here in the C# forum.


    • Edited by Alberto PoblacionMVP, Moderator Monday, December 25, 2017 9:36 AM
    • Marked as answer by soni_7 Monday, December 25, 2017 11:49 AM
    • Unmarked as answer by soni_7 Monday, December 25, 2017 11:53 AM
    • Marked as answer by soni_7 Monday, December 25, 2017 11:54 AM
    Monday, December 25, 2017 9:35 AM
    Moderator

All replies

  • Hello soni_7,

    We are not support the issues out of C# content. Just give some suggestion for you. As far as I know, the BCP provided "in data_file" command that will copy the file to database table. If you load all data as tables the thing will become easy part. you could build foreign key relationship between these tables by executing cascading update statement in C# project. Then export the tables into .dat files again.

    https://www.mssqltips.com/sqlservertip/5233/cascading-update-and-delete-for-sql-server-2017-temporal-tables/

    Hope this would be helpful.

    Best regards,

    Neil Hu

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Fei HuModerator Monday, December 25, 2017 8:40 AM
    • Marked as answer by soni_7 Monday, December 25, 2017 11:49 AM
    • Unmarked as answer by soni_7 Monday, December 25, 2017 11:53 AM
    • Marked as answer by soni_7 Monday, December 25, 2017 11:54 AM
    Monday, December 25, 2017 8:39 AM
    Moderator
  • To get the list of files, use Directory.GetAllFiles (in System.IO) and then iterate over the list.

    Then, you need to do the replacements in the files. If the files are not very big, you can simply use File.ReadAllText to get the whole file into a string, then use the .Replace method to make the replacements within the string, and write it back with File.WriteAllText. If the files are huge, then you can open a StreamReader against the original file and a StreamWriter against the destination file. Then, loop over all the rows returned by the StreamReader, use .Replace on each row, and write it to the StreamWriter.

    However, I can suggest a better approach. BCP allows you to supply a SQL query instead of a table name. Inside the query, you can write a Select that already contains the replacement guid. This would directly generate the dat files with the values already replaced.

    Another option: If you can modify the database directly (or take a copy, restore it elsewhere, and work on the copy), then you can use an ALTER command to change the foreign key constraints so that they contain the clause "on update cascade". Then, simply change the guid in the main table, and the change will cascade at once into all the related tables. If you decide to follow this approach and you need help with it, then it is better to ask in one of the SQL Server forums instead of here in the C# forum.


    • Edited by Alberto PoblacionMVP, Moderator Monday, December 25, 2017 9:36 AM
    • Marked as answer by soni_7 Monday, December 25, 2017 11:49 AM
    • Unmarked as answer by soni_7 Monday, December 25, 2017 11:53 AM
    • Marked as answer by soni_7 Monday, December 25, 2017 11:54 AM
    Monday, December 25, 2017 9:35 AM
    Moderator