Common Table Expression select into? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Common Table Expression select into?

Author

Topic

TimB
Starting Member

2 Posts

Posted - 2010-06-15 : 19:54:15

Hey guys. I'm trying to insert data from a common table expression into a table in a separate database (same server). It's throwing some errors and I can't figure out what's going on. Any ideas?

I'm trying to create buckets where each bucket represents a certain number of friends and populate those buckets with the number of users that have that many friends. FriendshipBuckets is simple:

NumberOfFriends INT, NumberOfUsers INT

THANKS!

With ListEntries(NumberOfFriends)
AS
(
select distinct COUNT(UserID) from Friendships group by UserID
)

INSERT INTO Stats.dbo.FriendshipBuckets (NumberOfFriends, NumberOfUsers)
SELECT
NumberOfFriends,
count(*) as NumberOfUsers from
(select UserID, Count(UserID) from Friendships group by UserID having COUNT(userid) = NumberOfFriends)
FROM
ListEntries

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-15 : 20:01:41

What is the error you're getting?

TimB
Starting Member

2 Posts

Posted - 2010-06-15 : 20:14:02

quote:
Originally posted by robvolk

What is the error you're getting?

Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'FROM'.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-15 : 22:47:26

Try this:

With ListEntries(NumberOfFriends)
AS
(
select distinct COUNT(UserID) from Friendships group by UserID
)

INSERT INTO Stats.dbo.FriendshipBuckets (NumberOfFriends, NumberOfUsers)
SELECT
NumberOfFriends,
count(*) as NumberOfUsers from
(select UserID, Count(UserID) from Friendships group by UserID having COUNT(userid) = NumberOfFriends) as A
FROM
ListEntries

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-16 : 02:36:33

[code]
With ListEntries(NumberOfFriends)
AS
(
select distinct COUNT(UserID) from Friendships group by UserID
)

INSERT INTO Stats.dbo.FriendshipBuckets (NumberOfFriends, NumberOfUsers)
SELECT
NumberOfFriends,
(
select
count(*)
from
(select UserID, Count(UserID) from Friendships group by UserID having COUNT(userid) = NumberOfFriends)
as t
)

FROM
ListEntries

[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources