Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

Generate insert/update script from Excel sheet

Hi Experts,

   Please do refer the attache excel sheets which contain two sample tables.  In my database I've two respective tables as given below.
CREATE TABLE CONFIGURATION
(
Name VARCHAR(100) NOT NULL,
NType  VARCHAR(100) NOT NULL,
Data	VARCHAR(100) NOT NULL,
Datatype VARCHAR(100) NOT NULL,
DataVal VARCHAR(100) NOT NULL,
Remarks VARCHAR(100)  NULL) 

GO
ALTER TABLE CONFIGURATION
ADD CONSTRAINT PK_NTD PRIMARY KEY CLUSTERED (Name, NType, Data);


GO

CREATE TABLE Settings
(
ID INT NOT NULL,
NKey VARCHAR(100) NOT NULL,
Value VARCHAR(100) NOT NULL,
Remarks VARCHAR(100)  NULL
)
GO
ALTER TABLE Settings
ADD CONSTRAINT PK_IDNkey PRIMARY KEY CLUSTERED (ID, NKey);
GO

Open in new window


I would like to read the excel sheet and generate sql script for each table based on the table's PK.

Say for example, for the  configuration table the expected result looks like given below.


IF EXISTS (SELECT 1 FROM CONFIGURATION WHERE Name= 'Auditing'  AND NType = 'Audit'  AND  Data= 'AuditDt')
BEGIN
 UPDATE CONFIGURATION
 SET 
 Datatype = 'D',	DataVal	= '11/26/2013', Remarks  = 'Audit Date'
 WHERE  Name= 'Auditing'  AND NType = 'Audit'  AND  Data= 'AuditDt'

END
ELSE
BEGIN
 INSERT CONFIGURATION ([Name],[NType],[Data],[Datatype],[DataVal],[Remarks])
 VALUES ('Auditing','Audit'	,'AuditDt','D','11/26/2013','Audit Date')

END

Open in new window



Please guide me how to achieve this? It would be great if you share the coding or existing tool?

Thanks in advance.
SampleExcel.xlsx
0
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
  • 2
1 Solution
 
unknown_routineCommented:
.NET(C#) lets you read Excel easily:

string MyCon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;Extended Properties='Excel 8.0;HDR=Yes;'"
using(OleDbConnection connection = new OleDbConnection(MyCon ))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Configuration$]", connection)
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
            'rest of your code....
            ...

         }
    }
}
0
 
Easwaran ParamasivamAuthor Commented:
Thanks. It would be great if you share sample application.
0
 
Easwaran ParamasivamAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now