0

Please read, my title is vague only because I can't explain what I need in a single sentence.

So I have a table table1 and a table2. Now, table1 contains data that I need to insert into table2. The issue is, table1 does not exist in environment 1, but it does exist in environment 2 (CPS and dev).

My question is; is there a way in SQL Management Studio to actually auto-generate a script that pulls the data from table1, and places them in a transaction that I can then run in our other environment? Such as:

UPDATE table2 SET column1 = 'data' etc...
Toby Caulk
  • 266
  • 1
  • 6
  • 21

2 Answers2

0

You need to link two servers first.

Please have a look How to Create a Linked Server

Then do something like below

Insert Into Server1.Databasename.dbo.TableName(Col1,Col2,Col3)
Select Col1, Col2, Col3
From Server2.Databasename.dbo.TableName

Other option is to create Simple ETL Package using SSIS to load data and insert.

Read more - Creating a Simple ETL Package

huMpty duMpty
  • 14,346
  • 14
  • 60
  • 99
  • Not possible, I should have made that more clear. I don't have control over something like that. – Toby Caulk Sep 03 '15 at 14:22
  • @TobyCaulk: Is this one off process or something you going to do regularly? – huMpty duMpty Sep 03 '15 at 14:23
  • This is a one off process, we used a test table for canned data for a new feature but that table unfortunately does not exist in our pre-staging env so I have to manually copy the data over. – Toby Caulk Sep 03 '15 at 14:32
  • @TobyCaulk: Then the easiest option is to export the table in to an excel file and then import that file back to the other server where you can update the date! – huMpty duMpty Sep 03 '15 at 15:00
0

If you are able to create an SSIS package you could do it that way.

A DataFlow task with a database source and database destination. The database source will grab everything from table1 and insert it into the database destination which will be table 2.

Doolius
  • 854
  • 6
  • 18