Problem to populate a table with data from other tables

• varkstad

  SSC-Addicted

  Points: 425

  More actions

  May 28, 2010 at 5:30 am

  #222067

  I have created a database with these tables:

  CREATE TABLE kurs.personal

  (personal_id INT IDENTITY(1,1),

  chef_idINT NULL,

  lonekat_idINT NOT NULL,

  fornamn CHAR(20) NOT NULL,

  efternamn CHAR(20) NOT NULL,

  titel CHAR(10) NOT NULL)

  GO

  CREATE TABLE kurs.lonekat

  (lonekat_id INT IDENTITY(1,1),

  beskrivning CHAR(20) NOT NULL,

  manadslon numeric(10,2) NOT NULL,

  provisionsandel decimal(3,2) NOT NULL)

  GO

  CREATE TABLE kurs.utbetalning

  (utbetalning_id INT IDENTITY(1,1),

  personal_id INT NOT NULL,

  grundlonNUMERIC(10,2)NOT NULL,

  bonus NUMERIC(10,2)NOT NULL,

  totallon NUMERIC(10,2)NOT NULL,

  loneperiod CHAR(6) NOT NULL)

  GO

  CREATE TABLE kurs.kund

  (kund_idINT IDENTITY(1,1),

  kundansvarig_idINT NOT NULL,

  fornamnCHAR(20) NOT NULL,

  efternamn CHAR(20) NOT NULL,

  telefonnr CHAR(15) NOT NULL,

  mobiltelefon CHAR(15) NOT NULL,

  ring_ej_flagga BIT NOT NULL)

  GO

  CREATE TABLE kurs.bokning

  (bokning_idINT IDENTITY(1,1),

  produkt_idINT NOT NULL,

  saljare_idINT NOT NULL,

  kund_id INT NOT NULL,

  tidpunktDATETIME NOT NULL)

  GO

  CREATE TABLE kurs.produkt

  (produkt_idINTIDENTITY(1,1),

  namnCHAR(20) NOT NULL,

  provisionNUMERIC(10,2)NOT NULL)

  GO

  ALTER TABLE kurs.personal

  ADD CONSTRAINT pk_personal PRIMARY KEY CLUSTERED (personal_id)

  GO

  ALTER TABLE kurs.lonekat

  ADD CONSTRAINT pk_lonekat PRIMARY KEY CLUSTERED (lonekat_id)

  GO

  ALTER TABLE kurs.utbetalning

  ADD CONSTRAINT pk_utbetalning PRIMARY KEY CLUSTERED (utbetalning_id)

  GO

  ALTER TABLE kurs.bokning

  ADD CONSTRAINT pk_bokning PRIMARY KEY CLUSTERED(bokning_id)

  GO

  ALTER TABLE kurs.kund

  ADD CONSTRAINT pk_kund PRIMARY KEY CLUSTERED (kund_id)

  GO

  ALTER TABLE kurs.produkt

  ADD CONSTRAINT pk_produkt PRIMARY KEY CLUSTERED(produkt_id)

  GO

  ALTER TABLE kurs.personal

  ADD CONSTRAINT fk_personaltopersonal FOREIGN KEY (chef_id)

  REFERENCES kurs.personal (personal_id)

  GO

  ALTER TABLE kurs.personal

  ADD CONSTRAINT fk_personaltolonekat FOREIGN KEY (lonekat_id)

  REFERENCES kurs.lonekat (lonekat_id)

  GO

  ALTER TABLE kurs.utbetalning

  ADD CONSTRAINT fk_utbetalningtopersonal FOREIGN KEY (personal_id)

  REFERENCES kurs.personal (personal_id)

  GO

  ALTER TABLE kurs.kund

  ADD CONSTRAINT fk_kundtopersonal FOREIGN KEY(kundansvarig_id)

  REFERENCES kurs.personal (personal_id)

  GO

  ALTER TABLE kurs.bokning

  ADD CONSTRAINT fk_bokningtoprodukt FOREIGN KEY(produkt_id)

  REFERENCES kurs.produkt (produkt_id)

  GO

  ALTER TABLE kurs.bokning

  ADD CONSTRAINT fk_bokningtopersonal FOREIGN KEY(saljare_id)

  REFERENCES kurs.personal (personal_id)

  GO

  ALTER TABLE kurs.bokning

  ADD CONSTRAINT fk_bokningtokund FOREIGN KEY(kund_id)

  REFERENCES kurs.kund (kund_id)

  GO

  All tables without utbetalning are populated with data from csv-files. My problem is two populate the table utbetalning with data from the other tables where:

  kurs.utbetalning.grundlon = kurs.lonekat.manadslon

  kurs.utbetalning.bonus = kurs.produkt.provision * kurs.lonekat.provisionsandel

  kurs.utbetalning totallon = kursutbetalning grundlon + kurs.utbetalning.bonus

  I have made lots of trials during lots of days but still I really don´t know how to get it work. Some code examples I have tried:

  USE kursdatabas

  GO

  INSERT INTO kurs.utbetalning (personal_id)

  SELECT personal_id

  FROM kurs.personal

  GO

  INSERT INTO kurs.utbetalning (grundlon)

  SELECT manadslon

  FROM kurs.lonekat

  SET IDENTITY_INSERT kurs.utbetalning on

  GO

  INSERT INTO kurs.utbetalning (totallon)

  SELECT grundlon + bonus

  FROM kurs.utbetalning

  SET IDENTITY_INSERT personal_id on

  GO

  INSERT INTO kurs.utbetalning (bonus)

  SELECT provision FROM kurs.produkt

  SELECT provisionsandel FROM kurs.lonekat

  SET bonus = provisionsandel * provision

  JOIN on = personal_id

  GO

  SELECT *

  FROM kurs.utbetalning

  GO

  Can anyone help me to get it work?

  Nilla

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  May 28, 2010 at 2:37 pm

  #1174136

  varkstad (5/28/2010)

  kurs.utbetalning.grundlon = kurs.lonekat.manadslon

  kurs.utbetalning.bonus = kurs.produkt.provision * kurs.lonekat.provisionsandel

  kurs.utbetalning totallon = kursutbetalning grundlon + kurs.utbetalning.bonus

  :pinch: Problem here is either lack of business specifications or faulty database design.

  I cannot see how tables utbetalning, lonekat and produkt are related - no apparent navigation is defined - so I do not understand how table is supposed to get populated.

  Could you please clarify?

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• varkstad

  SSC-Addicted

  Points: 425

  More actions

  May 28, 2010 at 9:45 pm

  #1174207

  I attach a database_diagram and hope it will answer your question.