Insert query for one database to another database inserting in sql server 2005

Given two databases named: SourceDB and TargetDB

SourceDB has a table SourceTable

TargetDB has a table TargetTable


The tables have some compatible columns you want to copy.

try this:


Code Snippet
USE SourceDB -- not needed if already in the source database's context

INSERT INTO TargetDB.dbo.TargetTable (ColumnID, SomeColumn1, SomeColumn2)

SELECT ColumnID, SomeColumn1, SomeColumn2

FROM SourceTable


I figure it might be possible to use SELECT * but if the number of columns don't match, I got an error. It was easy enough to explicitly specify what I wanted.


It's quite possible the schema 'dbo' doesn't work in all circumstances, but I figure if people are asking (like I was), we must be fairly novice and haven't set up complex databases, yet. (Hope this helps).
http://www.windows-tech.info/15/4a5e7d5023925d33.php
http://www.windows-tech.info