SQL Advanced scripting options

Question

I want to generate a script which will give me an "insert" query with data from my existing database, but only top 1000 rows ordered by ID from each table. I tried to generate a query for a single table by using "Generate scripts..." which is located in Management Studio (MyDatabaseName -> Tasks -> Generate scripts...) and then I wrote a simple function in C# which cut the data to 1000 first rows but it's not a good idea when you have hundreds of tables

List<string> script = File.ReadLines(@"script.sql").Take(1000).ToList();
File.WriteAllLines(@"top1000.sql", script);

That seems like a good idea. Now, what's wrong? Did you try something and it didn't work? You should [edit] to let us know 1) what you tried 2) what was the result 3) why this result was not acceptable to you — , Mar 22 '17 at 14:33
you can take a look [here](http://stackoverflow.com/questions/8668511/how-can-i-select-top-500-rows-from-table1-and-insert-them-to-table2). This will help you. — Iulian Iulian, Mar 22 '17 at 14:36
If you want to limit this to 1000 rows you will need to define what you mean by top in every table. By definition a table is an unordered set, the concept of top requires an order. Unless you just want any 1000 rows. But then the requirement seems quite strange to just get 1000 rows from every table. Almost like creating a test database but it would likely be one that wouldn't work because of RI. — Sean Lange, Mar 22 '17 at 14:40
Generating a script like that should be fairly easy with SSMS Generate script and a simple find & replace, However as Sean Lange pointed out, taking 1000 rows from a table without specifing an order by clause means you can't guarantee what rows will return from each table. — Zohar Peled, Mar 22 '17 at 14:52

LukStorms · Accepted Answer · 2017-03-22T16:13:17.087

The script below will generate statements for all tables in a source database to have 1000 random records copied to another empty database.

Note that a "select * insert into ..." statement will only work if the target table doesn't already exist.

The ORDER BY uses the primary key of the table.
If it has one.

SELECT CONCAT('SELECT ',
'TOP 1000 ',
'* INSERT INTO ',
'[TargetDb]', -- replace with the name of the database you want to copy to
'.',QUOTENAME(t.TABLE_NAME),
' FROM ',QUOTENAME(t.TABLE_SCHEMA),'.',QUOTENAME(t.TABLE_NAME),
' ORDER BY ' + QUOTENAME(k.PrimaryKeyName),';'
) as InsertStatement
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN (
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME as PrimaryKeyName
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(CONSTRAINT_SCHEMA) + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
) k ON (t.TABLE_SCHEMA = k.TABLE_SCHEMA and t.TABLE_NAME = k.TABLE_NAME)
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_SCHEMA = 'dbo' -- replace with the name of the source database you want to copy from

ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME;

SQL Advanced scripting options

1 Answers1