Number of tables is equal to number of jobs?

Question

0.00/5 (No votes)

See more:

C#

Hi there,

We have a database which consists of 12 tables. I want to create a SQL Job to copy the table data from one database to another (same server) hourly in a set schedule time. Data will be updated every hour at source database and that fresh data should be copied to my destination database time to time. So, in my Job step, I wrote a below query,

delete from <destination_table>
go
insert into <destination_table>
select * from [SourceDatabase].dbo.<Source_table>
go

And this query is working fine for 1 job and 1 table.

If I want to pass all 12 tables in the same job rather than creating 12 jobs for 12 tables, which query helps so that copying fresh data should be done by a single query for all 12 tables?

or shall we create 12 steps for 12 tables in that job? 

Please suggest.

Thanks,

What I have tried:

Delete from <destination_table>
go
insert into <destination_table>
select * from [SourceDatabase].dbo.<source_table>
go

THE ABOVE QUERY FOR 1 TABLE

Posted 11-Sep-16 22:08pm

Member 10914736

Updated 12-Sep-16 0:57am

Add a Solution

Comments

Maciej Los 12-Sep-16 4:58am

Why you need that functionality? It's sounds very irrational.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

FranzBe · Accepted Answer · 2016-09-12T00:46:00

1) you can write a stored procedure that handles the 12 tables one after another, you than call this one stored proc from your job; perhaps you want to use a transaction around the delete and insert

SQL

CREATE PROCEDURE [dbo].[psp_sync_stuff]
AS
  DECLARE @iCnt INT
  SET NOCOUNT ON
-- start of job
-- first table
SELECT *
 INTO #tempTableName
 FROM  [ServerName].DbName.dbo.sourceTableName 


SELECT @iCnt = COUNT(0) FROM  #tempTableName
IF @iCnt > 0
  BEGIN
    BEGIN TRANSACTION
      DELETE FROM   targetTableName
      INSERT INTO   targetTableName
      SELECT * FROM #tempTableName
    COMMIT TRANSACTION
END

-- second tables goeshere
-- [...]

SET NOCOUNT OFF
GO

2) when you want to put your tablesnames in variables you will need sp_executesql to perform the resulting dynamic sql, something in this direction

SQL

DECLARE @vSql        NVARCHAR(4000)
DECLARE @vSourceTableName1 NVARCHAR(100)
SET @vSourceTableName1 = "[ServerName].DbName.dbo.sourceTableName"
SET @vSql = "SELECT * INTO #tempTableName FROM " + @vSourceTableName1
EXECUTE sp_executesql @vSql

3) perhaps you should read something about replication and see whether it might perform the job you want to be done (you are not mentioning your Database Server system, MS SQL Server or Oracle, or ?)

Birsh Pal · Accepted Answer · 2016-09-12T00:57:00

From the question I assume
#1- Your SQL job will be running on source database
#2- Names of source and destination tables may or may not be same.

There are two solutions for your problem.

First, if #2 is not the case, that means table structure and constraints are same in source and destination tables you can use SQL Replication feature of the database.

Second, if both the points mentioned above are correct, you can create SQL Job in source database as planned.Below mentioned is the script that uses dynamic query, for the same to be placed in the job step command.
Note: You need to edit destination database name in this. Besides, you should insert source and destination names in the table variable @SourceDestinattionTables. Sample data is inserted, you can add/remove according to your need.

BEGIN TRANSACTION
DECLARE @DestinationDatabaseName VARCHAR(50)
DECLARE @SourceDestinattionTables TABLE ( TableMappingID INT Identity(1,1), SourceTable VARCHAR(500), DestinationTable VARCHAR(500))
--------------- INPUT--------- SET Destination Database name ---------------
SET @DestinationDatabaseName = 'DestDB'

---------------INPUT------ SET Source and Destination Table names ---------------
INSERT INTO @SourceDestinattionTables
VALUES('SourceEmployees','DestEmployees'),
('SourceOrders','DestOrders')

DECLARE @DestinationTable VARCHAR(100)
DECLARE @SourceTable VARCHAR(100)
DECLARE @TableCount INT
DECLARE @index INT
DECLARE @InsertScript NVARCHAR(MAX)

SELECT @TableCount = COUNT(1)
FROM
@SourceDestinattionTables

SET @index = 1
WHILE(@index <=@TableCount)
BEGIN

SELECT
@DestinationTable = DestinationTable,
@SourceTable = SourceTable
FROM
@SourceDestinattionTables
WHERE
TableMappingID = @index

SET @InsertScript =''
SELECT @InsertScript = 'DELETE FROM ' +@DestinationDatabaseName+'.dbo.'+@DestinationTable + ' '
SELECT @InsertScript = @InsertScript + 'INSERT INTO ' +@DestinationDatabaseName+'.dbo.'+@DestinationTable+ ' SELECT * FROM ' +@SourceTable

SELECT @index = @index + 1

EXECUTE sp_executesql @InsertScript

END
COMMIT

Number of tables is equal to number of jobs?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0