0

I have an requirement in order to transfer information from Table_1 in Database Test_1 to Table_2 in Database Test_2

Also these information should be in schedule Transfer with specific time(Ex: 11am)

I am using MS SQL 2014 Express edition.

New to SQL, So kindly guide me through in order to achieve my requirement

Kanimozhi
  • 1
  • 2

2 Answers2

0

SQL Agent is what you would use to schedule this transfer at 1100, but it is not included with SQL Server Express. You can read more on what features are included and not included here, in the MS Documents.

Since you can't use SQL Agent, you will have to use another scheduling mechanism like a PowerShell Script, Batch Script, etc that's triggered via Windows Scheduler or a Cron Job or whatever with the sqlcmd utility

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of available modes:

  • At the command prompt.
  • In Query Editor in SQLCMD mode.
  • In a Windows script file.
  • In an operating system (Cmd.exe) job step of a SQL Server Agent job.
S3S
  • 24,809
  • 5
  • 26
  • 45
0

As a starting point to get some ideas of how it can be done:

Step 1: create a stored procedure that will perform a table copy:

USE yourDB
GO
CREATE PROC uspCopyTable
AS
IF OBJECT_ID('dbo.Table_2') IS NOT NULL 
DROP TABLE dbo.Table_2

SELECT * INTO dbo.Table_2 FROM dbo.Table_1

Step 2: schedule the execution of the stored procedure using Windows Task Scheduler:

sqlcmd.exe -Q "EXEC dbo.uspCopyTable" -o "C:\Temp\MyOutput.txt" -d "yourDB"
Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • Thanks for your prompt response..can you explain the second step in detail.. Should I run this command in cmd or copy this command in notepad & save it as Bat file – Kanimozhi Mar 12 '19 at 16:32
  • Is it possible to set a flag or any identification for the data's that are imported from Table_1? – Kanimozhi Mar 12 '19 at 16:35
  • I would rather advise using a bat file. Regarding identification of the data, it can be achieved perhaps by adding extra column like `[SourceTable]`. For example: `SELECT *, 'Table_1' AS SourceTable INTO dbo.Table_2 FROM dbo.Table_1` – Alexander Volok Mar 12 '19 at 19:34
  • If I add an extra column like you said... How to update that column.. When I am importing or transferring data from Table_1 to Table_2... Also.. To create bat file... What command shou I give... – Kanimozhi Mar 12 '19 at 19:50
  • In a snippet I posted above, the column is already prefilled with value 'Table_1'. Regarding bat file, if it will be executed locally, you can literally copy paste the code from my answer – Alexander Volok Mar 12 '19 at 19:53