Excel to SQL Script
 

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

Excel to SQL Script
 

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]

Excel to SQL Script
 

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.

Excel to SQL Script
 

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.