How can I generate a script to choose only top 10 items from each table in SQL database?
I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records
only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements? These top 10 should be ordered by the primary key. Please
advi
August 22nd, 2015 10:52am
I used the below stored procedure that would create the insert statements containing the top 10 records.
CREATE procedure [dbo].[INS] ( @Query Varchar(MAX) ) AS Set nocount ON DEclare @WithStrINdex as INT DEclare @WhereStrINdex as INT DEclare @INDExtouse as INT Declare @SchemaAndTAble VArchar(270) Declare @Schema_name varchar(30) Declare @Table_name varchar(240) declare @Condition Varchar(MAX) SET @WithStrINdex=0 SELECT @WithStrINdex=CHARINDEX('With',@Query ) , @WhereStrINdex=CHARINDEX('WHERE', @Query) IF(@WithStrINdex!=0) Select @INDExtouse=@WithStrINdex ELSE Select @INDExtouse=@WhereStrINdex Select @SchemaAndTAble=Left (@Query,@INDExtouse-1) select @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble)) Select @Schema_name= Left (@SchemaAndTAble, CharIndex('.',@SchemaAndTAble )-1) , @Table_name = SUBSTRING( @SchemaAndTAble , CharIndex('.',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) ) , @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6 Declare @COLUMNS table (Row_number SmallINT , Column_Name VArchar(Max) ) Declare @CONDITIONS as varchar(MAX) Declare @Total_Rows as SmallINT Declare @Counter as SmallINT declare @ComaCol as varchar(max) select @ComaCol='' Set @Counter=1 set @CONDITIONS='' INsert INTO @COLUMNS Select Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FRom INformation_schema.columns Where Table_schema=@Schema_name And table_name=@Table_name select @Total_Rows= Count(1) FRom @COLUMNS Select @Table_name= '['+@Table_name+']' Select @Schema_name='['+@Schema_name+']' While (@Counter<=@Total_Rows ) begin --PRINT @Counter select @ComaCol= @ComaCol+'['+Column_Name+'],' FROM @COLUMNS Where [Row_number]=@Counter select @CONDITIONS=@CONDITIONS+ ' +Case When ['+Column_Name+'] is null then ''Null'' Else ''''''''+ Replace( Convert(varchar(Max),['+Column_Name+'] ) ,'''''''','''' ) +'''''''' end+'+''',''' FROM @COLUMNS Where [Row_number]=@Counter SET @Counter=@Counter+1 End select @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2) select @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4) select @ComaCol= substring (@ComaCol,0, len(@ComaCol) ) select @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS select @CONDITIONS=@CONDITIONS+'+'+ ''')''' Select @CONDITIONS= 'Select top 10 '+@CONDITIONS +'FRom ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition print(@CONDITIONS) Exec(@CONDITIONS)
Executing it as shown below would get me the insert statements.
EXEC dbo.ins 'item.ItemClass where 1=1 order by ItemClassID'
Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2015 8:28pm
As you mentioned you already have your query. Make sure it is order by clause is included
Then use the top 10 option.
Example:
below query returns all the rows based on salary earned.
Select * form employee order by max(salary)
Below query only returns top 10 salary earners.
Select top 10 * form employee order by max(salary)
August 24th, 2015 12:42am
Select top 10 rows form all user defined tabled using a cursor .Select statement should b
Free Windows Admin Tool Kit Click here and download it now
August 24th, 2015 1:25am