Stored procedure for Inserting Data into 3 tables.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure for Inserting Data into 3 tables.

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 ---> 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

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 MVP
http://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 query

what 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 MVP
http://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,

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

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 MVP
http://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 MVP
http://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
---
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
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 MVP
http://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 see

SELECT id,LEN(id) from dbo.State_C

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-01-23 : 04:35:59

same result in Country, State and City id's

id NoColumnname
----------------- --------------------
AF2A025C13CB4C4994CC4A3461234146 32
AF2A025C13CB4C4994CC4A3461234146 32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources