Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    37

    Unanswered: Order by with Insert into?

    I'm selecting records from a table and inserting them into another existing table

    Use DSRBQ000
    INSERT INTO dbo.db_table_information
    Select Table_Name, Column_Name + ' ' + Upper(data_type) +
    CASE WHEN data_type IN('binary','char','nchar','nvarchar','varbinary', 'varchar') THEN '('
    + Cast(character_maximum_length AS varchar(10))+')'
    WHEN data_type IN('decimal','numeric') THEN '(' + Cast(numeric_precision as varchar(3)) + ','
    + Cast(numeric_scale as varchar(3))+ ')'
    Else ''
    End +
    CASE WHEN columnproperty(object_id(table_name),column_name,' IsIdentity')= 1 THEN ' IDENTITY' +
    '(' + Cast(ident_seed(table_name) AS varchar(10)) + ',' + Cast(ident_incr(table_name) AS varchar(10)) + ')'
    Else ''
    End +
    CASE WHEN is_nullable = 'YES' THEN ' NULL'
    ELSE ''
    END 'Column_Definition', ordinal_position
    from information_schema.columns
    where table_name IN(select distinct table_name from information_schema.tables where table_type = 'BASE TABLE')
    and table_name NOT IN('dtproperties','dbo.db_table_information')
    order by table_name, ordinal_position

    I'm trying to first order by table_name and then ordinal_position. However, when viewing the table that it data is getting inserted into, I notice that even though it is ordered by table_name, sometimes a row is out of order according to ordinal position.

    Is there a valid reason for this? Are you not allow to select the order from which a recordset gets inserted into a table? If thats the case, how can I update the db_table_information and save it so it is in table_name, ordinal_position order?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Inserting a sorted record set is usally a wast of time as there is most likely an index in place. When you issue a select your result set will be based on the clustered index or first non-clustered index created for the table.

    If you always want db_table_information to be in table_name, ordinal_position order then create an index on those attributes.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2003
    Posts
    37
    figured out why this occured.. i defined ordinal position as char instead of a number.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    DOH! If only these computers would do as we want rather than do as we ask the world would be a better place!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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