• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

Export First 500 rows in SQL 2005 to Access DB

I need to extract the data from one of our SQL 2005 to an Access DB. The DB has about 20 or so tables. Using the export wizard how can I extract data from all the tables but limit the extract to the first 500 rows of each table only
0
compdigit44
Asked:
compdigit44
  • 4
  • 4
1 Solution
 
Jared_SCommented:
With just 20 tables, I'd just write the sql select statement in the wizards query space.

select top 500 * from MyTable 1

...

Just be mindful of your relationships between your tables when you do something like this.
You might end up leaving off some of the "many" in a one-to-many relationship.
Depending on your plans, you may need to only pull records where your keys match the keys of previously exported records.
0
 
dave_tillerCommented:
When using the Data Export Wizard, you would specify a query.  Then perform a "select top 500 into" query that would move the records.
0
 
compdigit44Author Commented:
how would I write teh query to automatiaclly pull the top 500 rows for ALL tables automatically instead of me having to listed each table...

Sorry I'm not a SQL DBA
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jared_SCommented:
You'll have to do this one table at a time as far as I know.  
Running this against your sql server database will generate the text of the queries for you though.

(changing "databasename" to the name of your database)

USE databasename
GO 
SELECT 'SELECT TOP 500 *  FROM ' + name 
FROM sys.Tables

Open in new window

0
 
compdigit44Author Commented:
What would happen if I change the from table name to From *?
0
 
compdigit44Author Commented:
There has to be a way to run the export to export all table automatically but limit the results to only the top 500 rows
0
 
Jared_SCommented:
From * would get you an error.

If your goal is to execute as few commands as possible you could create a new database using only the top 500 records of each table, export that database to access, and then drop the database from sql server.

I don't think Microsoft saw this as a very common requirement. To my knowledge there is not any built-in method for limiting the export or import to a specif row count.
0
 
compdigit44Author Commented:
Could it be done in SSIS?
0
 
Jared_SCommented:
Technically, but it would be harder and take longer than any of these other ways.

If you have enough rights on your database, just do this:

step 1: create a new database, I'm calling it my500
create database my500

Open in new window


step2: generate commands that will pull the first 500 records from your database. Change "myDatabase" to the name of the database your pulling from...
SELECT 'SELECT TOP 500 * into my500.dbo.' + name + ' FROM ' + 'myDatabase.dbo.' + name 
FROM sys.Tables

Open in new window


step3: copy the results of that query, paste them in a query window, and execute

step4: use the wizard in access or in sql server to transfer the my500 database to access

step 5: remove the my500 database
drop database my500

Open in new window


And your done. The whole thing could be done in under 10 minutes.

You will have an access database with the first 500 rows of data from every table in your database. The table and field names will be the same in your access database as they are in your sql database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now