Want to import few tables from another DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Want to import few tables from another DB

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-01-19 : 17:23:32

I am planning to import almost 25 tables from another DB on the same server.

Problem, i am having is with identity columns, reseeding the id plus increment etc, is there a way the import data takes care of identity column plus the reseeding / increment etc.
i did this while importing a table but the identity "Yes" is turned to "NO" so obviously i have to reseed., is there a way via SQL server 2008 R2 to do it automatically.

Under edit mappings:

checked: Enable identity insert

checked: drop and recreate destination table check.

Thank you very much for the helpful info.

Kristen
Test

22859 Posts

Posted - 2012-01-19 : 17:39:00

SELECT *
INTO MyLocalTable
FROM MyOtherDatabase.dbo.RemoteTable

Then create any indexes, foreign keys, constraints, etc that were on the original table.

Or script the table on the OtherDatabase, with all indexes etc., create the table locally, then copy the data over. (If there isn't very much data then there is an option in the Script Task to create INSERT statements for each data row.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-01-20 : 06:04:37

Thanks Kristen,

If i use the select * into mylocaltable query will it take care of the identity column in the table plus the reseeding etc?

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 09:06:50

Yes, it will create the column with IDENTITY property. Not 100% sure if it will seed it correctly, I expect it will seed it to the next-available-number, which may not be the same as the Source table (which may be a higher number [e.g. if recently inserted row(s) were rolled back], or it could be a lower number if it has been reset at some time [on Source table].

If those are not issues you need to worry about then you should be fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources