Перенос данных из СТАРОЙ базы в НОВУЮ

[dnclive]

Скрипты по перебросу данных из ecad_venta в ecad_etalon

http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match

[dnclive]

Экспорт материалов тех что еще не экспортированы not in

goodgroup

insert into ecad_etalon.dbo.goodgroup 
(
    idgoodgroup,parentid,name,deleted,comment,isactive,isvisible,numpos,guid
)

select 

idgoodgroup,parentid,name,deleted,comment,isactive,isvisible,numpos,guid

from ecad_venta.dbo.goodgroup  
where 
    --deleted is null 
    --and 
    idgoodgroup not in (select idgoodgroup from ecad_etalon.dbo.goodgroup )

good

insert into ecad_etalon.dbo.good  
(
    idgood, 
    idmeasure,
    name,
    marking,
    deleted,
    comment,
    typ,
    extmarking,
    height,
    width,
    thick,
    idgoodgroup,
    usehouse,
    ismy,
    thickness,
    price1,
    price2,
    idsystem,
    waste,
    idvalut,
    idcolor1,
    idcolor2,
    idgoodtype,
    minost,
    price1crypt,
    idgoodpricegroup,
    idgoodoptim,
    guid,
    price3,
    price4,
    price5,
    idvalut2,
    idvalut3,
    idvalut5,
    packing,
    idgoodtype2,
    weight,
    idstoredepart,
    sqr,
    sqr2,
    waste2,
    waste3,
    waste4,
    waste5,
    showinnopaper
)

select 

idgood, 
    idmeasure,
    name,
    marking,
    deleted,
    comment,
    typ,
    extmarking,
    height,
    width,
    thick,
    idgoodgroup,
    usehouse,
    ismy,
    thickness,
    price1,
    price2,
    idsystem,
    waste,
    idvalut,
    idcolor1,
    idcolor2,
    idgoodtype,
    minost,
    price1crypt,
    idgoodpricegroup,
    idgoodoptim,
    guid,
    price3,
    price4,
    price5,
    idvalut2,
    idvalut3,
    idvalut5,
    packing,
    idgoodtype2,
    weight,
    idstoredepart,
    sqr,
    sqr2,
    waste2,
    waste3,
    waste4,
    waste5,
    showinnopaper

from ecad_venta.dbo.good 
where 
    --deleted is null 
    --and 
    idgood not in (select idgood from ecad_etalon.dbo.good)

-- обновление всех
update ecad_etalon.dbo.good

    set ecad_etalon.dbo.good.idgood=ev.idgood,
        ecad_etalon.dbo.good.idmeasure=ev.idmeasure,
        ecad_etalon.dbo.good.name=ev.name,
        ecad_etalon.dbo.good.marking=ev.marking, 
        ecad_etalon.dbo.good.deleted=ev.deleted,
        ecad_etalon.dbo.good.comment=ev.comment, 
        ecad_etalon.dbo.good.typ=ev.typ, 
        ecad_etalon.dbo.good.extmarking=ev.extmarking,
        ecad_etalon.dbo.good.height=ev.height,
        ecad_etalon.dbo.good.width=ev.width,
        ecad_etalon.dbo.good.thick=ev.thick,
        ecad_etalon.dbo.good.idgoodgroup=ev.idgoodgroup,
        ecad_etalon.dbo.good.usehouse=ev.usehouse,
        ecad_etalon.dbo.good.ismy=ev.ismy,
        ecad_etalon.dbo.good.thickness=ev.thickness,
        ecad_etalon.dbo.good.price1=ev.price1,
        ecad_etalon.dbo.good.price2=ev.price2,
        ecad_etalon.dbo.good.idsystem=ev.idsystem,
        ecad_etalon.dbo.good.waste=ev.waste,
        ecad_etalon.dbo.good.idvalut=ev.idvalut,
        ecad_etalon.dbo.good.idcolor1=ev.idcolor1,
        ecad_etalon.dbo.good.idcolor2=ev.idcolor2,
        ecad_etalon.dbo.good.idgoodtype=ev.idgoodtype,
        ecad_etalon.dbo.good.minost=ev.minost,
        ecad_etalon.dbo.good.price1crypt=ev.price1crypt,
        ecad_etalon.dbo.good.idgoodpricegroup=ev.idgoodpricegroup,
        ecad_etalon.dbo.good.idgoodoptim=ev.idgoodoptim,
        ecad_etalon.dbo.good.guid=ev.guid,
        ecad_etalon.dbo.good.price3=ev.price3,
        ecad_etalon.dbo.good.price4=ev.price4,
        ecad_etalon.dbo.good.price5=ev.price5,
        ecad_etalon.dbo.good.idvalut2=ev.idvalut2,
        ecad_etalon.dbo.good.idvalut3=ev.idvalut3,
        ecad_etalon.dbo.good.idvalut4=ev.idvalut4,
        ecad_etalon.dbo.good.idvalut5=ev.idvalut5,
        ecad_etalon.dbo.good.packing=ev.packing,
        ecad_etalon.dbo.good.idgoodtype2=ev.idgoodtype2,
        ecad_etalon.dbo.good.weight=ev.weight,
        ecad_etalon.dbo.good.idstoredepart=ev.idstoredepart,
        ecad_etalon.dbo.good.sqr=ev.sqr,
        ecad_etalon.dbo.good.sqr2=ev.sqr2,
        ecad_etalon.dbo.good.waste2=ev.waste2,
        ecad_etalon.dbo.good.waste3=ev.waste3,
        ecad_etalon.dbo.good.waste4=ev.waste4,
        ecad_etalon.dbo.good.waste5=ev.waste5,
        ecad_etalon.dbo.good.showinnopaper=ev.showinnopaper
--select 
--  idgoodtype,name, numpos, comment, deleted, numpos, numpos2, numpos3, guid
from ecad_venta.dbo.good ev, ecad_etalon.dbo.good et
where 
    ev.idgood=et.idgood

[dnclive]

Добавляет строки goodcologroupprice для материалов у которых их нет

вроде работает

declare @idgood int, @idvalut int, @price1 numeric, @idgoodcolorgroupprice int

declare good_c cursor for 
select idgood, idvalut, price1 from ecad_etalon.dbo.good
where idgood not in (select idgood from goodcolorgroupprice where deleted is null)

OPEN good_c

FETCH NEXT FROM good_c
INTO @idgood, @idvalut, @price1

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM good_c
INTO @idgood, @idvalut, @price1

print @idgood--, @idvalut

exec ecad_etalon.dbo.gen_id4 'gen_goodcolorgroupprice', 4,  @idgoodcolorgroupprice output

insert into goodcolorgroupprice (idgoodcolorgroupprice, idcolorgroupprice, idvalut, idgood, deleted, price, k1, k2)
values (@idgoodcolorgroupprice, 29, @idvalut, @idgood, null, @price1, 1, 1)

end

CLOSE good_c;
DEALLOCATE good_c;

[dnclive]

типы материалов

--добавление новых
insert into ecad_etalon.dbo.goodtype
(
    idgoodtype,name, numpos, comment, deleted, numpos2, numpos3, guid
)
select 
    idgoodtype,name, numpos, comment, deleted, numpos2, numpos3, guid
from ecad_venta.dbo.goodtype 
where 
    idgoodtype not in (select idgoodtype from ecad_etalon.dbo.goodtype )

-- обновление всех
update ecad_etalon.dbo.goodtype 

    set ecad_etalon.dbo.goodtype.idgoodtype=ev.idgoodtype,
        ecad_etalon.dbo.goodtype.name=ev.name,
        ecad_etalon.dbo.goodtype.numpos=ev.numpos, 
        ecad_etalon.dbo.goodtype.comment=ev.comment, 
        ecad_etalon.dbo.goodtype.deleted=ev.deleted,
        ecad_etalon.dbo.goodtype.numpos2=ev.numpos2, 
        ecad_etalon.dbo.goodtype.numpos3=ev.numpos3, 
        ecad_etalon.dbo.goodtype.guid=ev.guid

from ecad_venta.dbo.goodtype ev, ecad_etalon.dbo.goodtype et
where 
    ev.idgoodtype=et.idgoodtype