How to CONCATENATE >50 fields in Excel table into SQL Insert State

SQL Server 2005, Excel 2003
I would like to populate some tables in a SQL database from some tables in
Excel.
Previously I have used the Concatenate function in Excel to construct SQL
Insert statement such as example below.
=CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
However, there is a limit in using the Concatenate function in Excel and
this dows not work on a larger table (50 fileds). Please could you advice
how I could create the SQL insert statements using this Excel table to
populate the associate SQL table? Is there any better/alternative ways?
Many thanks in advance,
Hi Will,
The bcp utility will import the file into a table for you. You can learn
about it here:
http://technet.microsoft.com/en-us/library/ms162802.aspx
-Susan
"will~" wrote:

> SQL Server 2005, Excel 2003
> I would like to populate some tables in a SQL database from some tables in
> Excel.
> Previously I have used the Concatenate function in Excel to construct SQL
> Insert statement such as example below.
> =CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
> VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
> However, there is a limit in using the Concatenate function in Excel and
> this dows not work on a larger table (50 fileds). Please could you advice
> how I could create the SQL insert statements using this Excel table to
> populate the associate SQL table? Is there any better/alternative ways?
> Many thanks in advance,
>
|||On Thu, 28 Feb 2008 07:43:00 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:

>Hi Will,
>The bcp utility will import the file into a table for you. You can learn
>about it here:
>http://technet.microsoft.com/en-us/library/ms162802.aspx
>-Susan
BCP.EXE is quite particular about the format of an input file. While
you might be able to EXPORT from Excel in a format that can somehow be
made to work with BCP.EXE, it is not a simple process.
I would start by using the data import wizard. This can create a DTS
package (SQL Server 2000) or an SSIS package (SQL Server 2005).
Roy Harvey
Beacon Falls, CT