A common pattern I still haven't solved is this: I have table A , and now I want to add table B and give it one row for each row in table A . Then I want to add a foreign key column to table A pointing at the row I just added. Of course this is easy to do with a loop, but can you do it all in pure SQL? If you have a solution I would love to see it. Here is my history of attempts: DROP TABLE IF EXISTS dogs; DROP TABLE…
This script works in SQL Server - I would think you could do the same in Postgres, but I'm not as familiar as I'd like to be with Postgres. Note that it does require 2 separate steps rather than 1 as you appear to desire, so may not work for you: IF OBJECT_ID('dbo.dogs', 'U') IS NOT NULL DROP TABLE dbo.dogs GO IF OBJECT_ID('dbo.doghouses', 'U') IS NOT NULL DROP TABLE dbo.doghouses GO CREATE TABLE dogs ( id INTEGER ID…
Re: Modern SQL: With – Organize Complex Queries
#61Yup, the OUTPUT keyword is the key to making this work. Before this was added to TSQL, the only way I knew of to make this work was by using a cursor and handling each record independently.