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?
If you go to the following stack exchange link and scroll down to the second answer where you see Neeraj Prasad Sharma create a stored procedure named INS, if you go into that stored procedure and modify the line
Select @CONDITIONS= 'Select '+@CONDITIONS +'FRom ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition
Select @CONDITIONS= 'Select top 10 '+@CONDITIONS +'FRom ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition
Here is a quick table I put together so you can do that and run it to see how it works
CREATE TABLE tbltest ( id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,col1 VARCHAR(20) ); GO INSERT INTO dbo.tbltest( col1 ) VALUES ( 'test1' ), ( 'test2' ),( 'test3' ),( 'test4' ); GO EXEC ins 'dbo.tblTest where 1=1 order by id'
From here you might have to generate your Exec INS executions by using sys.objects or information_schema.tables, but this should do what you are looking to do as long as I understand correctly what you are trying to do. As Mr. Bertrand said in his comment, this does not take any relationships into account. But if you are just looking to generate top 10 inserts for each table ordered by something, this should do that for you.