[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to create a copy of table from one database to another(need schema as well)+sql server 2005

Posted on 2009-12-21
9
Medium Priority
?
302 Views
Last Modified: 2012-05-08
Hi,
In db1 I do not have table1 which exists in db2.I need to create the table1 in db1 and copy the data as well.i am using sql server 2005.
Any suggestions appreciated.
0
Comment
Question by:RIAS
9 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26095242
do the databases run on the same server? if so a query like the one will do that...

on db1
SELECT * INTO schemaName.table1
FROM db2.schemaName.table1

if that are on different servers than first you should define a linked server from db1 to db2 and then run a quey like this..

SELECT * INTO schemaName.table1
FROM linkedServerName.db2.schemaName.table1


0
 
LVL 2

Expert Comment

by:BlackIce80
ID: 26095253
you can use the sql export data wizard:
in SQL Server Management Studio
right click the database
select Tasks->Export data
and follow the wizard

or you could use Microsoft SQL Server Database Publishing Wizard 1.1:

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-
A410-371A838E570A&displaylang=en
0
 
LVL 5

Accepted Solution

by:
spikelly earned 2000 total points
ID: 26095641
You can do it in two simple steps:

Step1: on DB2, right-click on table1, then select "Script table as-->CREATE TO-->New Query Window Editor" this will script your table structure; then just run the generated script USING DB1 ( USE DB1 on top of the generated script).

Step2: Run this if your 2 databases are located on the same SQL server instance
INSERT INTO DB1.table1
SELECT * FROM DB2;table1
if not, then if you have a linked server just run it like this:
INSERT INTO DB1.table1
SELECT * FROM YourLinkedServerName.DB2;table1

And you're done !
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:RIAS
ID: 26095910
Hi Experts,
Thanks for all the solutions.But I am stuck on the first step on how to copy the schema from one database to another to create a table.They are on two different servers.
Example of the script is
USE [PiDev]
GO
/****** Object:  Table [dbo].[PERSONAL_DETAILS]    Script Date: 12/21/2009 14:18:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PERSONAL_DETAILS](
      [REFNO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [BANNER] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SURNAME] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [INITIALS] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FORENAMES] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MAIDNAME] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TITLE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SEX] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DOB] [datetime] NULL,
Regards
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 26096002
By using fully qualified name in
INSERT INTO statement you can create table as well as load the data from the source table.
0
 
LVL 5

Assisted Solution

by:spikelly
spikelly earned 2000 total points
ID: 26096274
That's great; you already have the script to create your table (table1) on DB1.
Now copy this script on the other server and run it on DB1;it'll create the table PERSONALS_DETAILS on DB1.
Now i need to know one thing: how are your servers connected? do you know how to create a linked server? Otherwise, using SQL 2008 Express will allow you doing this pretty simply since it embed the functionality which allows you to copy a table with the data in just one single script, and all you'll have to do it run the generated script on your second server; check this:
http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

Regards
0
 

Author Comment

by:RIAS
ID: 26096767
Hi Spikelly,
Did exactly what you suggested but getting an error here on the create script
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

ERROR
Msg 170, Level 15, State 1, Line 98
Line 98: Incorrect syntax near '('.

Regards
0
 
LVL 5

Expert Comment

by:spikelly
ID: 26096794
Please post the full script you generated...
0
 

Author Closing Comment

by:RIAS
ID: 31668486
Excellent!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question