Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: INSERTING Multiple Entries based on the RESULTS of another query

    Hi all

    I am trying to use subqueries to do the following task. This is related to my last post. Anyway, say if I have 2 tables

    I want to do a query on the first table to find out all distinct dates. For each of these distinct dates, i want to create an entry in another table.

    so I'll do something like the following to find all the distinct dates from the jobupdate table.

    SELECT DISTINCT Convert(varchar(10), dateSubmitted, 103)
    FROM jobUpdate
    WHERE timesheetID IS NULL

    for each distinct date, i want to create an entry in another table, so something like. But i need to insert an entry for each of the distinct dates

    INSERT INTO Timesheet
    (
    timesheetDate,
    jobID,
    timesheetType
    )
    VALUES
    (
    [for each of the distinct date],
    [jobID is retrieved],
    [timesheetType may need to be determined from another subquery]
    )

    can any one suggest a good way to do this in a single query??
    any help will be much appreciated.

    cheers

    james

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: INSERTING Multiple Entries based on the RESULTS of another query

    Hi James,

    Nice nice name, by the way.

    Since you have probably several JobIDs in your JobUpdate table for the same date, you will have to decide, which JobID you want to use. I give you an example, for the lowest ID, and I assume the TimeSheetType to be constant 1:

    INSERT INTO Timesheet
    (
    timesheetDate,
    jobID,
    timesheetType
    )
    SELECT Convert(varchar(10), dateSubmitted, 103), min(JobID), 1
    FROM jobUpdate
    WHERE timesheetID IS NULL
    GROUP BY Convert(varchar(10), dateSubmitted, 103)
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Posts
    111

    Re: INSERTING Multiple Entries based on the RESULTS of another query

    Originally posted by DoktorBlue
    Hi James,

    Nice nice name, by the way.

    Since you have probably several JobIDs in your JobUpdate table for the same date, you will have to decide, which JobID you want to use. I give you an example, for the lowest ID, and I assume the TimeSheetType to be constant 1:

    INSERT INTO Timesheet
    (
    timesheetDate,
    jobID,
    timesheetType
    )
    SELECT Convert(varchar(10), dateSubmitted, 103), min(JobID), 1
    FROM jobUpdate
    WHERE timesheetID IS NULL
    GROUP BY Convert(varchar(10), dateSubmitted, 103)

    Thanx, i assume your name is james as well, hehhe

    Thanx for the reply, I have definitely learnt something new today. i think that is what i am looking for. i'll have to put more subqueries into the Select subquery to get what i want.

    Cheers

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: INSERTING Multiple Entries based on the RESULTS of another query

    You may look up my real name.

    You may add more SELECT statements with the UNION operator; let me know if you need some support.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •