Inserting a Select Distinct into a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting a Select Distinct into a table

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-09 : 12:04:43

I have the following query:

SELECT DISTINCT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE [sumhours]
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN [sumhours]-40
ELSE 0
END ) AS TotalOT

FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours

and I'd like to insert those values into a table in my database. What's the best way to go about doing that?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-09 : 12:12:37

well just add "Insert into databaseName.SchemaName.TableName (Col1,Col2,Co)" above this code ...

if the table already exists ... on the other hand if the table does not exist then you can also use Select * into databaseName.SchemaName.TableName From ....."

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-09 : 12:25:20

MIK,

This table doesnt exist, so I'm not sure where the From would be here, since this is summing data.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-09 : 12:36:10

if the query works fine then replace the DatabaseName and TableName as highlighted below with the desired one ... after executing the below query, a table will be created in that database with the name you specified

SELECT DISTINCT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE [sumhours]
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN [sumhours]-40
ELSE 0
END ) AS TotalOT
into databaseName.dbo.TableName
FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-09 : 12:53:26

Mik,

thanks, that's what I needed.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-09 : 14:45:47

Mik,

What about something like this?

insert into payroll (Employeenumber, TotalRegHours, TotalOT)
UPDATE Payroll SET payrolldate = CURRENT_TIMESTAMP, payrollran = 'Yes',
approved = 'Yes'

Payroll already exists, but I need to update when I insert data into it with those other three things.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources