Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
calvin464
Starting Member
11 Posts |
 Posted - 2007-11-09 : 14:37:01
|
| Hi,I have two tablesTransaction(TxnType, CardNumber, ExpDate, Invoice, ...)Customer(CusID, CardNumber, ExpDate, ...)and on the application (C#, ASP.NET 1.1) I have a dataTable that containmany rows of record like:TxnType,CusID,AmountTxnType,CusID,Amount....................My question is that is there a way that allow me to basically pass the whole dataTable (in csv, or xml format) into a sproc so it can process the whole list there with this actioninsert into Transactions (TxnType, CardNumber, ExpDate, Amount,....) values (TxnType, CardNumber, ExpDate, Amount)where the values data is pull from the Customer table and the Amount is from the pass in list? Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-09 : 15:15:24
|
Yes, this can be done both with csv and xml. maybe xml is the preferred choice here since the file will grow beyond 8k anyway.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-11-09 : 15:32:35
|
| you can do a bulk insert of your datatable into a staging table in the database, then invoke the SP that does the action you need. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
calvin464
Starting Member
11 Posts |
Posted - 2007-11-09 : 17:21:57
|
| Thanks all for yours reply. Since I'm using .net 1.1 SqlBulkCopy won't be available, I'll try bulk insert then. |
|
|
|
|
|
|
|
|
insert multiple record