2

I know that in SQL Server you can right click on a database, go to tasks, generate scripts, pick a table etc. and generate a script that inserts the data into the table as shown here

Is there any way I can restrict the data that is being scripted out by specifying some select clauses? For example, I might want to script out only the top 100 rows in a table or all records from the Employees table that has an IsManager flag set.

If I can't do it using tasks -> generate scripts functionality, is there any other alternative way or tool?

Community
  • 1
  • 1
Chaitanya
  • 5,203
  • 8
  • 36
  • 61
  • I can't find a tasks option when right clicking on a table so I can't try this myself but you might try creating a view and see if there is a tasks -> generate scripts on it. – Kenneth Fisher Mar 22 '13 at 01:00
  • Sorry, it is not on a table, it is on the database itself. I will update the question accordingly. – Chaitanya Mar 22 '13 at 01:10
  • I've been able to generate scripts like that using excel, or dynamic sql queries. I'm still not seeing how to do it under generate scripts though. – Kenneth Fisher Mar 22 '13 at 02:02

2 Answers2

2

I don't know if this can be done directly in SSMS, but you could create a query to return the content of the sql file (at least the insert statements):

SELECT 'insert into yourtable (id,othercol) values (' + cast(id as varchar) + ',''' + othercol + ''');'
FROM yourtable

SQL Fiddle Demo

Be sure to cast your non-varchar fields to varchar.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • An interesting way of doing it. Requires me to do a bit too much typing than I would have liked, but I suppose it gets the job done. – Chaitanya Apr 02 '13 at 02:32
0

Another way would be to create another table with the same schema ("script table as" -> "Create to") and from the original table do a Insert Into with a Select + Where statement.

Then you can do a "generate scripts" on the new table (and replace the tablenames in the output in a text editor).

user2467970
  • 1