Click here to Skip to main content
15,605,787 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have my application in VS2008 and coded

My query is i want to save record from an excel sheet to my Database Table.

But i dont want to insert the Excel sheet as a whole rather i want to insert the record in my database table Row wise.Inserting the records is not the only thing but my major issue is i want to check and compare the Primary Key column of my Table with the Column in my Excel.

Suppose Table1 is my Sql Database Table,and RegistrationNo is the Primary Key Column.Similarly there is also a column of Registration Number in my Excel Sheet.

Now before inserting each record the RegistrationNo column should be compared.If the a registrationNo in the excel sheet say RegistrationNo 112 is already present in the table then it should not Insert the record and if its not present then it should insert.

Also the other issue is the number of columns in my Database table are more than the number of columns in my excel Sheet.So the columns that are not present in the excel sheet should be set to NULL while inserting the record in the Table.

Please help me with a link of similar example or any helpful code to do this.
Sergey Alexandrovich Kryukov 1-Nov-11 11:45am    
What do you mean by "record" in Excel?
Maciej Los 1-Nov-11 13:26pm    
How do you want do this: manually (query in SSMSE) or programmatic?

1 solution

First of all you need to know how to create OleDb.Connection[^] for SQL Server OLE DB Provider.

The second one is to know how to create query:
INSERT INTO [A_TEST].[dbo].[Regs] (RegistrationNo, Column_2, Column_3, Column_4, Column_5, Column_6, Column_7, Column_8)
SELECT PT.RegistrationNo, PT.[Column 2] AS Column_2, PT.[Column 3] AS Column_3, PT.[Column 4] AS Column_4,
        PT.[Column 5] AS Column_5, PT.[Column 6] AS Column_6, PT.[Column 7] AS Column_7, PT.[Column 8] AS Column_8
    SELECT *
    FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\Regs.xls', 'SELECT * FROM [Sheet1$]') AS DT
    WHERE [RegistrationNo] NOT IN (SELECT RegistrationNo FROM A_TEST.[dbo].[Regs])
    ) AS PT

In my example:
- database: A_TEST
- table: Regs
- columns: RegistrationNo, Column_1, Column_2,..., Column_8
As you see i use 4 times SELECT and 1 times INSERT command. First SELECT command is used to get data from PT (alias) and to set names for column-headers (if in the destination table they are different from source table). The second SELECT command is used to get data from MS Excel sheet and the third (in WHERE clause) is used to exclude existing RegistrationNo.

Before you start you need to read more about OPENROWSET[^].

Your question is not enough clear, so i can't help you more.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900