Post reply

BULK Question

  • March 15, 2013 at 7:34 am

    #271661

    I export some data from a query into a text file. Then I create a temporary table and bulk insert the data from the text file.

    Most all is inserted properly - just a few aren't.

    For example (×Ϲ⻪ÓîÆ´ÒôÊäÈë) gets messed up during the bulk insert. It comes like (+ù+Å-¦+ó-+-¬+ô+«+å-¦+Æ+¦+è+ñ+ê+½).

    (×Ϲ⻪ÓîÆ´ÒôÊäÈë) exist in the text file and in the database, but when I check in temporary table it isn't there.

    declare @file sysname;

    SET @file = 'C:\Table.txt'

    SET nocount ON

    CREATE TABLE #tempfile (Column1 varchar(600))

    EXEC ('bulk INSERT #tempfile FROM "' + @file + '"')

    SELECT DISTINCT *

    FROM #tempfile

    WHERE Column1 = '×Ϲ⻪ÓîÆ´ÒôÊäÈë'

    ORDER BY DisplayName0 ASC

    DROP TABLE #tempfile

  • March 15, 2013 at 8:34 am

    #1597523

    try adding the CODEPAGE=RAW argument. This will cause no data conversion to be performed.

    declare @file sysname;

    SET @file = 'C:\temp\test.txt'

    SET nocount ON

    CREATE TABLE #tempfile (Column1 varchar(max))

    EXEC ('bulk INSERT #tempfile FROM "'+@file+'" with (codepage=''RAW'')')

    SELECT DISTINCT *

    FROM #tempfile

    WHERE Column1 = '×Ϲ⻪ÓîÆ´ÒôÊäÈë'

    --ORDER BY DisplayName0 ASC

    DROP TABLE #tempfile

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • March 15, 2013 at 10:48 am

    #1597589

    It is a CODEPAGE issue.

    Now it inserts like "×Ϲ⻪ÓîÆ´ÒôÊäÈÃ"

  • March 15, 2013 at 11:00 am

    #1597593

    I am uncertain why you are still getting incorrect results. What is your server collation?

    select serverproperty('collation')

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • March 15, 2013 at 11:03 am

    #1597595

    SQL_Latin1_General_CP1_CI_AS

  • March 15, 2013 at 11:12 am

    #1597598

    I even tried NVARCHAR

    I get +ù+Å-¦+ó-+-¬+ô+«+å-¦+Æ+¦+è+ñ+ê

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

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