Insert Results of Query Into Table
-
Posted on June 18, 2009 by Derek Dieter
-
4
There are a few ways to insert query results into another table. The most common way is to use the standard insert statement. This would insert query results into a table that already contains data.
[cc lang=”sql”]
INSERT INTO dbo.Users
(
Username
,FirstName
,LastName
,IsSuperUser
,AffiliateId
,Email
,DisplayName
,UpdatePassword
)
SELECT
Username
,FirstName
,LastName
,IsSuperUser
,AffiliateId
,Email
,DisplayName = DisplayName + ‘ SuperUser’
,UpdatePassword
FROM dbo.NewUsers nu
WHERE nu.IsSuperUser = 1
[/cc]
This way is most efficient if you are inserting records into a new table. It will automatically create the table for you and copy the datatypes from the existing table and all the rows:
[cc lang=”sql”]
SELECT *
INTO dbo.EventLogCopy
FROM dbo.EventLog
[/cc]
- Comments (RSS)
- Trackback
- Permalink
I use second solution
It created new table for me
but now original table were updated with new data, so my newly created table is outdated
now i want to update my newly created table
how can i do that?
Simple and sweet solution, it helped me.
Thank you for this helpful article..
Derek —
Thanks for this article. I needed a way to create a duplicate set of records in a table for one of the records management systems we sell and support. In the past, I’d query out the data, then massage it into an INSERT query, run it, then have to go do the same thing again. Now, taking your code and adding some criteria, all I have to do is change an ID (like your WHERE statement) and, waa-laa, I’m done! Much appreciated!
— Jeffrey
Cool, glad it helped!
Oh, forgot to say, I’m using Access 2007.
I’m kind of a newbee at this. I have a table that I enter a techs start time and end time on a service call. I figured out how to use a query and “DataDiff” to calculate the amount of time the tech spent on the job. Now I need that calculated result inserted into the Service Call form after the Start Time and End Time entries.
It should be simple but I can’t seem to get it to work.
Thanks
Ok, let’s see. So you need the DateDiff result to be inserted into the same row. First you would add the column to the table.
ALTER TABLE ServiceCalls ADD CallTimeMins int
UPDATE ServiceCalls
SET CallTimeMins = DATEDIFF(minute,StartTime, EndTime)
This will update the table one time. Use caution however because it will update every row in the entire table.
Does that help?
