Insert Results of Query Into Table

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]

8 comments
Dipen 20 May 2013 at 8:15 pm

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?

Sirisha 08 Feb 2013 at 6:55 pm

Simple and sweet solution, it helped me.

rizalmovic 27 Jul 2011 at 9:30 am

Thank you for this helpful article..

Jeffrey Cox 01 Dec 2010 at 1:07 pm

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

Derek Dieter 01 Dec 2010 at 9:15 pm

Cool, glad it helped!

Dennis 29 Aug 2010 at 8:39 am

Oh, forgot to say, I’m using Access 2007.

Dennis 29 Aug 2010 at 8:37 am

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

Derek Dieter 30 Aug 2010 at 9:24 pm

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?

Featured Articles

 Site Author

  • Thanks for visiting!