مقدمه :
در طول مدت توسعه نرم افزار، معمولا پایگاه داده طراحی شده و از اطلاعات آزمایشی پر می شوند.
برخی از این اطلاعات صرفا آزمایشی بوده و هنگامی که قرار است نرم افزار به مشتریان تحویل داده شود، باید جداولی که حاوی این اطلاعات می باشند خالی شوند.
ولی برخی داده ها نیز وجود دارند که باید همواره در پایگاه داده وجود داشته باشند. به طور مثال داده هایی مانند لیست نام کشور ها ، استان ها ، نام بانک ها و غیره از این قبیل اطلاعات می باشند که وجود آن ها در جداول ضروری می باشد.
بنابراین نیاز است، هنگامی که می خواهیم اسکریپت مربوط به دیتابیس را بسازیم ، داده های مورد نیاز جداولی که اطلاعات آن ها باید هنگام ساخت پایگاه داده جدید وارد شوند نیز تولید شوند.
متاسفانه، SQL Server Management Studio این امکان را به ما نمی دهد و صرفا اسکریپتی را می سازد که ساختار جداول و سایر عناصر پایگاه داده را تولید می کنند و نه داده های مورد نظر را.
ولی با استفاده از اسکریپت نویسی می تواند این مشکل را حل نمود. راه های مختلفی برای انجام این کار وجود دارد که در این ترفند به معرفی یکی از آن ها می پردازم.
در قسمت زیر شما یک روال ذخیره شده می بینید که نام یک جدول را به عنوان پارامتر می گیرد و رکورد های موجود در آن را بر می گرداند
if exists
(
select * from sys.objects
where
[object_id] = object_id(N'[dbo].[usp_generate_inserts]')
and type in (N'P', N'PC')
)
drop proc [dbo].[usp_generate_inserts];
go
create proc dbo.usp_generate_inserts
(
@table nvarchar(255)
)
as
begin
set nocount on
declare @is_identity bit;
declare @columns nvarchar(max);
declare @values nvarchar(max);
declare @script nvarchar(max);
if isnull(charindex('.', @table), 0) = 0
begin
print 'Procedure dbo.usp_generate_inserts expects a table_name parameter in the form of schema_name.table_name';
end
else
begin
-- initialize variables as otherwise the padding will fail (return nulls for nvarchar(max) types)
set @is_identity = 0;
set @columns = '';
set @values = '';
set @script = '';
/*
The following select makes an assumption that the identity column should be included in
the insert statements. Such inserts still work when coupled with identity_insert toggle,
which is typically used when there is a need to "plug the holes" in the identity values.
Please note the special handling of the text data type. The type should never be present
in SQL Server 2005 tables because it will not be supported in future versions, but there
are unfortunately plenty of tables with text columns out there, patiently waiting for
someone to upgrade them to varchar(max).
*/
select
@is_identity = @is_identity | columnproperty(object_id(@table), column_name, 'IsIdentity'),
@columns = @columns + ', ' + '['+ column_name + ']',
@values =
@values + ' + '', '' + isnull(master.dbo.fn_varbintohexstr(cast(' +
case data_type
when 'text' then 'cast([' + column_name + '] as varchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'
from
INFORMATION_SCHEMA.COLUMNS
where
table_name = substring(@table, charindex('.', @table) + 1, len(@table))
and data_type != 'timestamp'
order by ordinal_position;
set @script =
'select ''insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) +
') values ('' + ' + substring(@values, 11, len(@values)) + ' + '');'' from ' + @table + ';';
if @is_identity = 1
print ('set identity_insert ' + @table + ' on');
/*
generate insert statements. If the results to text option is set and the query results are
completely fit then the prints are a part of the batch, but if the results to grid is set
then the prints (identity insert related) can be gathered from the messages window.
*/
exec sp_executesql @script;
if @is_identity = 1
print ('set identity_insert ' + @table + ' off');
end
set nocount off
end
go
نحوه استفاده از روال ذخیره شده بالا به شکل زیر می باشد.
exec usp_generate_inserts 'dbo.tbl_Bank'
در قطعه کد بالا ما نام جدول مورد نظر خود را (بنده از جدولی به نام بانک که حاوی نام چند بانک می باشد استفاده نموده ام) که در اینجا جدول بانک ها می باشد را به عنوان پارامتر به روال ذخیره شده usp_generate_inserts داده ایم و خروجی این روال به شکل زیر می باشد.
تذکر مهم:
ذکر نام Schema در هنگام نوشتن نام جدول الزامی می باشد(در اینجا dbo می باشد)
با اجرای دستورات Insert نمایش داده شده در شکل بالا، اطلاعات زیر وارد جدول ما می شود.
بروز رسانی در تاریخ 13 تیرماه 1388:
در نسخه SQL Server 2008 عمل انجام شده در این ترفند از طریق SQL Server Management Studio امکان پذیر می باشد. چهت کسب اطلاعات بیشتر به اینجا مراجعه نمایید.
فایل انجام شده در این ترفند از لینک بالای صفحه قابل دریافت می باشد.
برگرفته از : SqlServerCentral