<p>I want to port data from one server's database to another server's database.
The databases are both on a different mssql 2005 server.
Replication is probably not an option since the destination database is generated from scratch on a [time interval] basis.</p>

<p>Preferebly I would do something like</p>

<pre><code>insert *
from db1/table1
into db2/table2
where rule1 = true
</code></pre>

<p>It's obvious that connection credentials would go in somehwere in this script.</p>

[{'answer_id': 70464, 'author': 'Thomas Rushton', 'author_id': 6977, 'author_profile': 'https://Stackoverflow.com/users/6977', 'pm_score': 0, 'selected': False, 'text': '<p>Can you use the Data Transformation Services to do the job?  This provides all sorts of bolt-together tools for doing this kind of thing.</p>\n\n<p>You can download the SQL Server 2005 feature pack from Microsoft\'s website\n<a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&amp;DisplayLang=en" rel="nofollow noreferrer">here</a></p>\n'}, {'answer_id': 70476, 'author': 'Galwegian', 'author_id': 3201, 'author_profile': 'https://Stackoverflow.com/users/3201', 'pm_score': 1, 'selected': False, 'text': "<p>Are SQL Server Integration Services (SSIS) an option? If so, I'd use that.</p>\n"}, {'answer_id': 70525, 'author': 'Matthew Pelser', 'author_id': 6331, 'author_profile': 'https://Stackoverflow.com/users/6331', 'pm_score': 6, 'selected': True, 'text': '<p>I think what you want to do is create a linked server as per <a href="http://web.archive.org/web/20150628090613/https://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx" rel="nofollow noreferrer">this webarchive snapshot of msdn article from 2015</a> or <a href="https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15" rel="nofollow noreferrer">this article from learn.microsoft.com</a>. You would then select using a 4 part object name eg:</p>\n<pre><code>Select * From ServerName.DbName.SchemaName.TableName\n</code></pre>\n'}, {'answer_id': 70817, 'author': 'kristof', 'author_id': 3241, 'author_profile': 'https://Stackoverflow.com/users/3241', 'pm_score': 1, 'selected': False, 'text': '<p>Would you be transferring the whole content of the database from one server to another or just some data from a couple of tables?</p>\n\n<p>For both options SSIS would do the job especially if you are planning to to the transfer on a regular basis. </p>\n\n<p>If you simply want to copy some data from 1 or 2 tables and prefer to do it using TSQL in SQL Management Studio then you can use linked server as suggested by pelser</p>\n\n<ol>\n<li>Set up the source database server as a linked server</li>\n<li>Use the following syntax to access data</li>\n</ol>\n\n<pre><code>select columnName1, columnName2, etc from serverName.databaseName.schemaName.tableName</code></pre>\n'}, {'answer_id': 72214, 'author': 'Philippe Grondier', 'author_id': 11436, 'author_profile': 'https://Stackoverflow.com/users/11436', 'pm_score': 1, 'selected': False, 'text': "<p>Well I don't agree with your comment on replication. You can start a replication by creating a database from scratch, and you can control either the updates will be done by updating the available client database or simply recreating the database. </p>\n\n<p>Automated replication will ease your work by automatically managing keys and relations.</p>\n\n<p>I think the easiest thing to do is to start a snapshot replication through MSSQL Server Studio, get the T-SQL corresponding scripts (ie the corresponding T-SQL instructions for both publication and subscriptions), and record these scripts as part of a job in the Jobs list of the SQL Agent or as a replication job in the replications folder. </p>\n"}, {'answer_id': 76953, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': "<p>You could go the linked server route. </p>\n\n<p>you just can't use the select * into you have to do an insert into select. </p>\n\n<p>I would avoid replication if you don't have experience with it as it can be difficult to fix if it breaks and can be prone to other problems if not properly managed. </p>\n\n<p>Keep it simple especially if the databases are small.</p>\n"}, {'answer_id': 18659242, 'author': 'Ardalan Shahgholi', 'author_id': 2063547, 'author_profile': 'https://Stackoverflow.com/users/2063547', 'pm_score': 3, 'selected': False, 'text': "<p>You can use Open Data Source Like this : </p>\n\n<pre><code>EXEC sp_configure 'show advanced options', 1\nGO\nRECONFIGURE\nGO\n\nEXEC sp_configure 'Ad Hoc Distributed Queries', 1\nGO\nRECONFIGURE\nGO\n\n\nSELECT  *\nFROM    OPENDATASOURCE('SQLOLEDB',\n                   'Data Source=&lt;Ip Of Your Server&gt;;\n                    User ID=&lt;SQL User Name&gt;;Password=&lt;SQL password&gt;').&lt;DataBase name&gt;.&lt;SchemaName&gt;.&lt;Table Or View Name&gt;\n\nGo\n</code></pre>\n"}, {'answer_id': 39628957, 'author': 'Ahmed Tambe', 'author_id': 6861533, 'author_profile': 'https://Stackoverflow.com/users/6861533', 'pm_score': -1, 'selected': False, 'text': "<pre><code>CREATE VIEW newR1 \nAS\nSELECT * from OPENQUERY ([INSTANCE_NAME], 'select * from DbName.SchemaName.TableName')\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/70455', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/11333/']