How-To Synchronize Data Between Excel 2007 and SQL Server 2008 | OPENROWSET
Actually the Release Date column will store just the year that the song was released. Album is the name of the album where the song first appeared and Decade is 2 digit Char to represent the decade.
Here is the new table definition. You can copy and paste directly into a SSMS query and execute it to change the table.
UPDATE [BISolutions].[dbo].[Music]
SET [Song Title] = <Song Title, nvarchar(255),>
,[Artist] = <Artist, nvarchar(255),>
,[Release Date] = <Release Date, char(4),>
,[Album] = <Album, varchar(150),>
,[Decade] = <Decade, char(2),>
WHERE <Search Conditions,,>
GO
Updating an Excel spreadsheet Worksheet is very easy with OPENROWSET. You can use data from an existing table to update specific column values in your Excel spreadsheet worksheet column or you can pass values from call a stored procedure or hard code the values that you want to change.
This first minimalistic example will use a hard coded value to update the song “You Give Love A Bad Name” by Bon Jovi. I am going to insert the year the song was released. As you can see from the code below that updating the worksheet entry is just like updating any other table.
First define what database and table you want to update. In our case this is the “music” table (Worksheet) in our music.xlsx database (Excel file) as define like this :
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
Be careful when specifying the Worksheet (table name). You need to append the “$” to the end of the name you have given your Worksheet and the complete name has to be enclosed in square brackets “[“ “]”. The rest of the code is a standard UPDATE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Kevin Languedoc
-- Create date: 9/25/2010
-- Description: Updating Records in Excel from SQL Server 2008 using Openrowset
-- =============================================
CREATE PROCEDURE dbo.UpdateExcelRecordsWithOpenrowset
AS
BEGIN
UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
SET [Release Date]='1986'
WHERE [Song Title]='You Give Love A Bad Name'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Kevin Languedoc
-- Create date: 9/25/2010
-- Description: Updating Records in Excel from SQL Server 2008 using Openrowset
-- =============================================
CREATE PROCEDURE dbo.UpdateExcelRecordsWithOpenrowset
@yr char(4),
@song varchar(150)
AS
BEGIN
UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
SET [Release Date]=@yr
WHERE [Song Title]=@song
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Kevin Languedoc
-- Create date: 9/25/2010
-- Description: Updating Records in Excel from SQL Server 2008 using Openrowset
-- =============================================
CREATE PROCEDURE dbo.UpdateExcelRecordsWithOpenrowset
AS
BEGIN
UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
SET xMusic.[Release Date]=dbo.Music.[Release Date]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]') xMusic
INNER JOIN dbo.Music dbMusic
ON xMusic.[Song Title] = dbMusic.[Song Title]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: klanguedoc
-- Create date: 10/01/2010
-- Description:
-- =============================================
CREATE PROCEDURE dbo.InsertRecordsWithOPENROWSET
@Song varchar(100),
@Artist varchar(100),
@Release char(4),
@Album varchar(150),
@Decade char(2)
AS
BEGIN
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
([Song Title],Artist,[Release Date],Album,Decade)
values (@Song, @Artist, @Release, @Album, @Decade)
END
GO
Comments
still is giving error
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]".