-1

I want to generate a script with SQL Server Management Studio for only the values ​​in the table. I only want the top 100. I already found a way to script everything by going to Task -> Generate Scripts...

Generate scripts

I chose data only but it returns the whole table:

Generate script options

I only need the top 100 rows. Is there a way to achieve this?

Thanks!

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
mahiruzun
  • 3
  • 4
  • Why do you want to script the data anyway? Is this some kind of migration scenario? Could [this](https://stackoverflow.com/questions/11560713/generate-insert-script-for-selected-records) resolve your issue? – Sander Sep 21 '20 at 13:45
  • Script the whole table and edit the script to keep only 100 rows? Use `select ... into ...` to copy the `top 100` rows into another table and script that, then edit the table name in the script? – HABO Sep 21 '20 at 13:49
  • the table rows are to large to script. im talking about milion records. I need only the first 100 of them. – mahiruzun Sep 21 '20 at 14:16
  • So you're asking how to use a specific tool to script _rows_ that are too large for it to handle? That would rule out my second suggestion of copying the "first" 100 rows to a separate table and scripting _that_ table. – HABO Sep 21 '20 at 14:48
  • How should the query look like? The script looks like this but it is much bigger: `USE [DB] GO INSERT [dbo].[Person] ([name], [surname] ) VALUES ('peter', 'mcdonald') GO GO INSERT [dbo].[Person] ([name], [surname] ) VALUES ('person2', 'mcdonald') GO GO INSERT [dbo].[Person] ([name], [surname] ) VALUES ('person3', 'mcdonald') GO GO INSERT [dbo].[Person] ([name], [surname] ) VALUES ('person4', 'mcdonald') GO` – mahiruzun Sep 22 '20 at 07:18

1 Answers1

0

If you want to generate a INSERT script for the TOP 100 recordsets in a first step and execute the generated script in a second step you can try this:

DROP TABLE IF EXISTS #source;
DROP TABLE IF EXISTS #target;

CREATE TABLE #source ( value1 INT, value2 VARCHAR(100) );
CREATE TABLE #target ( value1 INT, value2 VARCHAR(100) );

INSERT INTO #source
VALUES (10, 'A100'), (10, 'A101'), (10, 'A102'), (10, 'A103'), (20, 'B100'), (20, 'B101')

-- STEP 1: GENERATE SCRIPT

SELECT TOP 3 sql = 'INSERT INTO #target(value1, value2) VALUES' + '(' + CAST(value1 AS VARCHAR(100)) + ', ''' + value2 + ''');'
FROM #source
ORDER BY value2 DESC

-- STEP 2: EXEC GENERATED SCRIPT

INSERT INTO #target(value1, value2) VALUES(20, 'B101');
INSERT INTO #target(value1, value2) VALUES(20, 'B100');
INSERT INTO #target(value1, value2) VALUES(10, 'A103');

-- RESULT:

SELECT * FROM #target;

The cast and recast to VARCHAR may be exhausting if you have a lot of columns to handle. If it is not necessary to create a interim script it would be easier this way:

INSERT INTO #target(value1, value2)
SELECT TOP 3 value1, value2 FROM #source;
whynot
  • 96
  • 3
  • This is maybe one way of a solution, but it will be a hell of a job to script tables with more then 40 columns... – mahiruzun Sep 30 '20 at 09:24