logo
down
shadow

SQL procedure for inserting records in a table based on records in another table


SQL procedure for inserting records in a table based on records in another table

By : Pankaj Gupta
Date : October 16 2020, 08:10 PM
To fix the issue you can do Use this query inside your stored procedure
Note:keeping calender id as autoincrement
code :
insert into CalendarTable(LawId, StartDate, Frequency, FormId)
select ParentLawId,StartDate,Frequency,FormId
from FormTable  where formid not in(select formid from CalendarTable);


Share : facebook icon twitter icon
One SQL statement for counting the records in the master table based on matching records in the detail table?

One SQL statement for counting the records in the master table based on matching records in the detail table?


By : Zhenkun Cai
Date : March 29 2020, 07:55 AM
it should still fix some issue This answer assumes the following
The Name field is always FirstName LastName You are looking once and only once for the John firstname. The search criteria would be different, pending what you need
code :
SELECT Date, Count(*)
FROM tblmaster 
INNER JOIN tbldetails ON tblmaster.ID=tbldetails.masterId 
WHERE NAME LIKE 'John%'
GROUP BY Date, tbldetails.masterId
DECLARE @tblmaster as table(
ID  int,
[date] datetime
)

DECLARE @tbldetails as table(
masterID  int,
name varchar(50)
)

INSERT INTO @tblmaster (ID,[date])
VALUES
(1,'2014-09-07'),(2,'2014-09-07'),(3,'2014-09-08')

INSERT INTO @tbldetails(masterID, name) VALUES
(1,'John Walsh'),
(1,'John Jones'),
(2,'John Carney'),
(1,'Peter Lewis'),
(3,'John Wilson')
SELECT date,count(t1.ID) FROM @tblmaster mainTable INNER JOIN 
( 
    SELECT ID, COUNT(*)  as countOfAll
    FROM @tblmaster  t1
    INNER JOIN @tbldetails t2 ON t1.ID=t2.masterId 
    WHERE NAME LIKE 'John%' 
    GROUP BY id) 
as t1 on t1.ID = mainTable.id 
GROUP BY mainTable.date
Inserting records into 1 table from 3 other tables using a 5th table to determine which records to pull

Inserting records into 1 table from 3 other tables using a 5th table to determine which records to pull


By : Nimo
Date : March 29 2020, 07:55 AM
To fix the issue you can do Im using SQL-server and need to insert records into a table called "dummy" from 3 other tables based on their job number. which job numbers are being pulled is determined by the value ranges in a 5th table called "Mmay". There is not a unique key in any of the tables, and the job numbers repeat. the data from Mmay can be seen below, with jnost being the start value of the range and jnofn being the finishing value of the range. , Please try this code.
code :
INSERT INTO dummy  (code, invno, ven, invdate, amon, accno, amnt, jno, saccno, ckno)
SELECT
    RES.*
FROM
    (
        SELECT code, invno, ven, invdate, amon, accno, amnt, jno, saccno, ckno 
        FROM accnt 
        UNION ALL
        SELECT code, invno, ven, invdate, amon, accno, amnt, jno, saccno, ckno 
        FROM jobcost 
        UNION ALL
        SELECT code, invno, ven, invdate, amon, accno, amnt, jno, saccno, ckno 
        FROM servic15 
    ) RES
INNER JOIN Mmay M ON RES.jno BETWEEN M.jnost AND M.jnofn
Create a Stored Procedure to Delete Records from employee(first table) and orders(second table) based on input params (i

Create a Stored Procedure to Delete Records from employee(first table) and orders(second table) based on input params (i


By : Milad Zandi
Date : March 29 2020, 07:55 AM
may help you . In your example, it sounds like you want to delete all employee (and related order) records for a given company_id. Is that correct? If so, you can use two simple DELETEs:
code :
-- Delete all orders related to employees for given "company_id"
DELETE FROM orders 
WHERE employee_num IN (
  SELECT employee_num 
  FROM employees 
  WHERE company_id = p_company_id
);

-- Delete employees for given "company_id"
DELETE FROM employees
WHERE company_id = p_company_id;
-- Delete all orders related to employees for given "company_id"
DELETE FROM orders 
WHERE employee_num IN (
  SELECT employee_num 
  FROM employees 
  WHERE company_id = p_company_id
  AND employee_num <> ALL(arrayOfNo) -- Get employees NOT IN arrayOfNo list
);

-- Delete employees for given "company_id"
DELETE FROM employees
WHERE company_id = p_company_id
AND employee_num <> ALL(arrayOfNo) -- Get employees NOT IN arrayOfNo list
;
Inserting records in a table based on a temporary table

Inserting records in a table based on a temporary table


By : lucioperca
Date : March 29 2020, 07:55 AM
wish of those help You could use an INSERT ... SELECT ..., selecting all the serial number not already present using NOT EXISTS.
code :
INSERT INTO cpar
            (serialnumber,
             parametercategory,
             parametername,
             priority)
            SELECT _tmp_mj.serialnumber,
                   'Constituent Code',
                   'Major Donor',
                   'Normal'
                   FROM _tmp_mj
                   WHERE NOT EXISTS (SELECT *
                                            FROM cpar
                                            WHERE cpar.serialnumber = _tmp_mj.serialnumber);
Unable to retrieve records from azure storage table when manually inserting records

Unable to retrieve records from azure storage table when manually inserting records


By : user3023083
Date : March 29 2020, 07:55 AM
Does that help Ok found the solution just trying random stuff! I hope this will come handy for folks who are facing the same issue. So turns out the propertyName must follow camel case but with first character capitalized. So :
Related Posts Related Posts :
  • How to setup local server for wordpress site with git
  • Multiplying CASE row with different values
  • Index on a table not being used all the time
  • How can I get a date from mysql database if it is not null?
  • How to execute a TRIGGER in MSSQl?
  • MySQL 8 Window Functions + Full-text searching
  • Join on large table getting slower
  • Select record from two different table
  • Getting 1064 error while creating mysql trigger
  • MySQL Database Operations
  • How can I make this SQL sort by most relevent?
  • Database query on month
  • Select number of matching rows of a particular column in MySQL
  • How to use rake database commands with password
  • If we change a primary key value, why don't we have to change a dependent column value?
  • MySQL - selecting all records except the ones already associated in the relational table
  • Delete Duplicate MySQL rows but keep one
  • ORDER BY does not perform any function
  • Update column with output of select within the same table in mysql
  • Set a variable inside case statement in mysql
  • MYSQL: How to get rows inserted in the last X hours without querying the entire table
  • AWS RDS MySQL Cross-region replication
  • Cannot truncate a table referenced in a foreign key constraint from empty table
  • How does SQL determine a character's length in a varchar?
  • MySQL : Getting DB row with exact same data from a vector
  • Mysql update query with join
  • Group values that have the same name in one column and same id in other column
  • Mysql query syntax for conditional inserts
  • Is it faster to run an SQL count(*) query in a loop, or try to merge it into the parent query?
  • MySQL query to fetch product variants
  • Report Reindex taking too long after destroy
  • MySQL - Adding varchar as a foreign key
  • How to find duplicated entries which has different slug?
  • SQL Query comparing values in different rows
  • Eloquent giving error but query executes fine in phpmyadmin
  • JPQL fetch data from multiple tables
  • MySql Query - Expanding data into new table
  • Official MySQL Docker container not caching queries?
  • MySQL, Count values in every fields in a table
  • SQL : number different dates within users
  • elastic beanstalk docker app cannot connect to mysql
  • Why am I getting errno: 150 "Foreign key constraint is incorrectly formed"?
  • Update columns which included in payload
  • MySQL subquery in select
  • Get difference in top 2 salary of employee from each department
  • connecting to database using RMySQL and .my.cnf file in R
  • How to get data by mysql
  • SQL Error [1054] [42S22]: Unknown column ' ' in 'field list'
  • turn c# code into mysql function
  • How to loop through and output nested array in Laravel
  • MySQL TRIM spaces inside text
  • Mysql query with multiple selects results in high CPU load
  • Backup DB Django MysqlDump
  • How to select all rows from group, until occurrence of a value
  • Using substring to filter a specific word from a string in MySQL
  • Mysql - Alias in Left Outer Join giving error
  • How use custom alias field from select fields in join?
  • How to sum durations in units of Year, Month and Day in MySQL?
  • Is it possible to assign the values from select exist query in MySQL to multiple variables in a stored procedure?
  • How does mysql resolves conflict when same option is configured twice?
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org