CODEWITHSUNDEEP
sql-server
Chaitanya
Chaitanya

Reputation: 5293

Generate a script of inserts from existing data with a select clause in SQL Server

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?

Upvotes: 2

Views: 5831

Answers (2)

user2467970
user2467970

Reputation: 1

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

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

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.

Upvotes: 2

Related Questions