Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

BULK INSERT to table with specific columns

Mar 17 2010 3:16AM by Madhivanan   

BULK INSERT is used to insert data to a table from a text file.
Only problem with it is that it is not possible to specify the columns that you want data to be imported.
For example the table has 4 columns whereas there are data for 2 columns in the text file.


Consider the following data

Source data in text file called test.txt (It has first_name and last_name)

Davolio,	Nancy
Fuller,	Andrew
Leverling,	Janet
Peacock,	Margaret
Buchanan,	Steven
Suyama,	Michael
King,	Robert
Callahan,	Laura
Dodsworth,	Anne











Create a table with the following structure

create table bulk_insert_test
(
	employee_id int identity(1,1), 
	first_name varchar(30), 
	last_name varchar(30), 
	address varchar(100)
)


Note that the table has two extra columns employee_id and address

Now this query will fail

BULK INSERT bulk_insert_test
   FROM 'g:\test.txt'
   WITH
     (
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
      )

Becuase the table has four columns and text file has data for only two columns. In this case you can import data to specific columns using the following methods

1 Use a View

Create a view that has only required columns

create view vw_bulk_insert_test
as
select first_name,last_name from bulk_insert_test

Now use BULK INSERT using this view

BULK INSERT vw_bulk_insert_test
   FROM 'g:\test.txt'
   WITH
     (
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
      )

2 Create Format file

8.0
2
1  SQLCHAR  0  30 ","        2  first_name                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  30 ","        3  last_name                 SQL_Latin1_General_Cp437_BIN

Name it as format.txt Now use BULK INSERT using this format file

BULK INSERT bulk_insert_test
   FROM 'g:\test.txt'
with (formatfile = 'g:\format.txt')

3 Use OPENROWSET 

INSERT INTO bulk_insert_test(first_name,last_name)
SELECT 
	* 
FROM 
	OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=g:\', test#txt)

As you see method 3 doesn't require any extra work if the number of columns are different

Tags: t-sql, sql_server, bulk_insert,


Madhivanan
3 · 40% · 12862
7 Readers Liked this
rwillemain Liked this on 11/11/2011 3:48:00 PM
Profile
Madhivanan Liked this on 9/22/2011 7:54:00 AM
Profile · Blog · Facebook · Twitter
Jacob Sebastian Liked this on 11/12/2011 11:45:00 PM
Profile · Blog · Facebook · Twitter
Guru Samy Liked this on 2/25/2012 4:01:00 AM
Profile · Blog
DCS Liked this on 12/15/2011 10:32:00 AM
Profile
sunil20000 Liked this on 9/11/2012 1:57:00 PM
Profile
abhIShek BandI Liked this on 9/13/2012 1:12:00 AM
Profile · Facebook
7
Liked
 
 
0
Refreshed
 
 
0
Incorrect



Submit

9  Comments  

  • Nice article.

    I come across situation where I had ',' as a delimiter inside my field value. e.g. Mike,”456 2nd St, Apt 5"

    You can change list separator from Regional Settings to overcome this issue. More description @ http://tinyurl.com/2v4ndco

    Thanks, Ashish

    commented on Nov 15 2010 9:51AM
    ashishccet
    3015 · 0% · 2
  • Good Post

    commented on Dec 28 2010 3:24AM
    Venkat. V
    430 · 0% · 92
  • very well presented demo !

    commented on Nov 11 2011 3:49PM
    rwillemain
    666 · 0% · 50
  • with the OPENROWSET example, how does ('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=g:\', test#txt) define the selection of text strings ?

    commented on Nov 11 2011 4:02PM
    rwillemain
    666 · 0% · 50
  • Hi Madhivanan,

    Is it possible to capture the csv filename into a field in the table as well.

    Thanks in advance, DCS

    commented on Dec 15 2011 10:33AM
    DCS
    172 · 1% · 285
  • Hi Madhavan,

    Nice Post. I have one question. I have text file where all row data in one line separated by line feed(\n). We have another XML(Schema) file which helps to pick up fields based on positions.

    For Ex my flat file is like below

    "Steve Hurdle 233 John's Drive Houston TX john3 muddle 568 deer's Drive Lousiville KY".

    Now in my xml files tells like Firstname starting position 1 and length of 10, Lastname starting position 11 and length of 10. 1 10 11 10

    21 20 .....

    So how can I use bulk insert in above scenario.

    Suresh Pedireddi

    commented on Jan 27 2012 9:11AM
    hellosuresh
    246 · 1% · 183
  • Hi Madhivanan,

    The view trick is interesting and could be applied to others situations where there is a mismatch between columns values inserted and columns available.

    Thanks for sharing it.

    Maurice Pelchat

    commented on Sep 12 2012 12:16PM
    Maurice Pelchat
    1372 · 0% · 16
  • Good Post

    Thanks for sharing

    commented on Sep 12 2012 10:19PM
    Jagdish Ilasariya
    2009 · 0% · 8
  • Very useful post...

    Thanks 4 sharing..

    commented on Sep 13 2012 1:59AM
    Thina
    484 · 0% · 79

Your Comment


Sign Up or Login to post a comment.

"BULK INSERT to table with specific columns" rated 5 out of 5 by 7 readers
BULK INSERT to table with specific columns , 5.0 out of 5 based on 7 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]