Import data

FAQ

Profile

Import data

Database Help (Home) -> Server

Next: Changing the past

Author

Message

Markgoldin

External

Since: Jun 10, 2008
Posts: 16

(Msg. 1) Posted: Mon Apr 06, 2009 9:55 am Post subject: Import data Archived from groups: microsoft>public>sqlserver>server (more info?)

I have two SQL servers: Source is a 2005 server and a Target is 2008. I need to copy tables (tables only) from 2005 to 2008. All my 2005 tables have Identity column on. What is a best way of doing that? Thanks >> Stay informed about: Import data

Eric Isaacs

External

Since: May 13, 2008
Posts: 367

(Msg. 2) Posted: Mon Apr 06, 2009 9:58 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

In SQL Server Managment Studio, connect to your SQL Server 2005 database and script out the database objects by right clicking on the database name, select Tasks then Generate Scripts. Follow the wizard to select the options to you prefer in the scripts. The generated script(s) can then be executed in the 2008 database to create the tables. Just connect to the 2008 database and execute the script(s) against the new database. The scripts will do the structure only, not the data, as you requested. If you want the data too, the best way to do it would be via a backup and restore operation. I hope that helps! Eric Isaacs J Street Technology, Inc. http://www.JStreetTech.com >> Stay informed about: Import data

Artemakis Artemiou

External

Since: Apr 06, 2009
Posts: 1

(Msg. 3) Posted: Mon Apr 06, 2009 4:25 pm Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

If the tables do not have referential constraints, then you can use the SQL Server Import and Export Wizard from SQL Server 2008. You can use the SQL Server 2005 Database as the source and the corresponding SQL Server 2008 database as the destination. This process will create the two tables in SQL 2008 with the same names and data as in the source (using the default settings). Then, you will have to modify the newly-created tables in SQL 2008 and set the identity columns. Also, you will need to modify the identity seed for avoiding future conflicts with the existing data. As an example, if the largest identity value is 100 in one table let's say, you will have to set the identity seed to 101 etc. Cheers, Artemakis Artemiou, SQL Server MVP http://aartemiou.blogspot.com "Markgoldin" wrote in message > I have two SQL servers: Source is a 2005 server and a Target is 2008. > I need to copy tables (tables only) from 2005 to 2008. All my 2005 tables > have Identity column on. > > What is a best way of doing that? > > Thanks > >> Stay informed about: Import data

Uri Dimant

External

Since: Aug 24, 2003
Posts: 1744

(Msg. 4) Posted: Tue Apr 07, 2009 1:26 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Mark If the task you are going to perform frequently, I would consider www.red-gate.com tool . I would go with SSIS pakage "Markgoldin" wrote in message >I have two SQL servers: Source is a 2005 server and a Target is 2008. > I need to copy tables (tables only) from 2005 to 2008. All my 2005 tables > have Identity column on. > > What is a best way of doing that? > > Thanks > >> Stay informed about: Import data

Jay

External

Since: Jul 18, 2008
Posts: 28

(Msg. 5) Posted: Tue Apr 07, 2009 7:26 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

"Uri Dimant" wrote in message > Mark > If the task you are going to perform frequently, I would consider > www.red-gate.com tool . > I would go with SSIS pakage You would recommend a 3rd party tool AND use a SSIS package? To simple copy tables from one instance to another? > > "Markgoldin" wrote in message > >>I have two SQL servers: Source is a 2005 server and a Target is 2008. >> I need to copy tables (tables only) from 2005 to 2008. All my 2005 tables >> have Identity column on. >> >> What is a best way of doing that? >> >> Thanks >> > > >> Stay informed about: Import data

Uri Dimant

External

Since: Aug 24, 2003
Posts: 1744

(Msg. 6) Posted: Tue Apr 07, 2009 7:26 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Jay I think i was not clear in my post. What I meant if you do such things frequently, so consider using a third part, otherwise use SSIS. "Jay" wrote in message > "Uri Dimant" wrote in message > >> Mark >> If the task you are going to perform frequently, I would consider >> www.red-gate.com tool . >> I would go with SSIS pakage > > You would recommend a 3rd party tool AND use a SSIS package? > To simple copy tables from one instance to another? > > > >> >> "Markgoldin" wrote in message >> >>>I have two SQL servers: Source is a 2005 server and a Target is 2008. >>> I need to copy tables (tables only) from 2005 to 2008. All my 2005 >>> tables have Identity column on. >>> >>> What is a best way of doing that? >>> >>> Thanks >>> >> >> > > >> Stay informed about: Import data

Markgoldin

External

Since: Jun 10, 2008
Posts: 16

(Msg. 7) Posted: Tue Apr 07, 2009 5:29 pm Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

I did not find copy tables from one Server to another is a simple task. First, no indexes were copied and Identiy columns are not moving correctly either. "Jay" wrote in message > "Uri Dimant" wrote in message > >> Mark >> If the task you are going to perform frequently, I would consider >> www.red-gate.com tool . >> I would go with SSIS pakage > > You would recommend a 3rd party tool AND use a SSIS package? > To simple copy tables from one instance to another? > > > >> >> "Markgoldin" wrote in message >> >>>I have two SQL servers: Source is a 2005 server and a Target is 2008. >>> I need to copy tables (tables only) from 2005 to 2008. All my 2005 >>> tables have Identity column on. >>> >>> What is a best way of doing that? >>> >>> Thanks >>> >> >> > > >> Stay informed about: Import data

Uri Dimant

External

Since: Aug 24, 2003
Posts: 1744

(Msg. 8) Posted: Wed Apr 08, 2009 3:25 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Mark How do you copy? Linked Server? "Markgoldin" wrote in message >I did not find copy tables from one Server to another is a simple task. > First, no indexes were copied and Identiy columns are not moving correctly > either. > > > "Jay" wrote in message > >> "Uri Dimant" wrote in message >> >>> Mark >>> If the task you are going to perform frequently, I would consider >>> www.red-gate.com tool . >>> I would go with SSIS pakage >> >> You would recommend a 3rd party tool AND use a SSIS package? >> To simple copy tables from one instance to another? >> >> >> >>> >>> "Markgoldin" wrote in message >>> >>>>I have two SQL servers: Source is a 2005 server and a Target is 2008. >>>> I need to copy tables (tables only) from 2005 to 2008. All my 2005 >>>> tables have Identity column on. >>>> >>>> What is a best way of doing that? >>>> >>>> Thanks >>>> >>> >>> >> >> > > >> Stay informed about: Import data

Jay

External

Since: Jul 18, 2008
Posts: 28

(Msg. 9) Posted: Wed Apr 08, 2009 5:26 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

"Markgoldin" wrote in message >I did not find copy tables from one Server to another is a simple task. > First, no indexes were copied and Identiy columns are not moving correctly > either. Are you copying just the table structure including indexes or the structure and the data? If just the structure then you could script out the tables (including index definitions) and run these on your destination server/database. I assume you only want to do this once unless you want to drop the table(s) on the destination server before re-copying in the future. > "Jay" wrote in message > >> "Uri Dimant" wrote in message >> >>> Mark >>> If the task you are going to perform frequently, I would consider >>> www.red-gate.com tool . >>> I would go with SSIS pakage >> >> You would recommend a 3rd party tool AND use a SSIS package? >> To simple copy tables from one instance to another? >> >> >> >>> >>> "Markgoldin" wrote in message >>> >>>>I have two SQL servers: Source is a 2005 server and a Target is 2008. >>>> I need to copy tables (tables only) from 2005 to 2008. All my 2005 >>>> tables have Identity column on. >>>> >>>> What is a best way of doing that? >>>> >>>> Thanks >>>> >>> >>> >> >> > > >> Stay informed about: Import data

Markgoldin

External

Since: Jun 10, 2008
Posts: 16

(Msg. 10) Posted: Wed Apr 08, 2009 7:59 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

I was using Data Inport Wizard. All I wanted is to copy all tables from one database to another SQL Server into the same database. By copy I mean data has to be intact. Exactly the same. "Uri Dimant" wrote in message > Mark > How do you copy? Linked Server? > > > "Markgoldin" wrote in message > >>I did not find copy tables from one Server to another is a simple task. >> First, no indexes were copied and Identiy columns are not moving >> correctly either. >> >> >> "Jay" wrote in message >> >>> "Uri Dimant" wrote in message >>> >>>> Mark >>>> If the task you are going to perform frequently, I would consider >>>> www.red-gate.com tool . >>>> I would go with SSIS pakage >>> >>> You would recommend a 3rd party tool AND use a SSIS package? >>> To simple copy tables from one instance to another? >>> >>> >>> >>>> >>>> "Markgoldin" wrote in message >>>> >>>>>I have two SQL servers: Source is a 2005 server and a Target is 2008. >>>>> I need to copy tables (tables only) from 2005 to 2008. All my 2005 >>>>> tables have Identity column on. >>>>> >>>>> What is a best way of doing that? >>>>> >>>>> Thanks >>>>> >>>> >>>> >>> >>> >> >> > > >> Stay informed about: Import data

Uri Dimant

External

Since: Aug 24, 2003
Posts: 1744

(Msg. 11) Posted: Wed Apr 08, 2009 9:25 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Mark Ok, I have not used DIW, instead SSIS Package itself. What if you script out the structure, then run the script on the destination server and finally move the data (by using linked server)? "Markgoldin" wrote in message >I was using Data Inport Wizard. > All I wanted is to copy all tables from one database to another SQL Server > into the same database. > By copy I mean data has to be intact. Exactly the same. > > "Uri Dimant" wrote in message > >> Mark >> How do you copy? Linked Server? >> >> >> "Markgoldin" wrote in message >> >>>I did not find copy tables from one Server to another is a simple task. >>> First, no indexes were copied and Identiy columns are not moving >>> correctly either. >>> >>> >>> "Jay" wrote in message >>> >>>> "Uri Dimant" wrote in message >>>> >>>>> Mark >>>>> If the task you are going to perform frequently, I would consider >>>>> www.red-gate.com tool . >>>>> I would go with SSIS pakage >>>> >>>> You would recommend a 3rd party tool AND use a SSIS package? >>>> To simple copy tables from one instance to another? >>>> >>>> >>>> >>>>> >>>>> "Markgoldin" wrote in message >>>>> >>>>>>I have two SQL servers: Source is a 2005 server and a Target is 2008. >>>>>> I need to copy tables (tables only) from 2005 to 2008. All my 2005 >>>>>> tables have Identity column on. >>>>>> >>>>>> What is a best way of doing that? >>>>>> >>>>>> Thanks >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > >> Stay informed about: Import data

Markgoldin

External

Since: Jun 10, 2008
Posts: 16

(Msg. 12) Posted: Wed Apr 08, 2009 9:25 am Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Whta do you mean by moving data? Select and insert? "Uri Dimant" wrote in message > Mark > Ok, I have not used DIW, instead SSIS Package itself. > What if you script out the structure, then run the script on the > destination server and finally move the data (by using linked server)? > > "Markgoldin" wrote in message > >>I was using Data Inport Wizard. >> All I wanted is to copy all tables from one database to another SQL >> Server into the same database. >> By copy I mean data has to be intact. Exactly the same. >> >> "Uri Dimant" wrote in message >> >>> Mark >>> How do you copy? Linked Server? >>> >>> >>> "Markgoldin" wrote in message >>> >>>>I did not find copy tables from one Server to another is a simple task. >>>> First, no indexes were copied and Identiy columns are not moving >>>> correctly either. >>>> >>>> >>>> "Jay" wrote in message >>>> >>>>> "Uri Dimant" wrote in message >>>>> >>>>>> Mark >>>>>> If the task you are going to perform frequently, I would consider >>>>>> www.red-gate.com tool . >>>>>> I would go with SSIS pakage >>>>> >>>>> You would recommend a 3rd party tool AND use a SSIS package? >>>>> To simple copy tables from one instance to another? >>>>> >>>>> >>>>> >>>>>> >>>>>> "Markgoldin" wrote in message >>>>>> >>>>>>>I have two SQL servers: Source is a 2005 server and a Target is 2008. >>>>>>> I need to copy tables (tables only) from 2005 to 2008. All my 2005 >>>>>>> tables have Identity column on. >>>>>>> >>>>>>> What is a best way of doing that? >>>>>>> >>>>>>> Thanks >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > >> Stay informed about: Import data

Uri Dimant

External

Since: Aug 24, 2003
Posts: 1744

(Msg. 13) Posted: Wed Apr 08, 2009 1:27 pm Post subject: Re: Import data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Mark Let say yor hace already ran DDL on the destination server and have the tables but without the data. Create a linked server to the dest,server INSERT INTO SERV1.dbname.dbo.tbl SELECT * FROM yoursourcetable "Markgoldin" wrote in message > Whta do you mean by moving data? > Select and insert? > > "Uri Dimant" wrote in message > >> Mark >> Ok, I have not used DIW, instead SSIS Package itself. >> What if you script out the structure, then run the script on the >> destination server and finally move the data (by using linked server)? >> >> "Markgoldin" wrote in message >> >>>I was using Data Inport Wizard. >>> All I wanted is to copy all tables from one database to another SQL >>> Server into the same database. >>> By copy I mean data has to be intact. Exactly the same. >>> >>> "Uri Dimant" wrote in message >>> >>>> Mark >>>> How do you copy? Linked Server? >>>> >>>> >>>> "Markgoldin" wrote in message >>>> >>>>>I did not find copy tables from one Server to another is a simple task. >>>>> First, no indexes were copied and Identiy columns are not moving >>>>> correctly either. >>>>> >>>>> >>>>> "Jay" wrote in message >>>>> >>>>>> "Uri Dimant" wrote in message >>>>>> >>>>>>> Mark >>>>>>> If the task you are going to perform frequently, I would consider >>>>>>> www.red-gate.com tool . >>>>>>> I would go with SSIS pakage >>>>>> >>>>>> You would recommend a 3rd party tool AND use a SSIS package? >>>>>> To simple copy tables from one instance to another? >>>>>> >>>>>> >>>>>> >>>>>>> >>>>>>> "Markgoldin" wrote in message >>>>>>> >>>>>>>>I have two SQL servers: Source is a 2005 server and a Target is >>>>>>>>2008. >>>>>>>> I need to copy tables (tables only) from 2005 to 2008. All my 2005 >>>>>>>> tables have Identity column on. >>>>>>>> >>>>>>>> What is a best way of doing that? >>>>>>>> >>>>>>>> Thanks >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > >> Stay informed about: Import data

Display posts from previous:

Related Topics:

Import of XML data. - Dear all, I have been asked to look into the following. Data is delivered in XML format. (Assume wel formed XML and the stylesheet is present). This data has to be imported in the database. What are the possibilities in 2005? What are the possibilitie...

Import data issue - Hi all, Here is my scenario. I have a source server (SRV1) with the database (BaseLineDB) that has about 20 tables. I have to create a package (or a stored procedure) that can copy data from those 20 tables into a destination server. A destination..

How can I import CSV or Excel data into SQL2005 Express ? - How can I import CSV or Excel data into SQL2005 Express ? Thanks.

How to skip a line when import - I'm trying to import a text file to sql, but sql stop at a certain point. I know the problem record number and I remember that bulk insert could define the start and end line of the importing data. Is that possible to skip this bad line and import..

Import DB Watcom - Someone knows as it is possible to read the data contained in a Db Watcom 4.0 in order to import it in a Db Sql? Thanks from Italy Carlo

	Database Help (Home) -> Server	All times are: Pacific Time (US & Canada)
Page 1 of 1

You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum