Saturday, September 5, 2009

How to generate data script for a table in SQL Server

Hi,

In your SQL Server database you might be having some master tables which contains master data entries of your application,

schema of these tables you can easily generate from SQL Server table properties and generate schema options, now if you want to generate data script of all the existing data in the table then you can use below SQL script to do that work

Say AppRoles table contains following data

AppRoleId RoleDescription Status
1 Admin 1
2 User 1
3 Guest 1
4 Demo User 1


select 'insert into tAppRoles (AppRoleID, RoleDescription, Status) values (''' + AppROleId + ''',''' + RoleDescription + ''',' + convert(varchar(1),status) + ')' from tAppRoles


Now when you will run above script in your database it will give you following result set

insert into tAppRoles (AppROleId, RoleDescription, Status) values (1, 'Admin', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (2, 'User', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (3, 'Guest', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (4, 'DemoUser', 1)

So this way you can get insert script of all the records, now just save it ....you are done !!!



No comments:

Post a Comment