Geek asks and answers

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

I have an application that uses a SQL Server database with multiple database instances ... test, prod, etc. I am making some application changes, and one of the changes is related to changing the column from nvarchar (max) to nvarchar (200) so that I can add a unique constraint to it. SQL Server tells me that for this you need to reset the table and recreate it.

I want to build a script that will drop a table, recreate it with a new schema, and then reinsert the data that was there earlier, at a time, if possible, just to simplify use when I transfer this change to production.

There is probably a good way to SQL Server, but I just don't know about it. If I used Mysql, I would have mysqldump the table and its contents and used this as my script to apply this change to production. I cannot find any export functions on the SQL server that will provide me with a text file consisting of inserts for all the data in the table.

+3
source share
2 answers

Use SQL Server Generate Scripts Command

  • right click on the database; Tasks → Generate Scripts
  • select your tables, click "Next"
  • Click the "Advanced" button.
  • find the data types in the script - select "Schema and data".
  • You can choose to save to a file or add a new query window.
  • INSERT , 2.

SQL Server Generate Scripts Data

+6

script

:

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

example..You 1

create table Test(SomeColumn nvarchar(max))
go

alter table Test alter column SomeColumn nvarchar(200)
go

2

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

,

EXEC sp_rename 'SomeNewTable', 'OldTable';

+2

More articles:


All Articles