Author |
Topic |
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-21 : 00:10:59
|
I have three tables (Country table, state table and City table. Country table is primary key(Id) table and State table is foreign key(C-Id) and City table is foreign key(Sid) table. So my problem is when i insert data in table value of id column should come from Font-end application. using the id i retrieve and insert the values with newid() Table design follow as,Country State City-------- --------- ------------- Id --> PK ID ---> PK ID ---> PKName Name Name C-ID --> foreign key(Countrytable) S-ID --> FK(State)Values in Table1. CountryID Name------------------------------- ----------1AA7C63A5F532041BAE588E407A167E3 India2. State ID Name C-ID------------------------------- ------- -------------AF2A025C13CB4C4994CC4A3461234146 State1 1AA7C63A5F532041BAE588E407A167E3 CC78FD8E01883F429F8A960DC7AD41A0 State2 1AA7C63A5F532041BAE588E407A167E33. City ID Name S-ID------------------------------- ------- -------------9F968DA0448E4F7FA87C4AED6D87CD54 City1 AF2A025C13CB4C4994CC4A3461234146 B5F341B342D04CEEBD19B01FD9D2EBF7 City2 AF2A025C13CB4C4994CC4A3461234146844EFE0127C04840BBFDB64CED54C788 City3 CC78FD8E01883F429F8A960DC7AD41A01D4016CF328E447B89764638BBD7EC21 City4 CC78FD8E01883F429F8A960DC7AD41A0ALTER PROCEDURE [dbo].[c_Copy_Country_C] -- Add the parameters for the stored procedure here @FrontEnd-Id varchar(36) = NULL ASSET XACT_ABORT ONBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;--DECLARE @TransactionName varchar(20)Set @TransactionName = 'TransCountry'--Begin TransactionBEGIN TRAN @TransactionName--------------------Begin copy------------------------------------------- Insert new dbo.Country_C record -------------------------------Declare @IdNew varchar(36) Set @IdNew = Replace(CONVERT(varchar(255), NewID()),'-','') Insert Into dbo.Country_C with (ROWLOCK) Select @IdNew,Name from dbo.Country_C with (NOLOCK) where Id = @Frontend-Id-- Insert all dbo.Country_c records------------------------- Insert Into dbo.State_C with (ROWLOCK) Select Replace(CONVERT(varchar(255), NewID()),'-',''),Name,@IdNew from dbo.State_C with (NOLOCK) where Id = @Frontend-Id-- Insert all dbo._City_c records------------------------- Insert Into dbo.City_C with (ROWLOCK) Select Replace(CONVERT(varchar(255), NewID()),'-',''),Name,Replace(CONVERT(varchar(255), NewID()),'-','') from dbo.City_C with (NOLOCK) where S-Id in (Select id from dbo.State_C where C-ID = @Frontend-Id )IF @@ERROR <> 0 BEGIN -- Return 0 to the calling program to indicate failure. ROLLBACK TRAN @TransactionName Select 0 as ReturnState; ENDELSE BEGIN -- Return 1 to the calling program to indicate success. COMMIT TRAN @TransactionName Select 1 as ReturnState; ENDEND In Front end application i execute the procedureExec [Procedurename] 'Frontend-id'Now I need to insert only the name into the three tables with CountryName, StateName and CityName.with create a new IDs.The problem is , Country and state data values inserting.City return multiple values , so that the City names missing.How to Solve, and please correct the llines..Many Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 00:40:11
|
when you insert data to Cities you wont be having any data for current S-id value right, then how will that IN condition ever return any records?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-21 : 01:08:04
|
Right. Thanks for the reply. can you help me and correct the 3rd querywhat is the Solution ?So please help me with answers.Thanks for the reply. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 01:20:15
|
i think what you need is to use OUTPUT clause to capture the currently inserted state id and then use it for the insertion logic to City table.I dont know from where you get values for Name etc fields to be inserted to City table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-21 : 01:52:22
|
i need to insert the city name (City1,City2) with new id, (That means i copy & paste the values with new IDs)Condtion is,C-ID = @FrontEnd-Id Output is,CityID Name S-ID------------------------------- ------- -------------9F968DA0448E4F7FA87C4AED6D87CD54 City1 AF2A025C13CB4C4994CC4A3461234146B5F341B342D04CEEBD19B01FD9D2EBF7 City2 AF2A025C13CB4C4994CC4A3461234146844EFE0127C04840BBFDB64CED54C788 City3 CC78FD8E01883F429F8A960DC7AD41A01D4016CF328E447B89764638BBD7EC21 City4 CC78FD8E01883F429F8A960DC7AD41A0Here i add a same values,but new IDs(S-ID(generate from 2nd query)) A417BF27C29647CE8D8DC53FF195353E City1 20B08CEAF7D14839A9DC69C3AEB689E97DB66522445D4482B94463F39A007240 City2 20B08CEAF7D14839A9DC69C3AEB689E9Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 02:00:27
|
ok...so are you trying to replicate existing values for new state? but how would city repeat for every state? doesnt sound sensible to me------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-21 : 02:39:11
|
Ok, that's not a problem (city repeated is ok), bcoz some state having same city name..Pls tell me correct query...Many thanks.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 03:31:50
|
quote: Originally posted by teamjai Ok, that's not a problem (city repeated is ok), bcoz some state having same city name..Pls tell me correct query...Many thanks..
what does this return?Select id from dbo.State_C where C-ID = @Frontend-Id------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-21 : 04:17:18
|
Return@Frontend-ID --> id (from Country table)Select id from dbo.State_C where C-ID = '1AA7C63A5F532041BAE588E407A167E3'id---AF2A025C13CB4C4994CC4A3461234146CC78FD8E01883F429F8A960DC7AD41A0Ref:Select Name from dbo.City_C where S-ID ='AF2A025C13CB4C4994CC4A3461234146'Name------City 1Select Name from dbo.City_C where S-ID ='CC78FD8E01883F429F8A960DC7AD41A0'Name----City2 |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-21 : 06:35:23
|
Hi any solution ? |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-21 : 22:44:36
|
i am waiting for Your solution... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-22 : 10:01:05
|
doesnt look to have any issues. only probability is S-ID having some unprintable characters in State table (spaces etc)Whats the datatype of id in State table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-23 : 01:29:12
|
State table -------------------Id -> Varchar(36)Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-23 : 01:32:11
|
ok..check if len corresponds to data that you actually seeSELECT id,LEN(id) from dbo.State_C------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-23 : 03:21:34
|
the result is,id No Column name ----------------- --------------------9F968DA0448E4F7FA87C4AED6D87CD54 32 B5F341B342D04CEEBD19B01FD9D2EBF7 32 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-23 : 03:51:18
|
what about city table?Select [S-ID],LEN([S-ID]) from dbo.City_C------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-23 : 04:35:59
|
same result in Country, State and City id'sid NoColumnname----------------- -------------------- AF2A025C13CB4C4994CC4A3461234146 32 AF2A025C13CB4C4994CC4A3461234146 32 |
|
|
|