Today I had to move some data from a staging server to a production server, but instead of doing the manual move like usual I created a SQL script using the SQL Server syntax for OPENROWSET to move the data. It is a lot like the entry I made a few weeks ago with the Excel syntax, but a little different. This script was ran on the production server in order to pull the data from the staging server (which is not a linked server).

In the script (names and passwords changed for reasons I hope you know) below, STAGING - is the name of the staging database server, user - is the sql server login, password - is the password for that login, report_db is the name of the database.

-- ParameterID is an IDENTITY so let it create its own value
INSERT INTO Report_Parameter
 SELECT ReportID,
  ParameterName,
  ParameterType,
  DefaultValue,
  ValidValueList,
  Nullable,
  AllowBlank,
  Prompt,
  UseQuery,
  CommandText,
  CommandType
 FROM
 OPENROWSET('SQLOLEDB',
 'STAGING';'user';'password',
 'SELECT *
  FROM STAGING.report_db.dbo.Report_Parameter
  WHERE ReportID = 14
  ORDER BY ParameterID')

I know I could have done a DTS package faster, but I think this SQL script will be easier to edit for one-offs (that never seem to be one-off), besides that I thought it would be better to have this script in my reusable code files.