What is the proper syntax for specifying codepage or encoding when using Powershell, SQL Server, and SQLBULKCOPY?

edit

When using PowerShell, you can specify the codepage with the ‘-Encoding’ parameter when reading or writing files. For example, to read a file in encoded as UTF-8, you would use:

Get-Content -Path "C:\Path\To\File.txt" -Encoding UTF8

For SQL Server, you can specify the encoding for a particular column or table by setting the ‘COLLATE’ property to the appropriate encoding. For example, to create a new table with a column using UTF-8 encoding, you would use:

CREATE TABLE [dbo].[MyTable] (
    [MyColumn] VARCHAR(50) COLLATE utf8_general_ci
)

Finally, when using the SQLBulkCopy class in .NET, you can specify the encoding by setting the ‘DestinationTableName’ property to include the appropriate ‘CharacterSet’ value. For example, to bulk copy data to a table using UTF-8 encoding, you would use:

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";
bulkCopy.DestinationTableName += " WITH (DATAFILETYPE = 'char', "
bulkCopy.DestinationTableName += "      CHARACTERSET = 'utf-8')";
bulkCopy.WriteToServer(data);