Insert journal in SQL Server with data from another table in another server

Question

Insert journal in SQL Server with data from another table in another server

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I have to do a SQL Server 'job' scheduled for every day at 10 in the morning. The Work would be based on dump the data of a table of a database of a server in other 2 tables (with operations of by means) in another database of another server.

I do not know how to go through all the records, I understand that loops for and foreach do not exist in SQL. The access to other servers I think have it controlled. They are linked and lets me access their data but not insert (I guess it is because it is another server, it will be read only, I do not know). The idea would be something like this:

--TABLA_FINAL1
INSERT INTO [BDFINAL].[dbo].[TABLA_FINAL1]
SELECT
    campo1_tablaDN16,
    campo1_tablaDN16,
    'Parametro fijo',
    @ParametroCalculado,
    campo1_tablaDN16,
    ...(otros)
FROM [192.168.1.15].[BD_BASE].[dbo].[TABLA_BASE];


--TABLA_FINAL2
INSERT INTO [BDFINAL].[dbo].[TABLA_FINAL2]
SELECT
    campo1_tablaDN16,
    campo1_tablaDN16,
    'Parametro fijo',
    @ParametroCalculado,
    campo1_tablaDN16,
    ...(otros)
FROM [192.168.1.15].[BD_BASE].[dbo].[TABLA_BASE];

I do not know if the syntax would be correct, of course I mark error in the fields and that would serve if it were a single record, the idea would be to dump all (those that meet a certain condition). Any ideas?

sql sql-server

asked by Noark 11.05.2016 в 10:25

source

3 answers

Using "If" inside triggers Does not show Toast message in Android validation

score 1 · Answer 1

It depends on where you execute the JOB, you must do one thing or another, although only the first line changes

INSERT [TABLA_FINAL1]
SELECT
campo1_tablaDN16,
campo1_tablaDN16,
'Parametro fijo',
@ParametroCalculado,
campo1_tablaDN16,
...(otros)
FROM [192.168.1.15].[BD_BASE].[dbo].[TABLA_BASE];

In case of executing the job in the source table it would be

INSERT [IP SERVIDOR FINAL].[BD_BASE].[dbo].[TABLA_FINAL1]
SELECT
campo1_tablaDN16,
campo1_tablaDN16,
'Parametro fijo',
@ParametroCalculado,
campo1_tablaDN16,
...(otros)
FROM [TABLA_BASE];

and if you want to filter by dates, I would recommend using this conversion

CONVERT(varchar, fecha filtro,111) = CONVERT(varchar, getdate()-1,111)

score 0 · Answer 2

0

After a lot of searching it turns out that if you can do loops on sql server:

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
BEGIN
   {...statements...}
   SET @cnt = @cnt + 1;
END;
introducir el código aquí

answered by 11.05.2016 в 13:40

score 0 · Answer 3

try this

INSERT INTO [BDFINAL].[dbo].[TABLA_FINAL2]
SELECT
    campo1_tablaDN16,
    campo1_tablaDN16,
    'Parametro fijo',
    @ParametroCalculado,
    campo1_tablaDN16,
    ...(otros)
FROM [192.168.1.15].[BD_BASE].[dbo].[TABLA_BASE]
where (getdate()-1)
Ojo si tu tabla tiene el dia puedes hacerlo asi
INSERT INTO [BDFINAL].[dbo].[TABLA_FINAL2]
SELECT
    campo1_tablaDN16,
    campo1_tablaDN16,
    'Parametro fijo',
    @ParametroCalculado,
    campo1_tablaDN16,
    ...(otros)
FROM [192.168.1.15].[BD_BASE].[dbo].[TABLA_BASE]
where columna_fecha_ayer = (getdate()-1)