insert into multiple tables... help

• Peter de W

  SSC Enthusiast

  Points: 146

  More actions

  September 12, 2007 at 12:14 am

  #176579

  Now how do I get this query to loop through all the records in the Commercial table? The following only does the first property

  declare @pic1 as varchar(255)

  declare @pic2 as varchar(255)

  declare @pic3 as varchar(255)

  declare @pic4 as varchar(255)

  declare @newID as integer

  declare @PropertyNumber as varchar(255)

  declare @PropertyAddress as varchar(255)

  select [ID], @PropertyNumber=PrprtyNo, @PropertyAddress =PrprtyAddr , @pic1=isNull(ImageLink1, 'none.gif'), @Pic2=isNull(ImageLink2, 'none.gif'), @Pic3=isNull(ImageLink3, 'none.gif'), @pic4=isNull(ImageLink4, 'none.gif') from Commercial

  insert into Property (PropertyNumber, PropertyAddress) values (@PropertyNumber, @PropertyAddress)

  SET @NewID = SCOPE_IDENTITY()

  if @Pic1 <> 'none.gif'

  insert into PropertyImages (PropertyID, dbPhoto) values (@NewID, @Pic1)

  if @Pic2 <> 'none.gif'

  insert into PropertyImages (PropertyID, dbPhoto) values (@NewID, @Pic2)

  if @Pic3 <> 'none.gif'

  insert into PropertyImages (PropertyID, dbPhoto) values (@NewID, @Pic3)

  if @Pic4 <> 'none.gif'

  insert into PropertyImages (PropertyID, dbPhoto) values (@NewID, @Pic4)

  thanks

  ---

  Regards,

  PdW
  

• SwePeso

  SSC-Dedicated

  Points: 39758

  More actions

  September 12, 2007 at 1:11 am

  #733019

  -- Initialize staging

  BEGIN TRAN Yak

   

  DECLARE     @LastID INT

   

  SELECT      @LastID = MAX({Property table identity column name here})

  FROM        Property

   

  -- Insert "master" records

  INSERT      Property

              (

                           PropertyNumber,

                           PropertyAddress

              )

  SELECT      PrprtyNo,

              PrprtyAddr

  FROM        Commercial

   

  -- Insert "child" records

  INSERT        PropertyImages

                (

                            PropertyID,

                            dbPhoto

                )

  SELECT        id.CurrID,

                photo.Link

  FROM          (

                            SELECT      {Property table identity column name here} AS CurrID

                                        PropertyNumber,

                                        PropertyAddress

                            FROM        Commercial

                            WHERE       {Property table identity column name here} > @LastID

                ) AS id

  INNER JOIN    (

                            SELECT      PrprtyNo,

                                        PrprtyAddr,

                                        ImageLink1 AS Link

                            FROM        Commercial

   

                            UNION ALL

   

                            SELECT      PrprtyNo,

                                        PrprtyAddr,

                                        ImageLink2

                            FROM        Commercial

   

                            UNION ALL

   

                            SELECT      PrprtyNo,

                                        PrprtyAddr,

                                        ImageLink3

                            FROM        Commercial

   

                            UNION ALL

   

                            SELECT      PrprtyNo,

                                        PrprtyAddr,

                                        ImageLink4

                            FROM        Commercial

                ) AS photo ON photo.PrprtyNo = id.PropertyNumber AND photo.PrprtyAddr = id.PropertyAddress

  WHERE         photo.Link IS NOT NULL

   

  -- Do some cleanup

  IF @@ERROR = 0

     COMMIT TRAN Yak

  ELSE

     ROLLBACK TRAN Yak

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Peter de W

  SSC Enthusiast

  Points: 146

  More actions

  September 12, 2007 at 1:14 am

  #733020

  Thanks I will give it a try and get back to you

  ---

  Regards,

  PdW
  

• Peter de W

  SSC Enthusiast

  Points: 146

  More actions

  September 12, 2007 at 1:23 am

  #733024

  I get the following error when trying to Parse it:

  [Microsoft][ODBC SQL Server Driver]Syntax error or access violation

  All I changed was Property Table PK name which is ID

  Any clues do I have to use dbo.Property etc.

  ---

  Regards,

  PdW
  

• Peter de W

  SSC Enthusiast

  Points: 146

  More actions

  September 12, 2007 at 1:33 am

  #733025

  don't worry when i did the copy and paste it added a couple extra select statements

  ---

  Regards,

  PdW
  

• Peter de W

  SSC Enthusiast

  Points: 146

  More actions

  September 12, 2007 at 4:29 pm

  #733302

  I am having a problem with the insertion of the images.

  The PropertyAddress field is a text field while the PrprtyAddr field is an varchar(255) when I do a cast it is seems to matching in the "ON photo.PrprtyNo = id.PropertyNumber AND photo.PrprtyAddr = id.PropertyAddress" as no photos are being added

  Any ideas???

  - Initialize staging

  BEGIN TRAN Yak

  DECLARE @LastID INT

  SELECT @LastID = MAX([ID])

  FROM Property

  -- Insert "master" records

  INSERT Property

  (

  PropertyNumber,

  PropertyAddress

  )

  SELECT PrprtyNo,

  PrprtyAddr

  FROM Commercial

  -- Insert "child" records

  INSERT PropertyImages

  (

  PropertyID,

  dbPhoto

  )

  SELECT id.CurrID,

  photo.Link

  FROM (

  SELECT [ID] AS CurrID

  PropertyNumber,

  PropertyAddress

  FROM Property

  WHERE ID > @LastID

  ) AS id

  INNER JOIN (

  SELECT PrprtyNo,

  PrprtyAddr,

  ImageLink1 AS Link

  FROM Commercial

  UNION ALL

  SELECT PrprtyNo,

  PrprtyAddr,

  ImageLink2

  FROM Commercial

  UNION ALL

  SELECT PrprtyNo,

  PrprtyAddr,

  ImageLink3

  FROM Commercial

  UNION ALL

  SELECT PrprtyNo,

  PrprtyAddr,

  ImageLink4

  FROM Commercial

  ) AS photo ON photo.PrprtyNo = id.PropertyNumber AND photo.PrprtyAddr = id.PropertyAddress

  WHERE photo.Link IS NOT NULL

  -- Do some cleanup

  IF @@ERROR = 0

  COMMIT TRAN Yak

  ELSE

  ROLLBACK TRAN Yak

  ---

  Regards,

  PdW
  

• Peter de W

  SSC Enthusiast

  Points: 146

  More actions

  September 12, 2007 at 8:25 pm

  #733352

  The problem was caused when there were no properties in the property table, when I added a dummy property with dummy propertyimage it worked fine.

  Thanks for your help.

  P

  ---

  Regards,

  PdW