November 26, 2009 at 9:23 pm
Hi,
can anyone provide me a solution or a tool or a script that can help me in achieving my following goal
i want to fetch first few rows of every table from my database e.g. say i have 50 tables with 100 rows in each table. now i want to select first 5 rows or n rows from each 50 tables but with a single script or some tool or some method..
it will be a kind of small backup of whole database with first few rows and it can be used as dummy entries for test database.
November 26, 2009 at 11:27 pm
definitely with bcp utility, and a query inside....
C:\>bcp "select top 50 * from DatabaseName.dbo.Table_Name" queryout Test.bak -U username -P password -c -t
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 27, 2009 at 4:57 am
Well in this situation I would create the new database where I want the data to go and run something like the following:
DECLARE @object_id int, @schema_id int, @cmd varchar(max), @dbname varchar(20)
SET @dbname = 'new_database_name'
DECLARE special_backup CURSOR FOR
select [object_id], [schema_id] from sys.objects
where [type] = 'U'
OPEN special_backup
FETCH NEXT FROM special_backup
INTO @object_id, @schema_id
WHILE @@fetch_status = 0
BEGIN
SET @cmd = '
select top 10 * into ' + @dbname + '.dbo.' + object_name(@object_id) + '
from ' + schema_name(@schema_id) + '.' + object_name(@object_id) +''
EXEC (@cmd)
FETCH NEXT FROM special_backup
INTO @object_id, @schema_id
END
CLOSE special_backup
DEALLOCATE special_backup
what this will do is take the top 10 rows from each table in the current database and create new tables in the new_database_name(that db must exist) with those 10 rows.
November 27, 2009 at 7:49 am
This is what i would suggest, will work well with SQL Server 2005. If you need the same in SQL server 2000 then you can replace @STR varibles data type with varchar(8000), however if your database contains large number of tables varchar(800) will not work....
use master
create database DummyData -- Create a database where you want to copy the sample data
declare @STR varchar(max)
set @STR = ''
select @STR = @STR + ' select top 5 * into DummyData..[' + name + '] from [' + name + ']' from
sys.tables where type = 'U'
exec (@str)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply