Select and INTO - SQL Server Forums

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)

Select and INTO

Author

Topic

vwilsonjr
Starting Member

45 Posts

Posted - 2003-04-11 : 09:25:55

I'm trying to create a table of duplicates I have in my database. This is the code

SELECT Employee_Number
As Tmp GROUP BY [Employee_Number] HAVING Count(*)>1
INTO #a
FROM [Appuser]

If I leave off everything behind Group By it gives me all of the Employee_numbers. I only want the ones duplicated. Then I want to join that table to the employee table to display all of other field in the employee table.

Thank God for Forums.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-11 : 09:37:55

I think this should work (really should look into BOL)

SELECT Employee_Number INTO #a
FROM [Appuser]
GROUP BY [Employee_Number] HAVING Count(*)>1

Brett

8-)

vwilsonjr
Starting Member

45 Posts

Posted - 2003-04-11 : 10:12:28

Thanks that was it. I had the syntax wrong. BOL wasn't to clear on how to use select with into and count(*)

Thank God for Forums.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-11 : 10:26:44

BOL:

Inserting Rows Using SELECT INTO
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list, for example:

SELECT Shippers.*, Link.Address, Link.City,
Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers
JOIN LinkServer.DB.dbo.Shippers AS Link
ON (Shippers.ShipperID = Link.ShipperID)

SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table containing data selected from a linked server.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources