Import test data into SQL Express 2005

[John Cassell]

Hi there, I have seen a few examples of how to do this but all seem a bit over my head and if there is a simpler solution I would like to hear about it..

All I want to do is put about 30,000 rows of data into a table. Doesn't matter at all what the data is.

I thought the best way to do this would be to make the data in Excel and import into SQL Server Express 2005.

Is this the best way and if so how do you do the import?

Thanks

John

 

[giadich]

why do you need to turn it into excel first?
what format is it in right now?

 

[John Cassell]

Thanks for the reply.

My table is below, all I want to do is populate it with lots of test data to check performance of system.

The reason I was thinking of Excel was that I could manually create one row of data and just drag that down the spreadsheet so I have many records..

 

[giadich]

i did something like this to generate a few million records to see test the server performance on a search algorithm. i wrote a tiny app to randomly generate the data.

i don't see any issue with going with excel and it would seem to save a bit of time too.

alternatively, you can also create a stored proc and use a cursor to accomplish the same thing.

 

[John Cassell]

Hi again, I would go with Excel but I don't know how to export it over to SQL!

I am using SQL Express 2005 which doesn't seem to have an import function.

 

[giadich]

yes it does... it just doesn't appear where you would expect it to be though.

1. Launch SQL server management studio [express]
2. Login as appropriate
3. Expand the nodes to review your db
4. Right click on the db
5. Select Tasks - Import Data

In the event you can't use sql server management studio to import data, you can use oledb and open your xls to insert the data into your table.

 

[John Cassell]

Hi,

Thanks for that. SQL Server Management Express has no feature for importing. When you get to Select Tasks, it doesn't have import listed there.

Anyway, I looked up the oledb which you advised has eventually worked for me.

it wasn't straight forward so for any newbies reading this post this is what I did..

1. Open SQL SE 2005 Management Express, log in
2. Expanded my database, Right click my table and selected 'Script Table as INSERT to'
3. I then posted this code...

INSERT INTO [MyDatabase].[dbo].[TBL_MyTable]

SELECT Column1, Column2, Column3, Column4 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\TestDataForSQLExport.xls',
                'SELECT * FROM [Data$]')

*Note that although I am running Excel 2000 (9.0) I must leave the code above as 8.0. - also the xls file location noted above must be accessible from the actual server where the DB is stored.
SELECT Column1 means look for a column with that as the header (i.e Column1 would be in cell A1 in the spreadsheet)
4. When I execute this code (F5) I got the following an error about
'SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component...' - This meant I had to go to the actual server which is running the SQL server and do this..
a) Open the SQL Server Surface Area configuration.
b) Click 'Surface are configuration for features
c) click the box titled 'Enable OPENROWSET..'

5. Execute the code again and it should work OK.

Note: If you have an autonumber primary key column you should leave Column1 out of the equation as this is obviously assigned automatically.

Hope this helps someone!

John