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 involv

Solution 1:

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.

Solution 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

createtable Test(SomeColumn nvarchar(max))
go

altertable Test altercolumn SomeColumn nvarchar(200)
go

2 dump into a new table while converting the column

select<columns exceptfor 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