Excel to SQL Server

[TZoner]

I have an Excel worksheet with 4 columns:
F1 F2 F3 AutoNo
A Y C 1
G C D 2
S W A 3


I have a table in SQL Server 2000 which corresponds to the above worksheet.
What's the best way to update columns F1, F2, F3 in the table using the
AutoNo from both the table and worksheet?

Thanks for any replies using ADO/VB/SQL and not DTS.

[John Bell]

Re: Excel to SQL Server

Hi

I would expect either the autonumber in the spreadsheet or the autonumber in
the database to be the master, otherwise you will probably end up with
miss-matching records.

If you use a liked server you can update/query both. If the SQL Server table
has an identity that you want to force, then SET IDENTITY_INSERT ON.

John

"TZoner" <tzoner@hotmail .com> wrote in message
news:3f1132e5$0 $31925$afc38c87 @news.optusnet. com.au...[color=blue]
> I have an Excel worksheet with 4 columns:
> F1 F2 F3 AutoNo
> A Y C 1
> G C D 2
> S W A 3
>
>
> I have a table in SQL Server 2000 which corresponds to the above[/color]
worksheet.[color=blue]
> What's the best way to update columns F1, F2, F3 in the table using the
> AutoNo from both the table and worksheet?
>
> Thanks for any replies using ADO/VB/SQL and not DTS.
>
>
>[/color]

[TZoner]

Re: Excel to SQL Server

John

Real issue I have is how do I get data from Excel into SQL the fasted
possible way?

Thanks for your previous reply!








"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:3f12643a$0 $15033$ed9e5944 @reading.news.p ipex.net...[color=blue]
> Hi
>
> I would expect either the autonumber in the spreadsheet or the autonumber[/color]
in[color=blue]
> the database to be the master, otherwise you will probably end up with
> miss-matching records.
>
> If you use a liked server you can update/query both. If the SQL Server[/color]
table[color=blue]
> has an identity that you want to force, then SET IDENTITY_INSERT ON.
>
> John
>
> "TZoner" <tzoner@hotmail .com> wrote in message
> news:3f1132e5$0 $31925$afc38c87 @news.optusnet. com.au...[color=green]
> > I have an Excel worksheet with 4 columns:
> > F1 F2 F3 AutoNo
> > A Y C 1
> > G C D 2
> > S W A 3
> >
> >
> > I have a table in SQL Server 2000 which corresponds to the above[/color]
> worksheet.[color=green]
> > What's the best way to update columns F1, F2, F3 in the table using the
> > AutoNo from both the table and worksheet?
> >
> > Thanks for any replies using ADO/VB/SQL and not DTS.
> >
> >
> >[/color]
>
>[/color]

[Matthew Martin]

Re: Excel to SQL Server

I concur, a linked server gets your data in SQL fastest. From there you
just use SQL commands to join and update the tables.
EXEC sp_addlinkedser ver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet. OLEDB.4.0',
'c:\myexcelfile .xls',
NULL,
'Excel 5.0'
GO
SELECT * FROM ExcelSource...M yNamedRange
GO

If you are working with small tables that get dumped to Excel for an
employee to update (say prices) here is one way to make the update happen
immediately. It essentially binds the spreadsheet to the server table and
uses optomistic locking.

Matthew Martin

Dim con As ADODB.Connectio n

Private Sub Worksheet_Activ ate()
Set con = New ADODB.Connectio n
con.Provider = "sqloledb"
con.Properties( "Data Source").Value = "MARIA" ' Your server name here
con.Properties( "Initial Catalog").Value = "MyDB" ' your DB name here
con.Properties( "Integrated Security").Valu e = "SSPI"
con.Open
End Sub

Private Sub Worksheet_Chang e(ByVal Target As Range)
' Ensure we have a row ID & column name
' AutoNum is in column 1, where primary keys should be.
If Target.Row > 1 _
And Worksheets(1).C ells(1, Target.Row).Tex t <> "" _
And Worksheets(1).C ells(Target.Col umn, 1).Text <> "" Then
If con Is Nothing Then
Worksheet_Activ ate
End If

con.Execute "UPDATE tblExportSQL " & _
"SET " & Worksheets(1).C ells(1, Target.Column). Text & " = '" & _
Target.Text & "' WHERE AutoNo = " & Worksheets(1).C ells(Target.Row ,
1).Text

End If
End Sub

Private Sub Worksheet_Deact ivate()
con.Close
Set con = Nothing
End Sub



"TZoner" <tzoner@hotmail .com> wrote in message
news:3f1132e5$0 $31925$afc38c87 @news.optusnet. com.au...[color=blue]
> I have an Excel worksheet with 4 columns:
> F1 F2 F3 AutoNo
> A Y C 1
> G C D 2
> S W A 3
>
>
> I have a table in SQL Server 2000 which corresponds to the above[/color]
worksheet.[color=blue]
> What's the best way to update columns F1, F2, F3 in the table using the
> AutoNo from both the table and worksheet?
>
> Thanks for any replies using ADO/VB/SQL and not DTS.
>
>
>
>[/color]