I need to read through a file containing postgres DML (inserts, updates, deletes) and remove characters to make the DML compatible with SQL Server DML syntax

Question

I need to read through a file containing postgres DML (inserts, updates, deletes) and remove characters to make the DML compatible with SQL Server DML syntax

GaMusicMan

Say Hey Kid

Points: 674
More actions
August 18, 2016 at 3:25 pm

#316316

I have a script file that looks like this: Generated by Devart dbForge Data Compare for PostgreSQL
-- Script was generated by Devart dbForge Data Compare for PostgreSQL, Version 1.0.123.0
-- Product Home Page: http://www.devart.com/dbforge/postgresql/datacompare
-- Script date 8/18/2016 1:59:43 PM
-- Source server version: PostgreSQL 9.1.23, compiled by Visual C++ build 1500, 64-bit
-- Source connection string: User Id=postgres;Host=Localhost;Database=Production;Unicode=True;
-- Target server version: PostgreSQL 9.1.23, compiled by Visual C++ build 1500, 64-bit
-- Target connection string: User Id=postgres;Host=Localhost;Database=Staging;Unicode=True;
-- Run this script against Staging to synchronize it with Production
-- Please backup your target database before running this script
--
-- Inserting data into table "Staging"."Customer"
--
INSERT INTO "Staging"."Customer"("CustID", "CustName", "Address1", "Address2", "City", "State", "Zip") VALUES
(1, E'Lowes ', E'102 Lowes Drive ', E' ', E'Philadelphia ', E'Pa', 15029),
(4, E'Merriwethers ', E'322 Fisher Street ', E' ', E'Philadelphia ', E'Pa', 15655),
(5, E'Happy Harrys ', E'888 Herry Road ', E' ', E'Philadelphia ', E'Pa', 15112),
(7, E'Carolee ', E'102 CompUSA Drive ', E' ', E'Philadelphia ', E'Pa', 15029),
(8, E'Brandsmart USA ', E'88 Brandsmart Drive ', E' ', E'Philadelphia ', E'Pa', 15122);
You will notice that there are characters in the value fields that are preceded by (a space and 'E') Can anyone tell me what code I might write or show me some T-SQL that will read through the .txt file containing the (space and 'E') and replace these 2 characters with a "Space". I need to clean this code so that I can use the file containing the cleanedup DML in SQL Server. Suggestions welcome.Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

GaMusicMan (8/18/2016)
I have a script file that looks like this: Generated by Devart dbForge Data Compare for PostgreSQL
-- Script was generated by Devart dbForge Data Compare for PostgreSQL, Version 1.0.123.0
-- Product Home Page: http://www.devart.com/dbforge/postgresql/datacompare
-- Script date 8/18/2016 1:59:43 PM
-- Source server version: PostgreSQL 9.1.23, compiled by Visual C++ build 1500, 64-bit
-- Source connection string: User Id=postgres;Host=Localhost;Database=Production;Unicode=True;
-- Target server version: PostgreSQL 9.1.23, compiled by Visual C++ build 1500, 64-bit
-- Target connection string: User Id=postgres;Host=Localhost;Database=Staging;Unicode=True;
-- Run this script against Staging to synchronize it with Production
-- Please backup your target database before running this script
--
-- Inserting data into table "Staging"."Customer"
--
INSERT INTO "Staging"."Customer"("CustID", "CustName", "Address1", "Address2", "City", "State", "Zip") VALUES
(1, E'Lowes ', E'102 Lowes Drive ', E' ', E'Philadelphia ', E'Pa', 15029),
(4, E'Merriwethers ', E'322 Fisher Street ', E' ', E'Philadelphia ', E'Pa', 15655),
(5, E'Happy Harrys ', E'888 Herry Road ', E' ', E'Philadelphia ', E'Pa', 15112),
(7, E'Carolee ', E'102 CompUSA Drive ', E' ', E'Philadelphia ', E'Pa', 15029),
(8, E'Brandsmart USA ', E'88 Brandsmart Drive ', E' ', E'Philadelphia ', E'Pa', 15122);
You will notice that there are characters in the value fields that are preceded by (a space and 'E') Can anyone tell me what code I might write or show me some T-SQL that will read through the .txt file containing the (space and 'E') and replace these 2 characters with a "Space". I need to clean this code so that I can use the file containing the cleanedup DML in SQL Server. Suggestions welcome.Thank you.

T-SQL is not a text-processing language. It will not do this cleanly for you.

What's wrong with using Find and Replace in a text editor?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

GaMusicMan Say Hey Kid Points: 674 More actions · Answer 2

I need to read through this .txt file containing the DML code I placed in the post row by row and where ever I encounter (space and E) I want to replace the Space and E with a space. Not sure how to do that with a simple Replace.

GaMusicMan Say Hey Kid Points: 674 More actions · Answer 3

I need to read through .txt files like the sample daily and remove the

(space and E) so that the SSIS execute SQL Task will be able to execute the script contained in the .txt file. Note. I will be renaming the cleaned up .txt file to a .sql extension.

pietlinden SSC Guru Points: 63506 More actions · Answer 4

Just googling around found this discussion on StackOverflow showing how to do what you are asking in PowerShell.

GaMusicMan Say Hey Kid Points: 674 More actions · Answer 5

I dropped a Script Task and wrote C# code to find and replace. Problem solved. Thank you for your input.