x

merge two table and generate code at the same time

I want to update my table and at the same time generate unique code for it my destination table contain of code , province code , province name , city code , city name like : 61610001, 61, Tehran, 610001, Firooz 61610002, 61, Tehran, 610002, Damavand 52520001, 52, Kerman, 520001, Bam every day I receive an excel than contain Of a table of new province , city (just name ) that I should insert with a correct code for example if I have a new province my query return max of province code and generate code and city code from that code .and if my province is exists my query insert a row witch province code already exist and generate the maximum code of its level I should mention that a new province is rarely occurred but I want to have perfect code and I generate a code witch is correct when just on row is new but in two records it returns errors

more ▼

asked Dec 02 at 02:59 PM in Default

avatar image

fahime
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

my code that generate error is : merge [dbo].[DimGeography] as target using [dbo].[Center] as source on source.[استان] collate Persian_100_CI_AS = target.[ProvinceName] collate Persian_100_CI_AS and source.[شهر] collate Persian_100_CI_AS = target.[CityName] collate Persian_100_CI_AS

when not matched by target then insert (code , provincecode,provincename, citycode, cityname)

values ( (select distinct ProvinceCode from [dbo].[DimGeography] where [dbo].[DimGeography].ProvinceName collate Persian_100_CI_AS= [ostan] collate Persian_100_CI_AS )*1000000+(select max(citycode)+1 from [dbo].[DimGeography] where [dbo].[DimGeography].ProvinceName collate Persian_100_CI_AS= [ostan] collate Persian_100_CI_AS ),

(select distinct ProvinceCode from [dbo].[DimGeography] where [dbo].[DimGeography].ProvinceName collate Persian_100_CI_AS= [ostan] collate Persian_100_CI_AS ) ,

[استان],(select max(citycode)+1 from [dbo].[DimGeography] where [dbo].[DimGeography].ProvinceName collate Persian_100_CI_AS= [ostan] collate Persian_100_CI_AS ) ,[city]

);

more ▼

answered Dec 04 at 08:55 AM

avatar image

fahime
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x30

asked: Dec 02 at 02:59 PM

Seen: 12 times

Last Updated: Dec 04 at 08:55 AM

Copyright 2017 Redgate Software. Privacy Policy