Not sure if anyone has run into this problem before and I am going a little bit crazy.
I am exporting a table from SQL Server 2014 using BCP to a tab delimited text file and then uploading the file to PostgreSQL 9.5 using COPY.
exec master..xp_cmdshell 'bcp tempdb.dbo.##MyTable out "C:\BCPFiles\MyTableFile.txt" -c -t -T'
This works fine.
create temp table mytable (col1 int, col2 varchar(100), col3 varchar(100)); COPY mytable FROM '/tmp/MyTableFile.txt' WITH (FORMAT text); ERROR: invalid input syntax for integer: "1 text1 text2"
It looks to me like COPY doesn't recognize the tab, even though when I view the file it is tab delimited. There are no NULL values in this file btw.
CSV is not a good option for me because of the text has commas in it and from what I am reading PG doesn't like double quotes