sql - How do I create data during a SELECT statement while doing an INSERT INTO?

Question

Sorry if the question is confusing, but I wasn't sure how exactly to ask it. Basically I have a giant table with 20 columns and millions of rows. I want to insert into another table a subset of those rows and columns, and then also for each of those rows, create a new time and new GUID in the table. For example, let's say I have the following table:

CREATE TABLE [dbo].[AddressData](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [AddressDataGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
    [City] [varchar](32) NOT NULL,
    [State] [varchar](2) NOT NULL,
    [DateModified] [datetime2](7) NOT NULL,
    [TS] [timestamp] NOT NULL,
 CONSTRAINT [PK_AddressData_ID] PRIMARY KEY CLUSTERED [ID]

Now I also have another table that has City and State, but then it has a bunch of other columns too, like Street, Zip, etc. I want to select every distinct pair of City and State from the bigger table and stick them in the new table, and I also want it to set the DateModified to the current time, and create a new random GUID for each of those distinct pairs. I can't figure out how to do this in sql server. I tried things like the following:

INSERT INTO Community (CommunityGUID, City, [State], DateModified)
VALUES (NEWID(), t.City, t.[State], SYSUTCDATETIME())
SELECT DISTINCT City, [State] FROM FullTable t

However, I can't figure out the correct syntax. I COULD probably take care of this by creating a temp table with all nullable fields, select distinct with those two columns, and then loop through it row by row creating all of these values, and then finally selecting the fully constructed table into the other one. I'm almost tempted to do that, because that's my first instinct as a software developer and not a database developer, but DB developers tend to dislike having stuff like that in DB if there's a way to just do it in one statement and let the DB engine optimize it.

question from:https://stackoverflow.com/questions/66050828/how-do-i-create-data-during-a-select-statement-while-doing-an-insert-into

Answer 1

I think you just want a real query for the INSERT:

INSERT INTO Community (CommunityGUID, City, [State], DateModified)
    SELECT NEWID(), t.City, t.[State], SYSUTCDATETIME()
    FROM (SELECT DISTINCT City, [State]
          FROM FullTable t
         ) t;