SQL Server equivalent of MySQL Dump to produce insert statements for all data in a table

I have an application that uses a SQL Server database with several instances of the database...test, prod, etc... I am making some application changes and one of the changes involves changing a column from a nvarchar(max) to a nvarchar(200) so that I can add a unique constraint on it. SQL Server tells me that this requires dropping the table and recreating it.

I want to put together a script that will do the table drop, recreate it with the new schema, and then reinsert the data that was there previously all in one go, if possible, just to keep things simple for use when I migrate this change to production.

There is probably a good SQL Server way to do this but 开发者_运维百科I'm just not aware of it. If I was using Mysql I would mysqldump the table and its contents, and use that as my script for applying that change to production. I can't find any export functionality in SQL server that will give me a text file consisting of inserts for all data in a table.

Use SQL Server's Generate Scripts command

• right click on the database; Tasks -> Generate Scripts
• select your tables, click Next
• click the Advanced button
• find Types of data to script - choose Schema and Data.
• you can then choose to save to file, or put in new query window.
• results in INSERT statements for all table data selected in bullet 2.

No need to script

here are two ways

1 use alter table ....alter column.....

example..you have to do 1 column at a time

create table Test(SomeColumn nvarchar(max))
go

alter table Test alter column SomeColumn nvarchar(200)
go

2 dump into a new table while converting the column

select <columns except for the columns you want to change>, 
    convert(nvarchar(200),YourColumn) as YourColumn
into SomeNewTable
 from OldTable

drop old table

rename this table to the same table as the old table

EXEC sp_rename 'SomeNewTable', 'OldTable';

Now add your index