How to pass a table from one DB to another in Ms Sql 2008 Express

What is the best way to copy a table, including its configuration/columns info, in Sql 2008 express.
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
You can do an import from one db to anothter.

You can do SELECT INTO

For instance, Select * INTO table2 from table1
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Added database names..
SELECT *
INTO target_database_name..table_name
FROM source_database_name..table_name

Open in new window

afaik this will not copy constraints, defaults, relationships, etc.
For that you'll want to script out the source table, then re-execute anything in the target table that is missing.

Also this may require some special handling if one column is an identity, probably re-setting the target column as identity.
0
rayluvsAuthor Commented:
Forgot to mention, we did use "SELECT INTO" but like  jimhorn  says, it does not copy constraints, defaults, relationships, etc.; and we need those.

When you say "script out the source table", you mean goto table in sql express >> right-click >> script table as >> Create to.  Correct?

If this so, SELECT INTO will not work?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I don't have SQL Express in front of me, but in SSMS 2008 R2 it's
Click on table >> right-click >> Script table as >> Create >> New query editor.

>If this so, SELECT INTO will not work?
It'll work given what we've just discussed, however if you want to catch everything it's better to script out the Create per the above, then execute it in the target database, then do an INSERT (not INSERT INTO).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Ok we'll try.
0
rayluvsAuthor Commented:
It doesn't work (may be doing something wrong).

Created the table with the recommendatiosn, but in the actual data copy giving error (We tried these):


SELECT *
 target_database_name..table_name
FROM source_database_name..table_name

and

INSERT INTO  target_database_name..table_name
SELECT * FROM source_database_name..table_name
0
rayluvsAuthor Commented:
places attention
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
jimhorn might have expressed vague: You either SELECT INTO or create the table manually (per script as generated), and populate it with INSERT INTO.  You don't mix both methods. SELECT INTO expects the target table to not exist, else it will error out.

You told us you created the table per script, generated from the source table. That way, all columns are in the same sequence, so that cannot be the issue. What error do you get exactly on INSERT INTO? There might be constraint violations in the source table, which were not checked when those constraints were implemented, so e.g. a duplicate key might occur.

In general, it is always a very good idea to provide the detailled error message when saying "it does not work", as mind guessing is a Wizardry.
0
Jim P.Commented:
If you the table has an identity column then you have to specify the columns.

SET Identity Off
go
INSERT INTO  target_database_name..table_name(col1, col2, col3, .......)
SELECT (col1, col2, col3, .......) FROM source_database_name..table_name
go
SET Identity On
go

Open in new window

0
Surendra NathTechnology LeadCommented:
in order to serve you better and clear confusion, can you please give us the below data

1) your table create statement, including all the constraint information & default information.
2) sample data.
0
Vadim RappCommented:
The best is to use SQL Server Database Publishing Wizard, which you will find under SQL Server directory under Tools\Publishing, or search for SqlPubWiz.exe. If it's not there, version 1.3 can be downloaded from http://go.microsoft.com/fwlink/?LinkId=119368

Another way is to use "Import and Export Data" that you will find in Start menu.

Which way is better depends on whether you can access source and destination at the same time.
0
rayluvsAuthor Commented:
Correct, we had to set the Identity.

Just re-read the entire thread and wanted to give you guys our feedback regarding your observations and recommendation exactly copying a table along with its properties, like constraints, identity, etc., to another DB:

1. Right-click on origin table >> select script table as >> Create To
    >> New Query Editor Window
2. Go to destination DB and run the script to create the table
3. In the destination DB run SELECT * target_database_name..table_name
    FROM source_database_name..table_name


There is no one script for copying tables from one DB to another including their tables configuration.

Finally, if we wanted to just copy the tables without script using our Ms Sql 2008 Express, just:

1. Use 'SQL Server Database Publishing Wizard'.
2. Use "Import and Export Data" from the menu.

Is this correct?
0
Vadim RappCommented:
> if we wanted to just copy the tables without script using our Ms Sql 2008 Express,

It's not "without script"; the result of running Publishing Wizard will be the script to create the tables with all their constraints etc as well as the data. You then run that script on the target sql server.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
And 3. would be
SELECT * INTO target_database_name..table_name
    FROM source_database_name..table_name
0
rayluvsAuthor Commented:
Ok thanx all!!
0
rayluvsAuthor Commented:
Note: SqlPubWiz.exe never worked.  Ran it and seems to start but never seems to actually do any installation.
0
Vadim RappCommented:
> SqlPubWiz.exe never worked.  Ran it and seems to start but never seems to actually do any installation.

it's not an installation, it's the actual executable.screenshot
0
rayluvsAuthor Commented:
Strange, when executing it, it doesn't do anything or show anything.
0
Vadim RappCommented:
When this happens with .Net applications, the usual remedies are (1) looking into event log (2) reinstalling the application (3) reinstalling .Net framework.

More "hardcore" approach is to use Windbg as described here (with adjustments for the fact that this is winforms application rather than asp.net)
0
rayluvsAuthor Commented:
Ok will do.  Regarding Windbg I am totally lost.  Just checked out the link and trying to understand it.  Can you tell me in a nutshell what is Windbg for?
0
Vadim RappCommented:
>  Can you tell me in a nutshell what is Windbg for?

It's for working with dumps, i.e. binary representations of compiled code, continuing the traditions described here 30 years ago.
0
rayluvsAuthor Commented:
oh you mean that famous blue-screen that sometime says "dump" somewhere?  so in conclusion Windbg is for a better understanding and problem-identifying those types of complex error messages? yes?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
WinDbg can be used to check those BSOD dumps, yes, but also crash dumps of applications, or perform "live" debugging of processes instead of a full-size debugger/IDE like Visual Studio.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.