Professional Web Applications Themes

LQQLY: how do I put this exactly?? - Microsoft SQL / MS SQL Server

Yes, the query needs the "INTO" which has already been communicated in one of my previous posts. Select top 0 * into [stage] from [base table] is quite valid. If you want to be explicit, sure you can specify the column list. However, if the schema is changed, for example, a non-null column without default is added to the table, you would still have to change your import code to accommodate this. Your suggestion to create a #tmp would not fly for a 'bulk insert' process. You have to use a permanent table or updatable view. Preferably, I would create ...

  1. #1

    Default Re: LQQLY: how do I put this exactly??

    Yes, the query needs the "INTO" which has already been communicated in one
    of my previous posts.
    Select top 0 *
    into [stage]
    from [base table]

    is quite valid. If you want to be explicit, sure you can specify the column
    list. However, if the schema is changed, for example, a non-null column
    without default is added to the table, you would still have to change your
    import code to accommodate this.

    Your suggestion to create a #tmp would not fly for a 'bulk insert' process.
    You have to use a permanent table or updatable view.

    Preferably, I would create ONE instance of the staging table and reuse it as
    desired. However, in Trint's case, it would be *easier/faster* to show him
    how to get it started in the shortest amount of time.

    Anyway, Trint, please generate a DDL and post it here. That way we can see
    what your primary key is. We'll see about getting all this wrapped up for
    you.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    This 
    >
    >[/ref]


    oj Guest

  2. #2

    Default Re: LQQLY: how do I put this exactly??

    Hi oj,


    "oj" <com> wrote in message
    news:phx.gbl... 
    I just didn't see that post, I'm sorry.
     
    column 
    process. 
    You are right, I mixed things up with SELECT INTO.
     
    as 
    Actually, now I think of it, there is a case for dropping and recreating
    staging tables. You prefer not to use DELETE to clean out staging tables in
    most cirstances. That leaves you with either TRUNCATE TABLE or
    CREATE/DROP TABLE. If you want to run your bulk insert process with minimal
    permissions, CREATE/DROPTABLE is the best option because you only need to
    grant the login that the bulk insert process uses CREATE TABLE for that,
    whereas for TRUNCATE TABLE it has to be a member of sysadmin, db_owner or
    db_ddladmin. Somewhat academic I admit, but as the bulkadmin role has
    permisisons on the filesystem it has some importance in high security
    environments.
     
    > This [/ref]
    to: [/ref][/ref]
    always 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

  3. #3

    Default Re: LQQLY: how do I put this exactly??

    <G>

    To invoke server "bulk insert" process you would need the bulkadmin role at
    the minimum. So, whether or not you want to do truncate/delete is not that
    big of an issue. Simply granting 'create table/db_ddladmin' would not be
    enough to do bulk insert.

    At any rate, I thank you/all for jumping in to help Trint out - the poor
    thing has been at this bulk insert for a while now. <G>

    It would make it easier if he posts the DDL so we can see what he's dealing
    with. A little bit here and there sure makes it harder to give a complete
    solution.

    Cheers,

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... [/ref]
    one 
    > I just didn't see that post, I'm sorry.

    > column [/ref]
    your 
    > process. 
    > You are right, I mixed things up with SELECT INTO.
    > [/ref]
    it [/ref]
    him 
    > Actually, now I think of it, there is a case for dropping and recreating
    > staging tables. You prefer not to use DELETE to clean out staging tables[/ref]
    in 
    minimal [/ref]
    see [/ref]
    for [/ref][/ref]

    > > This [/ref]
    > to: [/ref]
    > always 
    > >
    > >[/ref]
    >
    >[/ref]


    oj Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139