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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

will~ - 28 Feb 2008 12:12 GMT

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,

Reply to this Message

Susan Cooper - 28 Feb 2008 15:43 GMT

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

> SQL Server 2005, Excel 2003
>
[quoted text clipped - 12 lines]
>
> Many thanks in advance,

Reply to this Message

Roy Harvey (SQL Server MVP) - 29 Feb 2008 22:05 GMT

>Hi Will,
>
[quoted text clipped - 4 lines]
>
>-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

Reply to this Message

John Stockett - 15 Jul 2008 15:24 GMT

Another, albeit less technically savvy, option is to use multiple cells to
concatenate your SQL statement.

For example, if you have 92 fields, concatenate the first 50 in cell A1 and
concatenate 51-92 in cell B1. Then in cell C1, simply concatenate the A1 &
B1.

Further illustrated...
In cell A1:
=Concatenate("value1","value2","value3"..."value50")

In cell B1:
=Concatenate("value51","value52","value53"..."value92")

In cell C1:
=CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
VALUES ('",A1,B1,"')")

Just be sure to include the proper syntax (apostrophes & commas) in cells A1
and B1!

> SQL Server 2005, Excel 2003
>
[quoted text clipped - 12 lines]
>
> Many thanks in advance,

Reply to this Message