I sporadically sync data from a small set of tables in production. I cannot access the production machine directly, so historically
- I submit "select * from dbo.source",
- DBA copies from Citrix, pastes locally the result as tab-delimited.txt
- I import tab-delimited.txt into Excel
- I use Excel formulas to concat an INSERT / UPDATE script, one per record
- I run the result on DEV / QA
As long and tedious as that looks, it skips the most arduous step - escaping all the ' or control characters to avoid syntax errors.
I remember, once upon a decade or two ago, that one could select cast(Column as varbinary(255)) as Column, then INSERT / UPDATE the binary version of the data and SQL Server would automagically convert to the underlying datatype. However, for the life of me, I cannot locate any of the tutorials I once used.
Is this still the most convenient way to sync data when I MUST convert the table to text for transport? If there's something better, what is it? If it's still a decent approach, can anybody remember the name this approach was given so I can seek out a refresher tutorial?