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 — user1228
– user1228, Commented Mar 22, 2017 at 14:33
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
– Sean Lange, Commented Mar 22, 2017 at 14:40

LukStorms · Accepted Answer · 2017-03-22 16:13:17Z

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;

Collectives™ on Stack Overflow

SQL Advanced scripting options

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related