SQLTeam.com | Weblogs | Forums

SQL Question


#1

Hay,
just wanted to ask.. Is something like that possible?

INSERT INTO fahrzeuge
(Kennz,FahrgNr,ZulDatum,InspTermin,Km,Treibstoff)
VALUES ('SI-0 815','2208AKA','2018-12-01','2018-12-01','30000',(
SELECT fahrzeuge.Treibstoff
FROM fahrzeuge
WHERE fahrzeuge.Kennz LIKE 'SI-KR 123'));


#2

hi

not sure what you are trying to do here
please explain what you are trying to do
:slight_smile::
:slight_smile:

but its not the proper syntax

INSERT INTO fahrzeuge
(Kennz,FahrgNr,ZulDatum,InspTermin,Km,Treibstoff)
VALUES ('SI-0 815','2208AKA','2018-12-01','2018-12-01','30000',(
SELECT fahrzeuge.Treibstoff
FROM fahrzeuge
WHERE fahrzeuge.Kennz LIKE 'SI-KR 123'));

normally its like this
the values can be there only
OR
select can be there only

INSERT INTO fahrzeuge
(Kennz,FahrgNr,ZulDatum,InspTermin,Km,Treibstoff)
SELECT fahrzeuge.Treibstoff
FROM fahrzeuge
WHERE fahrzeuge.Kennz LIKE 'SI-KR 123'));

INSERT INTO fahrzeuge
(Kennz,FahrgNr,ZulDatum,InspTermin,Km,Treibstoff)
VALUES ('SI-0 815','2208AKA','2018-12-01','2018-12-01','30000'


#3

Our task is to use the value of "Treibstoff" of an other car to define the car we are inserting
in one statement.
So it must be possible somehow


#4

You can do this:

 Insert Into fahrzeuge (
        Kennz
      , FahrgNr
      , ZulDatum
      , InspTermin
      , Km
      , Treibstoff
        )
 Select Kennz = 'SI-0 815'
      , FahrgNr = '2208AKA'
      , ZulDatum = '2018-12-01'
      , InspTermin = '2018-12-01'
      , Km = '30000'
      , f.Treibstoff
   From fahrzeuge                       f
  Where f.Kennz = 'SI-KR 123';

Since you are looking for a single non-wildcard value then use equals instead of like. If there is no source value with a Kennz value = 'SI-KR 123' then this will not insert a new row. If you want to insert the new row regardless of whether or not there is a previous value then you need to change it to:

Declare @treibstoff nvarchar(xxx) = 'default value';  -- declare the variable with the same size and type as the actual column

    Set @treibstoff = coalesce((Select f.Treibstoff
                                  From fahrzeuge f
                                 Where f.Kennz = 'SI-KR 123'), @treibstoff);

 Insert Into fahrzeuge (
        Kennz
      , FahrgNr
      , ZulDatum
      , InspTermin
      , Km
      , Treibstoff
        )
 Values (
        'SI-0 815'
      , '2208AKA'
      , '2018-12-01'
      , '2018-12-01'
      , '30000'
      , @treibstoff
        );

You could also set defaults in the table and not include those columns in the insert - but I don't think you want these values to be defined as the default values - especially not the date values.