SQLTeam.com | Weblogs | Forums

Insert data into second table that has the same relation with first table

I have two tables Bike and Bikemodel . I need to do an insert to populate bikemodel with the corresponding manufacturer that Bike contains
ddl for tables :
CREATE TABLE [dbo].[bike](
bikeid [int] IDENTITY(1,1) NOT NULL,
bikemfg [varchar] (15) null,
CREATE TABLE [dbo].[bikemodel](
bmid [int] IDENTITY(1,1) NOT NULL,
bikeid [int] null,
bikemodelname [varchar] (25) null,
bikemfg [varchar] (15) null

trying to do an insert into the bikemodel:

insert into bikemodel (bikemfg)
select bikemfg from bike

...but this does nothing, and joining the bikeid on both tables render 'multipart identifier cannot be bound error '

?

No, I'm trying to insert data into the second table BikeModel in the bikemfg field, and these values need to correspond with the bikemodels that are derived from the bike manufacturer bikemfg
HTH
Z

hi

i am trying this !!!

its confusing .. please provide sample data like i have !! thanks :slight_smile:

please click arrow to the left for "drop create data" script
DROP TABLE [dbo].[bike] 

go 

CREATE TABLE [dbo].[bike] 
  ( 
     bikeid  [INT] IDENTITY(1, 1) NOT NULL, 
     bikemfg [VARCHAR] (15) NULL 
  ) 

go 

DROP TABLE [dbo].[bikemodel] 

go 

CREATE TABLE [dbo].[bikemodel] 
  ( 
     bmid          [INT] IDENTITY(1, 1) NOT NULL, 
     bikeid        [INT] NULL, 
     bikemodelname [VARCHAR] (25) NULL, 
     bikemfg       [VARCHAR] (15) NULL 
  ) 

go 

INSERT INTO [dbo].[bike] 
SELECT 'BMW' 

INSERT INTO [dbo].[bike] 
SELECT 'KAWASAKI' 

INSERT INTO [dbo].[bike] 
SELECT 'HONDA' 

go 

INSERT INTO [dbo].[bikemodel] 
SELECT 1, 
       'A5', 
       NULL 

INSERT INTO [dbo].[bikemodel] 
SELECT 2, 
       'Hero', 
       NULL 

INSERT INTO [dbo].[bikemodel] 
SELECT 3, 
       'Zapper', 
       NULL 

go 

SELECT * 
FROM   [dbo].bike 

go 

SELECT * 
FROM   [dbo].bikemodel 

go

image