It is a common practice in IT industry that organizations manages data in different databases for different departments. For example a company has a Quality assurance database and a developer database. But they faces an issue when they need a record from some database to their database. In that case, IT person needs to write an insert query with the help of existing data values. But it won’t be a preferable solution if you have 30 or more columns in your table. I would recommend to use this statement to resolve the issue,
insert into QualityAssuranceDB.dbo.Customers (columnA, ColumnB)
Select columnA, columnB from DeveloperDB.dbo.Customers
Isn’t that too simple ?
But wait for a while, did we miss something ?
Yup, still if we have 30 or more columns then how to write “name of the columns” ? do we need to pick column names one by one ?
No, there is no need. For that the following script will help:
select (name + ',') as TableColumns from sys.columns where object_id = object_id('YourTableName')
Copy the result and paste into query window to represent your table column names. If you want to exclude the column that belongs to identity fields then use the below script:
select (name + ',') as TableColumns from sys.columns where object_id = object_id('YourTableName') and is_identity = 0
I hope its a viable solution to this problem.
NOTE: The instructions will work if the two databases belongs to the same location.
Awaited for your feedback….