| Author |
Topic |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2009-01-19 : 19:26:08
|
| I would like to insert 10,000 rows of data into a table. I am using this: insert into tblRiderA (MunicName, ProvName, GroupAB, Period, LocalSeed, CrtlNum, RiderASigned, SupportInc, Notes)values ('Bancroft','Downeast EMS','A','3',$65.01,1.00,'Y','Y') for each record. Is there an easier way to commit the to the database all at once? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2009-01-19 : 20:54:49
|
can you please help me with the bulk insert syntax?this is what I have so far:bulk insert into tblRiderA (MunicName, ProvName, GroupAB, Period, LocalSeed, CrtlNum, RiderASigned, SupportInc, Notes)file C:\Documents and Settings\jboucher\My Documents\SQL Server Management Studio\Projects\EMS\Rider A.txtquote:
Originally posted by tkizer
The best way would be to have your data in a file and then use BULK INSERT to get it into the table. Barring that, you could use INSERT INTO/SELECT UNION ALL if you wanted to do it in one command but I certainly wouldn't recommend it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-01-20 : 07:22:16
|
| You might find it easier to use bcp for a one off insert like this. With Bulk Insert the database server has to be able to see the file (via UNC path). With BCP it's the other way around -- you have to be able to get to the server from your PC (which you already can) and there doesn't have to be a UNC path to the file for the server. The syntax is similar to bulk insert.Here's the relevant pages for 2005:BCP (2005) : http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspxBULK INSERT (2005) : http://msdn.microsoft.com/en-us/library/ms188365(SQL.90).aspxGood luck.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-01-20 : 08:57:46
|
| Why not post what you have tried so far and the particulars of your setup. Where is the file in relation to the database. What format is it in. How far have you progressed? What errors are you running into?Then we can help you.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Mass Insert Statement