SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored procedure for Inserting Data into 3 tables.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

teamjai
Starting Member

India
44 Posts

Posted - 01/21/2013 :  00:10:59  Show Profile  Reply with Quote
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
52325 Posts

Posted - 01/21/2013 :  00:40:11  Show Profile  Reply with Quote
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/

Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/21/2013 :  01:08:04  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/21/2013 :  01:20:15  Show Profile  Reply with Quote
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/

Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/21/2013 :  01:52:22  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/21/2013 :  02:00:27  Show Profile  Reply with Quote
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/

Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/21/2013 :  02:39:11  Show Profile  Reply with Quote
Ok, that's not a problem (city repeated is ok), bcoz some state having same city name..

Pls tell me correct query...

Many thanks..

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/21/2013 :  03:31:50  Show Profile  Reply with Quote
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/

Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/21/2013 :  04:17:18  Show Profile  Reply with Quote

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
Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/21/2013 :  06:35:23  Show Profile  Reply with Quote
Hi any solution ?
Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/21/2013 :  22:44:36  Show Profile  Reply with Quote
i am waiting for Your solution...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/22/2013 :  10:01:05  Show Profile  Reply with Quote
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/

Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/23/2013 :  01:29:12  Show Profile  Reply with Quote

State table
-------------------
Id -> Varchar(36)

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/23/2013 :  01:32:11  Show Profile  Reply with Quote
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/

Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/23/2013 :  03:21:34  Show Profile  Reply with Quote
the result is,

id No Column name
----------------- --------------------
9F968DA0448E4F7FA87C4AED6D87CD54 32
B5F341B342D04CEEBD19B01FD9D2EBF7 32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/23/2013 :  03:51:18  Show Profile  Reply with Quote
what about city table?

Select [S-ID],LEN([S-ID]) from dbo.City_C

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 01/23/2013 :  04:35:59  Show Profile  Reply with Quote

same result in Country, State and City id's

id NoColumnname
----------------- --------------------
AF2A025C13CB4C4994CC4A3461234146 32
AF2A025C13CB4C4994CC4A3461234146 32
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000