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.