0

I need to collect data from a SQL Server table, format it, and then put it into a different table.

I have access to SQL Server but cannot setup triggers or scheduled jobs.

I can create tables, stored procedures, views and functions.

What can I setup that will automatically collect the data and insert it into a SQL Server table for me?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
seaside_escape
  • 129
  • 15

4 Answers4

2

I would probably create a stored procedure to do this task.

In the stored procedure you can create a CTE or use temp tables (depending on the task) and do all the data manipulation you require and once done, you can use the SELECT INTO statement to move all the data from the temp table into the SQL Server table you need.

https://www.w3schools.com/sql/sql_select_into.asp

You can then schedule this stored procedure to run at a time desired by you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Standin.Wolf
  • 1,224
  • 1
  • 10
  • 32
  • Thank you for your answer. How can I run the Stored Procedure at a time desired by me? – seaside_escape Jan 28 '20 at 14:44
  • @seaside_escape you can schedule the stored procedure to run at a specific time by using SQL Server Agent to schedule jobs that call your procedure. Those jobs have actual Schedules you can set up to run weekly, every day, every minute or any timeframe you would like. – Radu Gheorghiu Jan 28 '20 at 14:45
  • 1
    Thanks for the reply @RaduGheorghiu. I would only add that you can tell your DBA as well to help you out with that as you may not have rights to do that – Standin.Wolf Jan 28 '20 at 14:46
  • Yeah, it's a good idea to talk to your DBA (or whoever has access to more rights the database) and see how he can help. – Radu Gheorghiu Jan 28 '20 at 14:49
  • Unfortunately I can't use SQL Server Agent and the DBA won't help me. I was hoping there might be some hacks that could be cumbersome but would at least get the job done? – seaside_escape Jan 28 '20 at 15:04
  • Then the last thing would be run the Stored Procedure on your own. – Standin.Wolf Jan 28 '20 at 15:05
  • If the DBA can't/won't help then you need to come up with a different way. I would suggest writing a small application (c#) that calls your SQL code. This could be run as a scheduled job on an app (not a database) server. If this is not possible then you will need to work with your management on the business requirements and who is responsible. – JazzmanJim Jan 28 '20 at 20:07
1

A database is just a storage container. It doesn't "do" things automatically all by itself. Even if you did have the access to create triggers, something would have to happen to the table to cause the trigger to fire, typically a CRUD operation on the parent table. And something external needs to happen to initiate that CRUD operation.

When you start talking about automating a process, you're talking about the function of a scheduler program. SQL Server has one built in, the SQL Agent, and depending on your needs you may find that it's appropriate to enlist help from whoever in your organization does have access to it. I've worked in a couple of organizations, though, that only used the SQL Agent to schedule maintenance jobs, while data manipulation jobs were scheduled through an outside resource. The most common one I've run across is Control-M, but there are other players in that market. I even ran across one homemade scheduler protocol that was just built in C#.NET that worked great.

Based on the limitations you lay out in your question, and the comments you've made in response to others, it sounds to me like you need to do socialize your challenge within your organization to find out what their routine mechanism is for setting up data transfers. It's unlikely that this is the first time it's come up, unless the company was founded in the last week or two. It will probably require that you set up your code, probably a stored procedure or maybe an SSIS package, and then work with someone else, perhaps a DBA or a Site Operations team or some such, to get that process automated to fire when you need it to, whether through an Agent job or maybe a file listener.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
0

Well you have two major options, SP and SSIS. Both of them can be scheduled to run at a given time with a simple Job from the SQL Server Agent. Keep in mind that if you are doing this on a separate server you might need to add the source server as a Linked Server so you can access it from the script.

Ivan Ivanov
  • 131
  • 1
  • 2
  • 6
0

I've done this approach in the past and it has worked great. Note, for security reasons, I am not able to access the remote server's task scheduler, so I go through the SQL Server Agent:

  • Run a SQL Server Agent on a schedule of your choice
  • Use the SQL Server Agent to call an SSIS Package
  • The SSIS Package then calls an executable which can pull the data you want from your original table, evaluate it, and then insert a formatted version of it, one record at a time. Alternatively, you can simply create a C# script within the SSIS package via a Script Task.

I hope this helps. Please let me know if you need more details.

Peter J
  • 21
  • 4