John Soer

Archive for January, 2012

Importing data into SQL server using the the openrowset command can cause the Collation Conflict when using the imported table against existing tables. This happens because SQL Server selects the Collation that best fit the data being imported. The following import statements allows you to set the Collation when doing a select into using the OpenRowSet

    SELECT * INTO [NewTable]
    FROM
    (
      SELECT
           [Field1] collate database_default as [Field1]
         , [Field2] collate database_default as [Field2]
         , [Field3] collate database_default as [Field3]
      FROM OPENROWSET(
        'Microsoft.ACE.OLEDB.12.0',
        'Text;Database=Folder;HDR=Yes;FORMAT=TabDelimited();',
        'SELECT field1 as Field1, field2 as Field2, field3 as Field3 FROM [FileName]')
    ) as qImport