| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2002-12-09 : 18:01:34
|
| Hello....Here is my issue: I am creating a dataset from a master table that exists on one server...from a vb.net app running on a different server/workstation. Once I have the dataset locally, I am truncating the same table on my local sql server and I want to do something like a bulk copy or bulk insert to move all of the data from the dataset into the sql server table. Do I have to iterate through the entire recordset and issue a SQL INSERT (or call a store proc) for each row of data? In the Sql Query Analyzer, I can issue a command like: INSERT INTO myTable SELECT * FROM otherTableBut this is for a sql table to sql table copy/append. The data I want to insert is in an ado.net dataset. Also...I don't know if this matters, but three of my columns contain Image/Binary data(digital photos, signatures, etc.)Thanks for any help or suggestions.....dw. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-12-09 : 18:13:12
|
| Well I haev one suggestion for you. Don't store binary data in the database. The "best practice" is to store the file on disk, and put the path to the file in the database.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2002-12-09 : 18:17:02
|
| Michael...Thanks for the suggestion. That is something that we struggled with for a while during the design phase....however, it was decided (for a myriad of reasons) to store the binary data in the database. We had already thought of storing the path to the binary file, but there was no compelling reason to do so. Also, many of the "best practices" just don't make sense or work in the "real world"...and I ain't referring to the one on mtv....dw |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-12-09 : 18:23:38
|
| Few ways to do this.1. DTSCreate a DTS package that transforms data from source to destinationIf you need to launch it from VB use a DTS Object.2. BCP out / BCP inUse your VB.NET to call a stored procedure on server1 and bcp the dataset out to a file. Call another stored procedure on server2 to bcp the file in.3. Write dataset to file / BCP inSecond part is the same as 24. Linked ServerCreate a linked server on the destination server and write a stored procedure that pulls in the data usingINSERT INTO server.database.owner.tableSELECT * FROM server.database.owner.tableUse VB.Net to call the sp |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-09 : 18:26:02
|
| How do you do any of those from an ADO recordset/dataset though? |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2002-12-09 : 18:36:01
|
| Well..this is getting off topic from my original post....which will probably now fall into a vortex of swirling bernaise sauce....somewhere outside of the Bermuda Triangle......Yes...I have read and researched those very same arguments that were presented in that article. Regarding security, we have secured our sql db with a host of locks, chains and some special, created just for us, duct tape from NASA. In our situation, performance was paramount and it turned out after several minutes of hypothesizing over a large cup of ether...storing the binary data in the db proved faster in a series of tests versus a super-charged, 4-on-the-floor, big block V8 with nitrous injection. As you can tell, we did our homework....at least in high school!Thanks for the concern and advice.dw |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-12-09 : 18:38:53
|
| Only method 3 uses an ADO recordset.Open "c:\output.csv" For Output As #1WHILE NOT rs.EOF Write #1, Rs.Fields(field1) & "," & Rs.Fields(field2)WENDClose #1The others are alternative methods which can be initiated from VB.NET but don't require a recordset object.There is a .net file object if you want to use the new .net syntax. There is an object for everything.Edited by - ValterBorges on 12/09/2002 18:40:32Edited by - ValterBorges on 12/09/2002 18:55:48 |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2002-12-09 : 18:48:43
|
| ValterBorges....Thanks for the great advice. I will try them tonight.Regards,dw |
|
|
|
Argyle
Yak Posting Veteran
53 Posts |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-12-10 : 03:30:56
|
| I think you should open a book on ADO.NET. You'll see that it will answer your question. I know that you probably don't have time to do that right now, but I can't give you a detailed solution to your problem because it's not so trivial. But once you read a good book, you'll see that the solution is pretty easy.You should follow Argile's advice and dive deeper into ADO.NET and MSSQL2K communication. |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2002-12-10 : 10:45:56
|
| rihardh....Actually, what you probably mean is that I should "read" a book on ado.net. I have a half-dozen ado.net books "open" on my desk already. As you surmised....too many pressing issues without the time to deal with things slowly. And...unfortunately, the myriad of ado.net books I have purchased in the last 8 months are not that good. They have a few snippets buried in the pages that are treasure...but alot is fool's gold. I am looking for that one book that will offer some theories and ideas based on good, real-world projects. Kinda' like I am involved with now. If I had the time, I would write a book when we're finished. If you can offer a suggestion on what you think is a good ado.net book, I will definitely check it out.thanks...dwEdited by - dhw on 12/10/2002 10:46:35 |
|
|
|
|
Possible to insert ado.net dataset into sql table?