how to export top 100 row from many table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

how to export top 100 row from many table

Author

Topic

abc
Starting Member

48 Posts

Posted - 2004-03-02 : 05:08:09

Hi
I need to export data from many tables in my database to another server, but don't know how to export top 100 rows (from each table) for many tables in the same time. Thanks for help

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2004-03-02 : 05:28:45

You can't do it all at once. Do it table by table and for sure you'll need TOP 100.

Lookup BOL under INSERT INTO...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-02 : 05:47:05

Easy if you use bcp.
Just run
select 'master..xp_cmdshell ''bcp "select top 100 * from mydb..' + name + '" queryout c:\' + name + '.bcp -N'''
from sysobjects
where type = 'U'

Then run the result
Do something similar for the import.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abc
Starting Member

48 Posts

Posted - 2004-03-24 : 04:06:55

I try to use bcp command, but after this line:
bcp category out C:\category.txt
int have warning: Server: Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a stored procedure.
I don't understand why so is it ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 12:26:23

Could you post your bcp command?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 13:08:27

[CODE]
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@dbName sysname
, @fp varchar(255)
, @User varchar(255)
, @Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@@SERVERNAME+' -U'+@User+' -P'+@Pwd+' '
+ '-f'+@fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @CMD

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CMD
SELECT @CMD = 'ECHO ' + @CMD + ' > ' + @fp + '\bcpout.bat'
EXEC master..xp_cmdshell @CMD
SELECT @CMD = @fp + '\bcpout.bat'
SELECT @CMD
insert a (s)
exec master..xp_cmdshell @cmd

FETCH NEXT FROM bcpout INTO @CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF

[/CODE]

Brett

8-)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-24 : 20:28:52

Of course, there's no guarantee with these approaches that you'll get related records from different tables, such as the children that actually go with the parents that you've exported. So, if your goal is to create a small test system, you're going to need to put more thought into this to get the appropriate children.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources