none
BULK INSERT ?? RRS feed

  • Question

  • I have tried to figure out how make a bulk insert. Could you give an example with a line form a text input file, with a geography data type, a string and a real?

    This workaround works:

    table test
    {
    id int,
    shape nvarchar(4000)
    }

    table geo
    {
    id int,
    shape geography
    }

    bulk insert test form "ff.txt"

    insert into geo (id, shape) select id, shape from test

    The trick only work when WKT is below 4000 characters, because you can't do it with ntext.
    But it would a lot easier to do it directly.




    How about geography STGeomFromText() is it dropped?

    Regards Anders
    Tuesday, November 27, 2007 2:06 PM

Answers

  • Hi Anders,

     

    The only change you need to make to support WKT greater than 4000 characters is to declare the test.shape column as nvarchar(max). 

     

    As you noticed, you can't bulk insert from WKT directly to a geography column as that requires doing a conversion.  In order to use bulk insert with the types directly, the file needs to contain the raw binary values of the types.  This will work if you use bcp to output to a file, then bulk insert to load that file that contains the binary data into a table instead.

    Tuesday, November 27, 2007 7:59 PM
    Moderator