CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005t-sql
user1074474
user1074474

Reputation: 461

the insert statement in child table

I am trying to insert in child table , taking the [ACCNO] id and its count value from parent table ,and then inserting [ACCNO] in the FK of child table and count against each [ACCNO] , but some error still there 

INSERT INTO [test1].[dbo].[star_schema]
           (
           [ACCNO],
           [book_frequency])
     VALUES
           (SELECT  [books_dimension] .ACCNO , count(books_dimension .[ACCNO]) as book_frequency
            FROM         [books_dimension]  
                         group by [ACCNO] having (COUNT(*)>1) order by book_frequency  desc)
GO

Its giving error near SELECT and in very last bracket. I also like to mention that in table [star_schema] , the id is star_int which is identity –

Upvotes: 0

Views: 131

Answers (1)

Oleg Dok
Oleg Dok

Reputation: 21766

You need no VALUES and ORDER BY keyword when inserting from select, do like this:

INSERT INTO [test1].[dbo].[star_schema]([ACCNO], [book_frequency])
SELECT  
  [books_dimension].ACCNO, 
  count(books_dimension.[ACCNO]) as book_frequency
FROM [books_dimension]  
group by [ACCNO] 
having COUNT(*)>1

Upvotes: 2

Related Questions