Bulk Insert field mapping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bulk Insert field mapping

Author

Topic

salmab
Starting Member

3 Posts

Posted - 2013-02-10 : 20:13:22

Hi,
I'm trying to do a bulk insert.

Here is my sql script

GO
Use test
DROP TABLE PDTEST
create table pdtest
(
CustID INT IDENTITY(1,1),
work_phone_extension varchar(5),
residential_postal_or_zip_code varchar(30),
residential_street_address_line_1 varchar(30),
residence_phone varchar(17),
work_phone varchar(17),
name_part varchar(30),
mailing_city varchar(20),
mailing_postal_or_zip_code varchar(30),
mailing_street_address_line_2 varchar(30),
mailing_street_address_line_1 varchar(30),
cell_phone varchar(17),
residential_city varchar(20),
residential_country_and_province_or_state varchar(10),
mailing_country_and_province_or_state varchar(10)
)
BULK INSERT pdtest
FROM 'C:\Python27\Scripts\pd.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS,
FIRSTROW = 2
)
GO

Here is my textfile row
,91234,,,,WOLFIE HOWLETT,416-,Toronto,,,,,Toronto,CA_ON,CA_ON

All my columns are messed up.The name part should have Wolfie Howlett.It has 416- and then the work phone extension should be null but it has got value 91234 and residential_postal_or_zip_code is null which is wrong.

The field mapping is wrong here.What should I do to correct it?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-10 : 21:44:42

quote:
All my columns are messed up.The name part should have Wolfie Howlett.It has 416- and then the work phone extension should be null but it has got value 91234 and residential_postal_or_zip_code is null which is wrong.

Try putting a comma at the beginning of each row

,,91234,,,,WOLFIE HOWLETT,416-,Toronto,,,,,Toronto,CA_ON,CA_ON

If that is not possible, or if that does not work, you would need to use a format file. There are examples of format files and description about how to create them here: http://msdn.microsoft.com/en-us/library/ms178129.aspx

Another alternative that you might want to consider is to use SSIS or the simple Import/Export Wizard. That will let you map the columns easily via a helpful dialog.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 00:28:53

in any case your metedata ie number of columns and types have to be fixed. you can have an intermidiate , missing and expect it to pick up next set of columns correctly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources