how can i send a lot of data to a database using stored procedure?

I need to send a lot of data from the server(using c#) to the database at once, for example all the data are in a 'csv' format and i need to pass it once to the database.. the database functionality is implemented in the database,I mean i'm using the stored procedure way(T-SQL)开发者_如何学运维...

I tried to use the 'bulk insert' way and it works... but unfortunately it only works with a file... I don't need to use a file, i need to pass it in another way, like a string or anyway but not file...

how i can do that ??

this is the site for the 'bulk insert' http://msdn.microsoft.com/en-us/library/ms188365.aspx

thanks alot...

---

You can also do using Table-Valued Parameters. Please find more details at below mentioned link.

http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

http://msdn.microsoft.com/en-us/library/bb675163.aspx

Hope it will help for you.

---

You have the following options

1. SQL Export Import Wizard

2. using bcp utility programmatically http://msdn.microsoft.com/en-us/library/ms162802.aspx

---

One way would be to create xml and send that to a sp that interprets the xml.

improving ms sql insert performance with lazarus

Edit 1 Add sample code

Create a test table

create table TestTable (Id int, Name nvarchar(50))

Create the stored procedure that insert from xml to table

create procedure XMLInsertToTestTable
    @Data xml
as
insert into TestTable (Id, Name)
select
    r.r.value('Id[1]', 'int'),
    r.r.value('Name[1]', 'nvarchar(50)')
from @Data.nodes('root/row') r(r)

Call the procedure with xml-string as argument

exec XMLInsertToTestTable
  '<root>
    <row> 
      <Id>1</Id>
      <Name>Name 1</Name>
    </row>    
    <row> 
      <Id>2</Id>
      <Name>Name 2</Name>
    </row>    
  </root>'