How to Bulk Insert specific columns from a log file in SQL Server 2000?

Question

---

Known is a DROP, Unknown is an OCEAN.

Answer 1

Hi Bangaaram,

You can do skip columns to import in the BULK INSERT command by using a format file and specifying the columns of interest to you in the format file - http://msdn.microsoft.com/en-us/library/ms179250.aspx

This topic describes format files. You can use a format file to skip importing a table column when the field does not exist in the data file. A data file can contain fewer fields than the number of columns in the table only if the skipped columns are nullable and/or have default value.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 2

Hi Bangaaram,

You can do skip columns to import in the BULK INSERT command by using a format file and specifying the columns of interest to you in the format file - http://msdn.microsoft.com/en-us/library/ms179250.aspx

This topic describes format files. You can use a format file to skip importing a table column when the field does not exist in the data file. A data file can contain fewer fields than the number of columns in the table only if the skipped columns are nullable and/or have default value.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

I didn't understand it clearly. Should I create a .dat file or .xml file or .fmt file to perform the bulk insert?

---

Known is a DROP, Unknown is an OCEAN.

Answer 3

I didn't understand it clearly. Should I create a .dat file or .xml file or .fmt file to perform the bulk insert?

There are two types of format files, XML files and the old format. I much prefer the older style. And if you are on SQL 2000, you don't have any other choice anywway.

Old-style format files can have extension you like; I prefer to use .fmt.

If you want to get an understanding of format files, you can search my postings in this forum, as I have explained this in more than one response.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Hi Bangaaram,

You can do skip columns to import in the BULK INSERT command by using a format file and specifying the columns of interest to you in the format file - http://msdn.microsoft.com/en-us/library/ms179250.aspx

This topic describes format files. You can use a format file to skip importing a table column when the field does not exist in the data file. A data file can contain fewer fields than the number of columns in the table only if the skipped columns are nullable and/or have default value.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Thanks Sanil. That was helpful.

---

Known is a DROP, Unknown is an OCEAN.

Answer 5

I didn't understand it clearly. Should I create a .dat file or .xml file or .fmt file to perform the bulk insert?

There are two types of format files, XML files and the old format. I much prefer the older style. And if you are on SQL 2000, you don't have any other choice anywway.

Old-style format files can have extension you like; I prefer to use .fmt.

If you want to get an understanding of format files, you can search my postings in this forum, as I have explained this in more than one response.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Well you are right. I have got already a .fmt file which solved the problem for me. Thanks Erland.

---

Known is a DROP, Unknown is an OCEAN.

Answer 6

Hi Bangaaram,

You can do skip columns to import in the BULK INSERT command by using a format file and specifying the columns of interest to you in the format file - http://msdn.microsoft.com/en-us/library/ms179250.aspx

This topic describes format files. You can use a format file to skip importing a table column when the field does not exist in the data file. A data file can contain fewer fields than the number of columns in the table only if the skipped columns are nullable and/or have default value.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

It worked for me when there were equal numbe of columns. However, it didn't work in the following case:

Source File: 14 Columns

Destination Table: 24 Columns

I should pull only 10 columns from the source and the rest can be NULL in destination.

I've used something like this and its not letting it do.

8.0
10
1   SQLCHAR   0   23  "|"      1	DateAndTime		""
2   SQLCHAR   0   23  "|"      2	Session			SQL_1xCompat_C0_CI_AS
3   SQLCHAR   0   9   "|"      6	UserID			SQL_1xCompat_C0_CI_AS
4   SQLCHAR   0   50  "|"      7	UserFirstName		SQL_1xCompat_C0_CI_AS
5   SQLCHAR   0   50  "|"      8	UserLastName		SQL_1xCompat_C0_CI_AS
6   SQLCHAR   0   75  "|"      10	CompanyName		SQL_1xCompat_C0_CI_AS
7   SQLCHAR   0   1   "|"      14	Flag			SQL_1xCompat_C0_CI_AS
8   SQLCHAR   0   100 "|"      12	Current_Address		SQL_1xCompat_C0_CI_AS
9   SQLCHAR   0   1000 "|"	13	Permanant_Address	SQL_1xCompat_C0_CI_AS 
10  SQLCHAR   0   50  "|"	11	Service_Category	SQL_1xCompat_C0_CI_AS

Can you help where I was going wrong?

---

Known is a DROP, Unknown is an OCEAN.

Answer 7

To close the loop, Bangaaram has started a new thread at
http://social.Msdn.microsoft.com/Forums/en-US/transactsql/thread/f8e5098b-b304-45f5-86a7-fb1c86590419#f8e5098b-b304-45f5-86a7-fb1c86590419
and I have hopefully added a helpful answer.

---

Erland Sommarskog, SQL Server MVP, [e-mail]