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
-
1How 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...– Aaron BertrandAug 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– AnOldSoulAug 21, 2015 at 13:12
-
1Yes, 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.– Aaron BertrandAug 21, 2015 at 13:15
Add a comment
|
1 Answer
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.
