SQL INSERT - how to execute a list of queries automatically

Question

I've never done this, so apologies if I'm being quite quite vague.

Scenario

I need to run a long series of INSERT SQL queries. This data is inserted in a table for being processed by a web service's client, i.e. the data is uploaded on a different server and the table gets cleared as the process progresses.

What I've tried

I have tried to add a delay to each Insert statement like so

WAITFOR DELAY '00:30:00'

INSERT INTO TargetTable (TableName1, Id, Type) SELECT 'tablename1', ID1 , 1 FROM tablename1

WAITFOR DELAY '00:30:00'

INSERT INTO TargetTable (TableName2, Id, Type) SELECT 'tablename2', ID2 , 1 FROM tablename2

But this has the disadvantage of assuming that a query will finish executing in 30 minutes, which may not be the case.

Question

I have run the queries manually in the past, but that's excruciatingly tedious. So I would like to write a program that does that for me. The program should:

Run each query in the order given
Wait to run the next query until the previous one has been processed, i.e. until the target table is clear.

I'm thinking of a script that I can copy into the command prompt console, SQL itself or whatever and run.

How do I go about this? Windows service application? Powershell function?

I would appreciate any pointers to get me started.

Can you just use a DOS batch file and OSQL command line SQL tool to submit your SQL ? Is there a need to make this more complex ? — PhillipH
– PhillipH, Commented Aug 18, 2014 at 9:32
@PhillipH Hi, absolutely no need to make this more complex than necessary. I have written a batch file before, but it's the first time I heard of the OSQL tool. Could you elaborate a bit more please? — U r s u s
– U r s u s, Commented Aug 18, 2014 at 9:38
this is documented here technet.microsoft.com/en-us/library/aa214012(v=sql.80).aspx and is supplied with SQL Server. It allows you to connect to and execute T-SQL against a server. Its the command-line equivalent to the Query Window in SQL Server Enterprise Manager. — PhillipH
– PhillipH, Commented Aug 18, 2014 at 12:37
I see, thank you. But how would I make sure that each query is processed before running the next one? — U r s u s
– U r s u s, Commented Aug 18, 2014 at 12:44

Santosh Gadge · Accepted Answer · 2014-08-18 10:19:47Z

1

You need to schedule job in SQL Server

http://www.c-sharpcorner.com/UploadFile/raj1979/create-and-schedule-a-job-in-sql-server-2008/

answered Aug 18, 2014 at 10:19

Santosh Gadge

621 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

U r s u s Over a year ago

Thanks, but I don't want to add another service on the server, because the server is not mine.

Collectives™ on Stack Overflow

SQL INSERT - how to execute a list of queries automatically

Scenario

What I've tried

Question

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Scenario

What I've tried

Question

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related