0

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?

3
  • 1
    How exactly do you define "top 10"? Any 10? What do you want to do about relationships? If you're going to insert "top 10" Orders and top 10 OrderDetails, you either need to disable/drop FK constraints, or spend a lot more time thinking about how exactly you can go after top 10... Aug 21, 2015 at 12:54
  • @AaronBertrand top (10) ordered by the Primary key. I am talking about generating scripts where we have a table with values, we can generate a script that will put those data in the table in a script filled with insert statements
    – AnOldSoul
    Aug 21, 2015 at 13:12
  • 1
    Yes, I understand what you want to do, I just don't know how you will make TOP 10 Orders and TOP 10 OrderDetails and TOP 10 Customers all line up. This would either have to mean that relationships do not matter (and won't be scripted), or you will have to happen to have the first 10 orders consist of exactly one of the first 10 products each, and each ordered by one of the first 10 customers. Aug 21, 2015 at 13:15

1 Answer 1

2

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                              

to be

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.

1
  • Easiest way using SQL Server Data Tools in Visual Studio , here is a post which explains how generate insert statement for first 1000 rows hope helps.
    – Shaiju T
    Nov 22, 2016 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.