BULK INSERT a string constant into a table

• jhom

  SSC-Addicted

  Points: 443

  More actions

  November 13, 2013 at 12:17 pm

  #285167

  Let's say I have a table

  create table myTable

  (

  data int not null,

  who varchar(100) null

  primary key (data)

  )

  where I want to keep track of who was the last person to insert/update a row.

  Is there a way to have BULK INSERT place a string constant into the "who" column?

  Let's say I have a flat data file containing the following numbers

  1

  22

  333

  4444

  55555

  that I want to BULK INSERT into myTable. The XML format file might be

  <?xml version="1.0"?>

  <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <RECORD>

  <FIELD ID="1" xsi:type="CharTerm" ... />

  </RECORD>

  <ROW>

  <COLUMN NAME="data" SOURCE="1" xsi:type="SQLINT" />

  </ROW>

  </BCPFORMAT>

  but this does not address the "who" field. Is there something like

  <ROW>

  <COLUMN NAME="who" VALUE="bulk insert" xsi:type="SQLVARYCHAR" LENGTH="100" />

  </ROW>

  Thanks,

  John

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 13, 2013 at 12:56 pm

  #1666196

  instead of bulk insert directly into your destination table, use a staging table.

  what i've always done in this case is to bulk insert into a staging table, and then massage teh data in the table if required, and finally perform a separate migration from the staging table to the final table.

  that way you can insert extra columns, join against other tables, and handle your encryption requirements from your other post all in a clean migration.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!