December 13, 2006 at 8:49 am
I have changed over from the Oracle world to SQL Server. I need to do a bulk load from a csv file into a temp table, modify the records in the temp table ( add sequence numbers ) and then load the data into a permenant table. I am afraid I don't know the proper command syntax to put all of this inside a procedure and unforunately I do not have access to any reference material. Does anyone have any code examples on how this might be done.
thanks,
Lost in SQL Server
December 13, 2006 at 8:58 am
>>unforunately I do not have access to any reference material
SQL Books on line (aka BOL) is freely available.
Read BOL on topics BULK INSERT, IDENTITY() function and #temp tables.
December 13, 2006 at 9:36 am
Do a search for BCP in Books online and you should have all the info you need.
Also a very helpful aid is to just do a Google search for what you need. It's suprising how much you can find out there.
December 14, 2006 at 3:56 am
Went to BOL and ran what you see below. Thus:
1. Load your CSV file into Excel
2. Insert a blank row in the beginning (apparently needed - don't ask me why)
3. Select all the data (including the blank row) and name it by typing in a name (test in my case) in the text box to the left of the formula bar
4. Save it somewhere (c:\tmp\Book1.xls in my case)
5. Run what you see below in Query Analyzer. Incredible, but it works
sp_addlinkedserver 'ExcelSource','Jet 4.0','Microsoft.Jet.OLEDB.4.0','c:\tmp\Book1.xls',null,'Excel 5.0'
go
sp_addlinkedsrvlogin 'ExcelSource', false, null, null, null
go
select * from ExcelSource...test
December 14, 2006 at 5:38 am
You don't need to even have excel in the equation.
If you read BOL about BCP you will see that you can set the comma as a field delimiter and load the file that way.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply