Bulk Insert with Format File. - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Bulk Insert with Format File.

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2009-01-08 : 04:30:18

Hi,

I have a table with the following structure:
id varchar no 10
name varchar no 30
id1 varchar no 10
id2 varchar no 10
ID3 varchar no 10

I have a data file as below:
1foo520031101
3bla720031101
5foo220031104

Now I wanted to use bulk insert to insert this data(fixed length) into the table.

For the same I have used the format file :

8.0
4
1 SQLCHAR 0 1 "" 1 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "" 2 NAME SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "" 3 ID1 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 8 "\r\n" 4 ID2 SQL_Latin1_General_CP1_CI_AS

Now I want to insert the 4th field from the datafile again into the 5th column of the table i.e ID3.
Can this be done?

Thanks
Sam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 05:30:41

You want ID3 as a duplicate of ID2?
You can do an UPDATE after your BULK INSERT or BCP.

E 12°55'05.63"
N 56°04'39.26"

xpandre
Posting Yak Master

212 Posts

Posted - 2009-01-08 : 05:50:33

Yes. I want ID3 as duplicate of ID2.
Cant I do anything in the format file itself?
I am trying to void the update statement as it involves large number rows and columns and indexes.

Thanks
Sam

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-08 : 05:56:26

Why keep duplicate columns in a table. You can make a view on top of it and have the 2 rows reflecting the same values.

xpandre
Posting Yak Master

212 Posts

Posted - 2009-01-08 : 09:02:26

No. Actually the fields that needs to be moved to multiple columns is a set of data for all those columns put together, which is updated by a windows service.
And this service expects data in all the columns for its proper functioning. And we cant change the service too.
I guess, load and then update is the only and best option that I can look forward to.

Thanks
Sam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:19:54

Either manually update or have a trigger do the update for you.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources