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

Réponses

  • 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


    vendredi 11 mai 2012 20:00

Toutes les réponses

  • 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


    vendredi 11 mai 2012 20:00
  • 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.

    mardi 5 juin 2012 08:21
  • 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, esquel@sommarskog.se
    mardi 5 juin 2012 10:23
  • 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.


    jeudi 7 juin 2012 13:22
  • 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, esquel@sommarskog.se
    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.

    jeudi 7 juin 2012 13:23
  • 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.

    mercredi 13 juin 2012 18:38
  • 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, esquel@sommarskog.se
    mercredi 13 juin 2012 21:55