Post reply

how to get the selected data created on local instance to Production server when I have no create table permissions

  • January 26, 2011 at 1:10 pm

    #245872

    Hi,

    I imported an excel file to my local instance as an sql table to use in a join. Now I need to run the select join query on the cluster(production) server cuz I need to do a Insert into tblSubmissions from Select col1,col2, etc from #SelectedDataFromExcelTableJoin. I need the join query which the table(imported from excel) uses to be on the Production Server. No one can create a table even in our dev group's DBs on the prod server without a change request to the SQL DBA team which would be denied for this 1 time ad-hoc query wonder. Does someone know a way to do this? I just need the extracted data from my local to be use to insert into a table on prod. As it stands, I will need to use access linked tables as a broker, but not interested in using JumboJetSQL!

    I almost want to think I can copy the resultData from my local grid result and do something with it in terms of using it on prod. ,but nothing comes to me on how I would do that if at all possible.

    Again its a permissions problem but looking for a code solution cuz we cant get create table permission on prod.

    Thanks in advance,

    Adam

  • January 26, 2011 at 1:35 pm

    #1278759

    i believe CONNECT privileges automatically gives you rights to tempdb,so because of that you can create a temp table.

    so you could insert data into a temp table, which would not violate the restrictions for creating a table in that specific database, but you could then insert into the table from the temp (assuming you have permissions for that.

    otherwise you could write a statement which contains everything in a script itself...

    ie

    INSERT INTO Fintal Table ...

    SELECT ColumnList FROM (

    select ColumnList UNION ALL

    select ColumnList ) myAlias

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 26, 2011 at 3:45 pm

    #1278835

    Yes, but my local server is not linked to the prod so I cannot insert into a temp on prod from my local instance and of course I have tried to link the only to get off course permission denied.

    But a person in my group told me (cuz I am new here), that we have...

    Adam

    Its not a script, per se.

    there is a table called tblImport that is a staging table with one big field in it. We use it a lot to read lines of fixed/delimited text into from text files, then parse out in sql and do stuff with it.

    you can clear that table, and insert your data to it. Then you have to do some string sql to get columns like

    select left(data,9) as ssn

    , substring() as otherData

    , substring(xxxx,xxx,xxx) as otherData

    from tblImport

    SO this will be my solution. I hope this helps someone along the way to atleast have the DBAs create a staging table for situations like this!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply