| Author |
 Topic  |
|
|
teamjai
Starting Member
India
42 Posts |
 Posted - 01/21/2013 : 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 ---> PK
Name Name Name
C-ID --> foreign key(Countrytable) S-ID --> FK(State)
Values in Table
1. Country
ID Name
------------------------------- ----------
1AA7C63A5F532041BAE588E407A167E3 India
2. State
ID Name C-ID
------------------------------- ------- -------------
AF2A025C13CB4C4994CC4A3461234146 State1 1AA7C63A5F532041BAE588E407A167E3
CC78FD8E01883F429F8A960DC7AD41A0 State2 1AA7C63A5F532041BAE588E407A167E3
3. City
ID Name S-ID
------------------------------- ------- -------------
9F968DA0448E4F7FA87C4AED6D87CD54 City1 AF2A025C13CB4C4994CC4A3461234146
B5F341B342D04CEEBD19B01FD9D2EBF7 City2 AF2A025C13CB4C4994CC4A3461234146
844EFE0127C04840BBFDB64CED54C788 City3 CC78FD8E01883F429F8A960DC7AD41A0
1D4016CF328E447B89764638BBD7EC21 City4 CC78FD8E01883F429F8A960DC7AD41A0
ALTER PROCEDURE [dbo].[c_Copy_Country_C]
-- Add the parameters for the stored procedure here
@FrontEnd-Id varchar(36) = NULL
AS
SET XACT_ABORT ON
BEGIN
-- 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 Transaction
BEGIN 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;
END
ELSE
BEGIN
-- Return 1 to the calling program to indicate success.
COMMIT TRAN @TransactionName
Select 1 as ReturnState;
END
END
In Front end application i execute the procedure
Exec [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
India
51228 Posts |
Posted - 01/21/2013 : 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 MVP
http://visakhm.blogspot.com/
|
 |
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/21/2013 : 01:08:04
|
Right. Thanks for the reply.
can you help me and correct the 3rd query
what is the Solution ?
So please help me with answers.
Thanks for the reply. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
51228 Posts |
Posted - 01/21/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/21/2013 : 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,
City
ID Name S-ID
------------------------------- ------- -------------
9F968DA0448E4F7FA87C4AED6D87CD54 City1 AF2A025C13CB4C4994CC4A3461234146
B5F341B342D04CEEBD19B01FD9D2EBF7 City2 AF2A025C13CB4C4994CC4A3461234146
844EFE0127C04840BBFDB64CED54C788 City3 CC78FD8E01883F429F8A960DC7AD41A0
1D4016CF328E447B89764638BBD7EC21 City4 CC78FD8E01883F429F8A960DC7AD41A0
Here i add a same values,but new IDs(S-ID(generate from 2nd query))
A417BF27C29647CE8D8DC53FF195353E City1 20B08CEAF7D14839A9DC69C3AEB689E9
7DB66522445D4482B94463F39A007240 City2 20B08CEAF7D14839A9DC69C3AEB689E9
Thanks
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
51228 Posts |
Posted - 01/21/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/21/2013 : 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
India
51228 Posts |
Posted - 01/21/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/21/2013 : 04:17:18
|
Return
@Frontend-ID --> id (from Country table)
Select id from dbo.State_C where C-ID = '1AA7C63A5F532041BAE588E407A167E3'
id
---
AF2A025C13CB4C4994CC4A3461234146
CC78FD8E01883F429F8A960DC7AD41A0
Ref:
Select Name from dbo.City_C where S-ID ='AF2A025C13CB4C4994CC4A3461234146'
Name
------
City 1
Select Name from dbo.City_C where S-ID ='CC78FD8E01883F429F8A960DC7AD41A0'
Name
----
City2 |
|
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/21/2013 : 06:35:23
|
| Hi any solution ? |
|
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/21/2013 : 22:44:36
|
| i am waiting for Your solution... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
51228 Posts |
Posted - 01/22/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/23/2013 : 01:29:12
|
State table
-------------------
Id -> Varchar(36)
Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
51228 Posts |
Posted - 01/23/2013 : 01:32:11
|
ok..check if len corresponds to data that you actually see
SELECT id,LEN(id) from dbo.State_C
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/23/2013 : 03:21:34
|
the result is,
id No Column name
----------------- --------------------
9F968DA0448E4F7FA87C4AED6D87CD54 32
B5F341B342D04CEEBD19B01FD9D2EBF7 32 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
51228 Posts |
Posted - 01/23/2013 : 03:51:18
|
what about city table?
Select [S-ID],LEN([S-ID]) from dbo.City_C
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
teamjai
Starting Member
India
42 Posts |
Posted - 01/23/2013 : 04:35:59
|
same result in Country, State and City id's
id NoColumnname
----------------- --------------------
AF2A025C13CB4C4994CC4A3461234146 32
AF2A025C13CB4C4994CC4A3461234146 32 |
|
|
| |
Topic |
|
Stored procedure for Inserting Data into 3 tables.
