Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Скрипт заполнения таблицы  [new]
Defiler
Member

Откуда:
Сообщений: 21
есть некая таблица. Нужно получить список инсертов для каждой записи.
например

таблица Customer

ID Name
--------
1 Alex
2 John
3 <NULL>

надо получить такой текст:

insert into Customer (ID, Name) values (1, Alex);
insert into Customer (ID, Name) values (2, John);
insert into Customer (ID, Name) values (3, NULL);

и так для любой таблицы

есть какие нибудь соображения по этому поводу?
первую часть(имена полей) я из метаданных выбираю, а вот со значениями происходит затык.


declare 
	@tab varchar(10),
	@s1 varchar(100),
	@fields nvarchar(1000),
	@values nvarchar(4000),
	@table varchar(100),
	@sql nvarchar(4000);

set 	@table = 'Report';  	-- изменить имя таблицы
set 	@tab = '  ';

declare FieldNames cursor local fast_forward
for
select	sc.name	
from 	dbo.sysobjects so, 
	dbo.syscolumns sc,
	dbo.systypes st
where	so.name = @table
	and so.id = sc.id
	and so.xtype = 'U'
	and st.xusertype = sc.xusertype
order by sc.colorder;

open 	FieldNames;
fetch 	next from FieldNames
	into @s1;
while @@FETCH_STATUS = 0
begin
	if @fields is null
		set @fields = @s1 
	else
		set @fields = @fields + ', ' + @s1;

	if @values is null
		set @values =  'isnull(cast(' + @s1 + ' as nvarchar(4000)), '''')'
	else
		set @values = @values + ', ' + 'isnull(cast(' + @s1 + ' as nvarchar(4000)), '''')'

	fetch 	next from FieldNames
		into @s1;
end;
close 	FieldNames;
deallocate FieldNames;
22 сен 05, 19:46    [1903235]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

use master
go

if exists(select * from sysobjects where name = 'sp_generateinsert' and 
type = 'P')
drop procedure sp_generateinsert
go
/*
$Archive: /oep/master/sp_GenerateInsert.sql $
$Author: Locky $
$Revision: 8 $ $Modtime: 24.10.04 16:55 $
*/

create procedure sp_generateinsert
@table_name varchar(255),
@II int = 0
with encryption
as begin
set nocount on
declare	@obj int
select	@obj = object_id(@table_name)


declare	@colnames varchar(8000),@colname varchar(8000),@values 
varchar(8000),@coltype varchar(255)
set	@ColNames = ''
set	@values = ''

select	@ColNames = @ColNames+',['+sc.name+']',
@values = @values +'+'',''+case when ['+sc.Name+'] is null then 
convert(varchar(8000),''null'') else '
+case when st.name in 
('char','varchar','nchar','nvarchar','datetime','smalldatetime') then 
'''''''''+' else '' end
+'convert(varchar(8000),['+sc.name+']'
+case when st.name in ('datetime','smalldatetime') then ',121' else '' end
+')'
+case when st.name in 
('char','varchar','nchar','nvarchar','datetime','smalldatetime') then 
'+''''''''' else '' end
+' end'
from syscolumns sc join systypes st on sc.xtype = st.xtype
where sc.id = @obj
	and (COLUMNPROPERTY(@obj,sc.name,'IsIdentity') = 0 or @ii = 1)
order by sc.colid


declare	@sql varchar(8000)
select	@ColNames = substring(@colNames,2,8000)
select	@values = substring(@values,4,8000)
select	@sql = 'select ''insert into ' + @table_name + '(' + @colNames +')'
print @sql
select @sql = 'values('+ @values+'+'')'''
print @sql
select	@sql = 'from '+@table_name
print @sql
return 0
end

GO
генерирует скрипт который генерирует скрипт заполнения таблицы. Работает
не со всеми типами данных, так-что дорабатывать напильником.
Ну, и могут быть каки со строками большой длины.

--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

22 сен 05, 20:21    [1903306]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
SQL Scripter - http://www.sqlscripter.com/
23 сен 05, 10:39    [1904203]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
Defiler
Member

Откуда:
Сообщений: 21
приведу рабочий вариант, может кому пригодится
(особое спасибо LOCKY)

declare @s1 varchar(100),
	@s2 varchar(100),
	@v varchar(8000),
	@fields varchar(8000),
	@values varchar(8000),
	@table varchar(100),
	@sql varchar(8000);

set 	@table = 'Customer';  	-- изменить имя таблицы

declare FieldNames cursor local fast_forward
for
select	sc.name,		
	case st.name 			-- приводим все известные типы к трем основным
		when 'PrimaryKey' then 'num'
		when 'int' then 'num'
		when 'Color' then 'string'
		when 'datetime' then 'datetime'
		when 'smalldatetime' then 'datetime'
		when 'DateStamp' then 'datetime'
		when 'DateTimeStamp' then 'datetime'
		when 'PriceValue' then 'num'
		when 'Boolean' then 'num'
		when 'Name' then 'string'
		when 'Description' then 'string'
		when 'varchar' then 'string'
		when 'nvarchar' then 'string'
		when 'char' then 'string'
		when 'float' then 'num'
		when 'TicketNumber' then 'num'
		when 'TicketSeries' then 'string'
		when 'URL' then 'string'
		when 'Signature' then 'string'
		when 'RollBarCode' then 'string'
	end		
from 	dbo.sysobjects so, 
	dbo.syscolumns sc,
	dbo.systypes st
where	so.name = @table
	and so.id = sc.id
	and so.xtype = 'U'
	and st.xusertype = sc.xusertype
order by sc.colorder;

open 	FieldNames;
fetch 	next from FieldNames
	into @s1, @s2;
while @@FETCH_STATUS = 0
begin
	if @fields is null
		set @fields = @s1 
	else
		set @fields = @fields + ', ' + @s1;

	set @v = ' case when ' + @s1 + ' is null then ''NULL'' else ' +
		case @s2 
		when 'num' then 'convert(varchar(8000), ' + @s1 + ')'
		when 'string' then ''''''''' + convert(varchar(8000),' + @s1 + ') + ''''''''' 
		when 'datetime' then ''''''''' + convert(varchar(8000), ' + @s1 + ', 121) + '''''''''
		end + ' end'

	if @values is null
		set @values = @v
	else
		set @values = @values + ' + '', '' + ' + @v

	fetch 	next from FieldNames
		into @s1, @s2;
end;
close 	FieldNames;
deallocate FieldNames;

set @sql = 'select ''insert into ' + @table + ' (' + @fields + ')' + char(13) +
 'values (''+' + @values + '+'')''' + char(13) + 'from ' + @table;
--print @sql
exec(@sql)
26 сен 05, 15:46    [1910540]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
Defiler
Member

Откуда:
Сообщений: 21
приведу рабочий вариант, может кому пригодится
(особое спасибо LOCKY)

declare @s1 varchar(100),
	@s2 varchar(100),
	@v varchar(8000),
	@fields varchar(8000),
	@values varchar(8000),
	@table varchar(100),
	@sql varchar(8000);

set 	@table = 'Customer';  	-- изменить имя таблицы

declare FieldNames cursor local fast_forward
for
select	sc.name,		
	case st.name 			-- приводим все известные типы к трем основным
		when 'PrimaryKey' then 'num'
		when 'int' then 'num'
		when 'Color' then 'string'
		when 'datetime' then 'datetime'
		when 'smalldatetime' then 'datetime'
		when 'DateStamp' then 'datetime'
		when 'DateTimeStamp' then 'datetime'
		when 'PriceValue' then 'num'
		when 'Boolean' then 'num'
		when 'Name' then 'string'
		when 'Description' then 'string'
		when 'varchar' then 'string'
		when 'nvarchar' then 'string'
		when 'char' then 'string'
		when 'float' then 'num'
		when 'TicketNumber' then 'num'
		when 'TicketSeries' then 'string'
		when 'URL' then 'string'
		when 'Signature' then 'string'
		when 'RollBarCode' then 'string'
	end		
from 	dbo.sysobjects so, 
	dbo.syscolumns sc,
	dbo.systypes st
where	so.name = @table
	and so.id = sc.id
	and so.xtype = 'U'
	and st.xusertype = sc.xusertype
order by sc.colorder;

open 	FieldNames;
fetch 	next from FieldNames
	into @s1, @s2;
while @@FETCH_STATUS = 0
begin
	if @fields is null
		set @fields = @s1 
	else
		set @fields = @fields + ', ' + @s1;

	set @v = ' case when ' + @s1 + ' is null then ''NULL'' else ' +
		case @s2 
		when 'num' then 'convert(varchar(8000), ' + @s1 + ')'
		when 'string' then ''''''''' + convert(varchar(8000),' + @s1 + ') + ''''''''' 
		when 'datetime' then ''''''''' + convert(varchar(8000), ' + @s1 + ', 121) + '''''''''
		end + ' end'

	if @values is null
		set @values = @v
	else
		set @values = @values + ' + '', '' + ' + @v

	fetch 	next from FieldNames
		into @s1, @s2;
end;
close 	FieldNames;
deallocate FieldNames;

set @sql = 'select ''insert into ' + @table + ' (' + @fields + ')' + char(13) +
 'values (''+' + @values + '+'')''' + char(13) + 'from ' + @table;
--print @sql
exec(@sql)
26 сен 05, 15:56    [1910623]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104760
case st.name -- приводим все известные типы к трем основным
А куда интересно делись стандартные типы данных вроде money, image и тд ?
26 сен 05, 16:13    [1910735]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
Konst_One
Member

Откуда:
Сообщений: 11514
это наверное НЕизвестные типы :)
26 сен 05, 16:26    [1910817]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

Glory wrote:
> / case st.name -- приводим все известные типы к трем основным/
> А куда интересно делись стандартные типы данных вроде money, image и тд ?
ха, только сейчас заметил, что у меня их тоже нету :-) как-то не надо
было - я этим скриптом только настройки переносил, а там в основном
int,varchar,datetime...
надо будет подумать о том, как прикрутить еще UDT, но пока лень.

--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

26 сен 05, 16:33    [1910855]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
Defiler
Member

Откуда:
Сообщений: 21
-- А куда интересно делись стандартные типы данных вроде money, image и тд ?

хехе
кому надо, допишет пару строк
и UDT там уже прикручено

вот блобы и имаджи - вопрос. Ну да ладно
26 сен 05, 16:45    [1910923]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Скрипт заполнения таблицы  [new]
Retter
Member

Откуда: Красноярск
Сообщений: 4
Я, конечно, некропостер, но мне непонятно, зачем тут столько кавычек?

		when 'string' then ''''''''' + convert(varchar(8000),' + @s1 + ') + ''''''''' 
		when 'datetime' then ''''''''' + convert(varchar(8000), ' + @s1 + ', 121) + '''''''''
11 авг 09, 11:51    [7521361]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт заполнения таблицы  [new]
Uladzimir_Hul
Member

Откуда: Минск
Сообщений: 23
Еще один вариант, генерирующий скрипт :

Declare @TableName varchar(64),
@StrColumns varchar(max),
@StrValues varchar(max),
@StrNull char(4),
@SqlStr nvarchar(max),
@return_value int

Set @SqlStr = 'SELECT '
Set @TableName = 'Customers'
Set @StrColumns = ''
Set @StrValues = ''
Set @StrNull = 'NULL'


SELECT @StrColumns = @StrColumns + COLUMN_NAME + ' ,' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME NOT LIKE '%Timestamp%'
ORDER BY ORDINAL_POSITION

SET @StrColumns = SUBSTRING(@StrColumns, 1, Len(@StrColumns)-1)

SELECT @StrValues = @StrValues +
'ISNULL(' + 'CHAR(39) + ' + 'CAST(' + COLUMN_NAME + ' AS VARCHAR(max))' + ' + CHAR(39) ' + ',' + CHAR(39) +'NULL' + CHAR(39) +')' + ' + ' + CHAR(39) + ',' + CHAR(39) + ' + '
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME NOT LIKE '%Timestamp%'
ORDER BY ORDINAL_POSITION

SET @StrValues = SUBSTRING(@StrValues, 1, Len(@StrValues)-6)

SELECT @SqlStr = @SqlStr + CHAR(39) + 'INSERT INTO dbo.' + @TableName + '(' + @StrColumns + ') VALUES(' + CHAR(39) + ' + '
SELECT @SqlStr = @SqlStr + @StrValues
SELECT @SqlStr = @SqlStr + ' + ' + CHAR(39) + ')' + CHAR(39) + ' FROM dbo.' + @TableName
print @SqlStr
EXECUTE @return_value = sp_executesql @SqlStr
11 авг 09, 12:16    [7521637]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить