SQL Server Import table keeping default values

Question

I am importing a table from one database to another in SQL Server 2008 by right-clicking the target database and choosing > Tasks > Import Data...

When I import the table I get the column names and types and all the data fine, but I lose the primary key, identity specifications and all the default values that were set in the source table. So now I have to set all the default values for each column again manually. Is there any way to get the default values with the import, or even after with a Query?

I am VERY new to this and flailing in the dark, so forgive me if this is a really stupid question...

Answer 1

Try generating a create script for your source table and use that script to create your complete destination table structure. Then use the import data wizard to copy data from the source table into your newly created table.

Answer 2

you can use bcp command for this work, this command have a switch that keep identities unchanged.

this command is very useful when you have huge amount of data.

for more information see this link.