Mass data insert into SQL Server?
I've got 8 worksheets within an Excel workbook that I'd like to import into separate tables within a SQL Server DB.
I'd like to import each of the 8 worksheets into a separate table, ideally, with table names coinciding with worksheet tab names, but initially, I just want to get the data into the tables, so arbitrary table names work for the time being too.
The format of the data in each of the worksheets (and tables by extension) is the same (and will be identical), so I'm thinking some kind of loop could be used to do this.
Data looks like this:
Universe Date Symbol Shares MktValue Currency SMALLCAP 6/30/2011 000360206 27763 606361.92 USD SMALLCAP 6/30/2011 000361105 99643 2699407.52 USD SMALLCAP 6/30/2011 00081T108 103305 810926.73 USD SMALLCAP 6/30/2011 000957100 57374 1339094.76 USD
And table format in SQL would/should be consistent with the following:
CREATE TABLE dbo.[market1] ( [Universe_ID] char(20), [AsOfDate] smalldatetime, [Symbol] nvarchar(20), [Shares] decimal(20,0), [MktValue] decimal(20,2), [Currency] char(3) )
I'm open to doing this using either SQL/VBA/C++ or some combination (as these are the languages I know and have access to). Any thoughts on how to best go about this?
You could use SSIS or DTS packages to import them. Here are a couple references to get you going.