Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export First 500 rows in SQL 2005 to Access DB

Posted on 2012-09-18
9
Medium Priority
?
472 Views
Last Modified: 2012-09-22
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
Comment
Question by:compdigit44
  • 4
  • 4
9 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38409970
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
 
LVL 3

Expert Comment

by:dave_tiller
ID: 38410060
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
 
LVL 20

Author Comment

by:compdigit44
ID: 38410377
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 12

Expert Comment

by:Jared_S
ID: 38410533
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
 
LVL 20

Author Comment

by:compdigit44
ID: 38410569
What would happen if I change the from table name to From *?
0
 
LVL 20

Author Comment

by:compdigit44
ID: 38410572
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38410698
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
 
LVL 20

Author Comment

by:compdigit44
ID: 38410718
Could it be done in SSIS?
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 2000 total points
ID: 38410868
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question