SQL Copy 1000 rows per table to new table

[Recht]

Is there an easy way to copy 1000 rows from every table in the database to a new database.

I was thinking about:

set rowcount=1000
INSERT INTO [1435530ODR2]..AAPROCQT
SELECT * FROM [1435530-R]..AAPROCQT

But then I would have to detail all 1700 tables, uggg

Thanks in advance.

Recht

 

[Frink]

Hallo,

You could loop through the tabledefs collection (in MS Access '97)

- Frink

 

[BNPMike]

Always try and think in terms of SQL because that is a good way of thinking about Access. Then, before that, think in,terms of English, because that's what SQL aims to be like (originally "Stuctured English Query language - SEQUEL"). So how would you ask Access the question "Can I have 1000 rows?" The problem is SQL doesn't understand position so you aren't allowed to say "the first 1000" (although some naughty relational databases so do that).

Think of how you are going to select your rows and then it's easy to get Access to do it.

 

[Recht]

Maybe I posted in the wrong area. I am using MS SQL 2000.

I have 254 tables in the database

Each of them has over 1000 rows of data.

I want to use the first 1000 rows from every table and create a new database with those rows.

I could use:
set rowcount = 1000
select * from database..table
into database2..newtable

The problem with this query is I would have to type in the database name for all 254 tables in the database.

I was hoping someone could suggest an easy way to loop through all of the table names.

 

[PHV]

I am using MS SQL 2000
You may get more accurate replies in the SQL Server forum.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

You will probably need to do it in a stored procedure where you can set up a loop with a cursor.

1. stored procedure
2. tables names are in the sysobjects table, I belive type = 'U' but check.
3. Set up a Cursor on the sysobject table so that you can loop.
4. Need to build dynamic SQL.
5. Save dynamic SQL in a Variable
6. Use ExecuteSQl VarName to execute the dynamic SQL.
7. Do until end of loop.