0

I have an excel file which gives in info to be inserted for couple of tables in Database. There are around 40000 rows in Excel. I want to load them into Database. So looks like Stored procedure does the Job from ASP.NET UI. For Example: if the excel file row contains A,B,C,D,E in Column 1, Column2,Column3,Cloumn4,Column5 respectively, then I need to insert (A,C) in Table 1 and (B,D,E) into Table 2. we can assume that this splitting logic is built into the front End code where User inputs the excel file(ASP.NET UI).

But, reading each excel row and sending into the Stored procedure to insert into DB for 40K records seems waste of time and resources. Is there any way that we can read all the rows and pass in all this data in one call to Stored procedure and insert into database? its been too long that I worked with stored procedures and finding it difficult to remember which is the best way to go.

Programmerzzz
  • 1,237
  • 21
  • 48
  • Possible duplicate of [Uploading an Excel sheet and importing the data into SQL Server database](https://stackoverflow.com/questions/10447015/uploading-an-excel-sheet-and-importing-the-data-into-sql-server-database) – Nick.Mc Jun 14 '17 at 05:18
  • Please take a look at the duplicate post. It uses `SQLBulkCopy` to do it in one go – Nick.Mc Jun 14 '17 at 05:19
  • But in my case I need to split each excel data row to two different tables in database.how do we do that – Programmerzzz Jun 14 '17 at 05:22
  • Is there any other information that you need to add to your question? Please edit your question and add this information. Also please provide some info on the rules on how the data is split. Randomly split in half? based on a column? Is this split logic built into your stored procedure? – Nick.Mc Jun 14 '17 at 05:26
  • In that case, it's probably a duplicate of this instead. https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code – Nick.Mc Jun 14 '17 at 05:27
  • added the explanation for split with an example. – Programmerzzz Jun 14 '17 at 05:31
  • I've never heard of a requirement like that - splitting individual columns into multiple tables. Anyway. If you look at the duplicate that I referred to, you'll see the line `"Select [ID],[Name],[Designation] from [Sheet1$]`. You just have two bulk inserts, each one using a different select, selecting only the columns you need. – Nick.Mc Jun 14 '17 at 05:54
  • Yup that's what I too thought looking at the link.jist select the columns needed for both tables into two table valued parameters and pass both of them to the stored procedure from UI. are you suggesting the same thing or a different one – Programmerzzz Jun 14 '17 at 05:57
  • Sorry now that I've posted two links it's confusing.The _first_ link describes how to insert excel directly into a table using SqlBulkCopy (not via a stored proc). It will allow you to alter the source SQL. The second link shows how to use a table valued parameter with a stored procedure. Does your SP do anything special or does it just insert? If it just inserts, use the first method – Nick.Mc Jun 14 '17 at 06:06
  • I just wanted to insert it into those two mentioned tables – Programmerzzz Jun 14 '17 at 06:07
  • I suggest you follow the first link and forget about using a stored procedure at all then. The first link should have everything you need – Nick.Mc Jun 14 '17 at 06:11

0 Answers0