I'm very new at SQL scripts and couldn't figure out how to make a specific for each loop. I need to do something like this:
I have Sites and SiteCrawls tables.
Basically, for every site I need to create a new SiteCrawl and that SiteCrawl's Site_ID column will equal to that site's ID.
How can I do this?
insert SiteCrawl
(
Site_ID
)
select
s.Site_ID
from Site as s
where s.Site_ID not in (select Site_ID from SiteCrawl)
insert into site_crawl (site_id) values (select site_id from site);
So basically: there is no specific for/each in plain SQL, you handle tables and rows of results always as one statement. So you could also say: there is no way an SQL Statement is something else than a for/each. With that knowledge, you can see that the above query will insert one row into site_crawl for every site_id in the site table. You most likely want to use more values than this, but given the information from your question that is all I can do for you :)
Also: you want to read more about what sql is and how its used.
Have fun, SQL is a lot of fun!
In SQL you typically don't want to loop over every record. It's very inefficient.
You could do something like
insert into SiteCrawl (Site_Id)
select Id from Sites
insert into SiteCrawls (SiteID)
select SiteID
from Sites
you can do it by trigger
CREATE TRIGGER tg AFTER INSERT ON `Sites`
FOR EACH ROW
BEGIN
insert into SiteCrawls(Site_ID) values (NEW.id);
END
;
