help with inserting data - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

help with inserting data

Author

Topic

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-09-30 : 10:32:32

CREATE TABLE [dbo].[Addresses] (
[AddressID] [int] IDENTITY (1, 1) NOT NULL ,
[Address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PIN] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

CREATE TABLE [dbo].[Carriers] (
[CarrierID] [smallint] NOT NULL ,
[Name] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressID] [int] NOT NULL
)

Hey guys,
i have two tables as you can see above (Carriers and Addresses). Each 'Carrier' has an address ID which is related to 'Addresses'. How do i insert data in such a way that the addresses go into the 'Address' table and the carrier details go into the 'Carrier' table. The problem i'm facing is this: I could use two seperate SQL statements - one to insert data in each table but how would i get the approiate AddressID of thje contact into the 'Carrier' table.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-30 : 10:36:56

You can use @@IDENTITY variable to get last inserted identity value..So basically steps will be:

1. Add details to Addresses table
2. Make use of @@IDENTITY variable while inserting AddressID in Carriers table

Alternately, you can use SCOPE_IDENTITY() function also.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources