How to Bulk insert

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  November 30, 2010 at 10:08 pm

  #229699

  I have following text file

  Ap1236

  MH5698

  TM2136

  I have following table

  CREATE TABLE Bulk_Insert_Test

  (

  State nvarchar(50)

  ,Number nvarchar(50)

  )

  my requirement is while bulk Insert from text file to table

  I need to get following records.

  State Number

  AP 1236

  MH 5698

  TM 2136

  What can I use for Field Terminator and row Terminator.

  can any one give solution please.

  Thanks,

  IRK

• WayneS

  SSC Guru

  Points: 95461

  More actions

  December 1, 2010 at 7:12 am

  #1257336

  For this, I would bulk insert into a one column staging table (you should use a staging table anyway to do validations on the data before inserting into the permanent table).

  Then use left(col1,2) and substring(col1,3,50) to put the data into the separate fields in the permanent table.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2