Bulk insert - 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

Author

Topic

bmeza77
Starting Member

1 Post

Posted - 2006-04-21 : 18:05:51

Hi, I have a text file and I want to use bulk insert, I now how to do it, but in the text file I have header record and detail record, how can I make a difference to insert in boot tables;

example text file
H^MP^319267228^17-FEB-06^^^^GXB
D^GXB10269^US^23^EA^^^^.45632^^MX^^^^
D^GXB10269^US^23^EA^^^^.45632^^MX^^^^
H^MP^319310332^27-FEB-06^^^^GBX
D^GBX10269^US^4^EA^^^^.07936^^MX^^^^

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-21 : 18:14:27

If you're lucky, you can use the FIRSTROW and LASTROW options to separately bulk insert the header and the detail. In my experience though, this is not always possible because headers are often misread by bulk insert. Therefore, you often have no choice but to get rid of the header before using bulk insert or bcp.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-21 : 19:04:44

just bcp the whole text file into a staging (temporary) table and then insert from there.
it'll be easier.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-21 : 21:36:48

Right, but if the header is in a different format than the detail (which it usually is), then bcp'ing into a staging table won't be that easy.

Kristen
Test

22859 Posts

Posted - 2006-04-22 : 06:10:42

I think Spirit was meaning BCP into staging table as a single varchar column, then optionally split into two staging tables based on the initial "H" / "D", and then "split" the varchar on the "^" delimiter so that they can be inserted / update the final tables.

Receiving the data as XML would be another way