Need some help with SQL

[tom3297]

Ik trying to connect two databases in Microsoft SQL.

I need to move data from one to the other. to do this i made a linked Database.

Now im trying to move data from a Table1 on the linked server to a table on the server ill be using.

But i only want to move specific Collums to an other specific Collum.

And to make is extra difficult i need to put a C in the first Collum of everything i add do it will know what is added.

 

Thanks in advance.

[myselfolli]

Can you give us column names or something?

[Swaffy]

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

 

so something like :

INSERT INTO table2
SELECT 'C' as Col1, col2, col3 FROM table1
WHERE condition;

 

[ThePD]

My experience with MSSQL is limited, but if you are using two seperate databases you will likely need to specify the schema.table.column in your references.

I don't understand what you mean by adding a 'C' to the column. Do you mean you are changing the name of the column or are you trying to concatenate a 'C' to your data import?

[Franck]

54 minutes ago, tom3297 said:

Ik trying to connect two databases in Microsoft SQL.

I need to move data from one to the other. to do this i made a linked Database.

Now im trying to move data from a Table1 on the linked server to a table on the server ill be using.

But i only want to move specific Collums to an other specific Collum.

And to make is extra difficult i need to put a C in the first Collum of everything i add do it will know what is added.

 

Thanks in advance.

is it a one time move or you will have to keep it up to date ?

[tom3297]

27 minutes ago, myselfolli said:

Can you give us column names or something?

so the data needs to go,

from SRVSQL03 database TPD_PROD Table dbo.hardware

 

To SRV-MDT-01 database MDT  Table dbo.settings

From colum naam               to colum OSDComputerName

From colum objectid           to colum ID

And Add C                          to colum Type

 

To SRV-MDT-01 database MDT  Table dbo.computerIdentity

From colum naam               to colum Description

From colum Serienummer  to colum SerialNumber

From colum objectid           to colum ID

 

[tom3297]

1 minute ago, Franck said:

is it a one time move or you will have to keep it up to date ?

It Needs to be kept up to date.

 

[Franck]

5 minutes ago, tom3297 said:

It Needs to be kept up to date.

 

Then you better use replication. What you do is tell your dev server to be a replication and you select your table.

Then your production server you make it subscribe to the other server. And for your 2 different tables you should create 2 views.

 

Replication is like a auto backup and restore ish kind of thing. There could be a couple milliseconds delay in between both table state.

[kladzen]

You would be using MERGE...

 

take a look here

 

https://stackoverflow.com/questions/13613983/sql-server-synchronizing-2-tables-on-2-different-databases

 

Once you have a working query that performs what you want, then just create a job that runs every X minute.. 

[tuguduvuka]

https://www.welookups.com/sql/sql_insert_into_select.html