Excel to SQL Script

[Budget Programmer]

Hello,
Is there some function/utility/etc. that will read a row of Excel data, and
create a file with SQL statements to load the data into SQL? We want to
avoid using ADO and the traditional "insert" method.

--
Programmer on Budget

[Jake Marx]

Hi BP,

Budget Programmer wrote:
> Hello,
> Is there some function/utility/etc. that will read a row of Excel
> data, and create a file with SQL statements to load the data into
> SQL? We want to avoid using ADO and the traditional "insert" method.

If you're talking about importing the data into SQL Server, you can use DTS
or BCP to import the data directly from Excel into SQL Server.

If it's a limited # of rows of data, I typically dynamically generate the
INSERT statements using Excel, then copy/paste the resulting script into the
SQL SMS and execute it from there.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

[Jake Marx]

Just to follow up on what I meant by dynamic script generation, let's assume
we have this data in a worksheet (A1:C3):

ID Name Phone
1 Jake 555-1212
2 John 555-1213

In D2, you could use a formula like this:

="INSERT INTO dbo.Employees ([" & $A$1 & "], [" & $B$1 & "], [" & $C$1 & "])
VALUES (" & $A2 & ", '" & $B2 & "', '" & $C2 & "')"

and copy it down to D3. This would result in the following 2 SQL stmts in
D2:D3:

INSERT INTO dbo.Employees ([ID], [Name], [Phone]) VALUES (1, 'Jake',
'555-1212')
INSERT INTO dbo.Employees ([ID], [Name], [Phone]) VALUES (2, 'John',
'555-1213')

Which you could then copy into any SQL editor to execute them against the
server.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


Jake Marx wrote:
> Hi BP,
>
> Budget Programmer wrote:
>> Hello,
>> Is there some function/utility/etc. that will read a row of Excel
>> data, and create a file with SQL statements to load the data into
>> SQL? We want to avoid using ADO and the traditional "insert" method.
>
> If you're talking about importing the data into SQL Server, you can
> use DTS or BCP to import the data directly from Excel into SQL Server.
>
> If it's a limited # of rows of data, I typically dynamically generate
> the INSERT statements using Excel, then copy/paste the resulting
> script into the SQL SMS and execute it from there.

[Budget Programmer]

Jake,
Thanks for your help.
--
Programmer on Budget


"Jake Marx" wrote:

> Just to follow up on what I meant by dynamic script generation, let's assume
> we have this data in a worksheet (A1:C3):
>
> ID Name Phone
> 1 Jake 555-1212
> 2 John 555-1213
>
> In D2, you could use a formula like this:
>
> ="INSERT INTO dbo.Employees ([" & $A$1 & "], [" & $B$1 & "], [" & $C$1 & "])
> VALUES (" & $A2 & ", '" & $B2 & "', '" & $C2 & "')"
>
> and copy it down to D3. This would result in the following 2 SQL stmts in
> D2:D3:
>
> INSERT INTO dbo.Employees ([ID], [Name], [Phone]) VALUES (1, 'Jake',
> '555-1212')
> INSERT INTO dbo.Employees ([ID], [Name], [Phone]) VALUES (2, 'John',
> '555-1213')
>
> Which you could then copy into any SQL editor to execute them against the
> server.
>
> --
> Regards,
>
> Jake Marx
> www.longhead.com
>
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> Jake Marx wrote:
> > Hi BP,
> >
> > Budget Programmer wrote:
> >> Hello,
> >> Is there some function/utility/etc. that will read a row of Excel
> >> data, and create a file with SQL statements to load the data into
> >> SQL? We want to avoid using ADO and the traditional "insert" method.
> >
> > If you're talking about importing the data into SQL Server, you can
> > use DTS or BCP to import the data directly from Excel into SQL Server.
> >
> > If it's a limited # of rows of data, I typically dynamically generate
> > the INSERT statements using Excel, then copy/paste the resulting
> > script into the SQL SMS and execute it from there.
>
>
>