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);

Answer 1

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;