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
LVL 21
compdigit44Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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

compdigit44Author Commented:
What would happen if I change the from table name to From *?
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
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.
compdigit44Author Commented:
Could it be done in SSIS?
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.