Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with EXCEL SCRIPT | Excel Discussion (Misc queries) | |||
help with EXCEL SCRIPT | Excel Discussion (Misc queries) | |||
I need a VB script for Excel | Excel Programming | |||
Excel 2000/XP script to Excel97 script | Excel Programming | |||
VBA Script in Excel | Excel Programming |