Results 1 to 7 of 7

Thread: Insert..Select statement problem

  1. #1
    Join Date
    Sep 2002
    Posts
    53

    Insert..Select statement problem

    Hi,

    I am trying to run the following insert statement, but am gettng an error. The table I want to insert to
    (DimensionMonthTime_hold) has an
    Identity column defined for its key.

    The part I cannot figure out is that when I run this insert statement without the order by statement, the insert is successful. If I attempt to run this insert statement with the Order By statement, I get an error saying that I have to provide a value for the identity value in the insert statement (which i don't want to do.) I need to have the data sorted, hence the reason for the order by.

    I've tried to specify the column names on the insert line, but haven't figure that out.

    Any suggestions?

    Thanks

    Jim

    ----------------------------

    Insert into DimensionMonthTime_hold
    select distinct substring(period,1,4) + substring(period,6,2),
    substring(period,1,4) ,
    case substring(period,6,2)
    when '01' then '1'
    when '02' then '1'
    when '03' then '1'
    when '04' then '2'
    when '05' then '2'
    when '06' then '2'
    when '07' then '3'
    when '08' then '3'
    when '09' then '3'
    when '10' then '4'
    when '11' then '4'
    when '12' then '4'
    else 1
    end,
    substring(period,6,2),
    case substring(period,6,2)
    when '01' then 'First Quarter'
    when '02' then 'First Quarter'
    when '03' then 'First Quarter'
    when '04' then 'Second Quarter '
    when '05' then 'Second Quarter'
    when '06' then 'Second Quarter'
    when '07' then 'Third Quarter '
    when '08' then 'Third Quarter '
    when '09' then 'Third Quarter '
    when '10' then 'Fourth Quarter'
    when '11' then 'Fourth Quarter'
    when '12' then 'Fourth Quarter'
    end,
    case substring(period,6,2)
    when '01' then 'January'
    when '02' then 'February'
    when '03' then 'March'
    when '04' then 'April '
    when '05' then 'May'
    when '06' then 'June'
    when '07' then 'July '
    when '08' then 'August '
    when '09' then 'September '
    when '10' then 'October'
    when '11' then 'November'
    when '12' then 'December'
    end,
    1,
    getdate()
    from transaction
    where Account_type ='A'

    ------------------------

  2. #2
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    In an Insert Into ... Select ... you cannot use an Order By statement for the Select. It does not make any sense anyway as it does not have any effect on the order SQl Server stores the records in your result table.

    BTW: It is good practice to specify the column list in the Insert statement. This will avoid such kind of error message.

  3. #3
    Join Date
    Feb 2003
    Posts
    1,048
    Actually, you can use Order by in an Insert ... Select statement. If the table you are inserting into doesn't have any pre-defined sorting rules stating otherwise, then the data will be in the order you specified.

    This is especially true if the table has a clustered identity filed that is being autopopulated by SQL Server when you insert the data.

  4. #4
    Join Date
    Sep 2002
    Posts
    53
    Thank you for your responses,

    I did figure out how do what I wanted to do.

    The order by statement has be the same as the select distinct statement
    i.e.


    -
    order by substring(period,1,4) + substring(period,6,2),


    Jim

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    Actually, you can use aliases in Order By statements. This will make your query more efficient as it won't have to do everything twice.

    Select substring(period,1,4) + substring(period,6,2) As Field1, ....
    .....
    order by Field1, ....

  6. #6
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    Shame on me for giving wrong information. :-(

    However, I do not like the idea of sorting the data on insert because the physical order of records depends on the clustered index of he table and the logical order depeds on any "order by" you apply when selecting data out of the table.

  7. #7
    Join Date
    Feb 2003
    Posts
    1,048
    I agree, for the most part. The one exception that comes to mind is when I'm inserting data into a new table. If it can be done efficiently, I'll insert the data in the same order as the index will be in and then add the index after the insert.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •