I want to make an exact copy of a production database table in our development database.
1. Script the production table using Script Table as Create
2. Drop the existing development table
3. Use the Script (see 1. above) to recreate the development table (substituting the proper database name)
4. Insert all records from the production table into the development table (insert into table development_table select * from production_table)
Does this create an exact duplicate including the proper sequencing of identity fields? Meaning if there are deleted records in the production database table, their identity fields would be numbered something like 1, 2, 3, 5, 6, 9, 11, etc. I want to make sure that the identity fields are numbered exactly the same in the development table.
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing: dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Regions dbo.Shippers dbo.Suppliers dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing: dbo.Categories New Table... Modify Open Table Script Table as |> CREATYE To |> DROP To |> SELECT To |> INSERT To |> New Query Editor Window File.... Clipboard UPDATE To |> DELETE to |> From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance, Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated?
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
Hello, I have a table that I would like to copy into a different database. I'm using SQL Server 2005. The reason I don't just copy the data with "SELECT * FROM..." is because the desired table doesn't exist yet. It has ~50 columns and I'd rather not recreate them all (I didn't design it). Thanks.
this maybe a simple question but is it possible to copy a table from one sqlmobile database to another?
here is what i would like to be able to do although i am aware that it is not necessarily the most efficient way
- for various reasons this is how i have to do it:
A copy of a sqlmobile2005 database will sit on an SD Card on a pocket pc - DatabaseA.
A second database - DatabaseB - will then be sent as an attachment via email and pasted onto the sdcard
The information in DatabaseB is an update for DatabaseA.
At least 2 of the tables in DatabaseA need emptying and refilling with the data from matching tables in DatabaseB.
I'm guessing the easy way to to this would be a parameterized insert statement but unfortunately these tables contain approx. 900 records and i fear this would be a long process.
Any helpwith this matter would be much appreciated
I have a table most columns of it (and there are many) are useful for another table in the same database. how can I copy the table to a new name table?
Hello. I need to copy all of the rows in a table from a database on one server, to another existing table of the same name in a different database on a different server. I'm trying to use a SELECT INTO statement. Any idea how to do this?I've tried SELECT * INTO DestinationServer.dbo.DestinationDB.DestinationTableFROM SourceTable AS SourceTable_1 But this doesn't work, saying there are too many prefixes. Any idea how to do this?
Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.
However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.
An example table.
The following is the script created by SQL Management Studio if I right click and script the table as CREATE. CREATE TABLE [dbo].[tbPRO_Package]( [PRO_PackageID] [int] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()), [CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()), [ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()), [GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()), CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED ( [PRO_PackageID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
Note that the field PRO_PackageID has an identity, and is a primary key.
Also note the constraints on several fields and the default values.
If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script
CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] ([PRO_PackageID] int NOT NULL,[PackageCode] varchar(8) NOT NULL,[LKU_VatRateCode] nchar(10),[CancellationCharge] decimal(18,2),[CancellationMultiplier] decimal(18,2),[CreatedDate] datetime NOT NULL,[CreatedBy] varchar(50) NOT NULL,[ModifiedDate] datetime NOT NULL,[ModifiedBy] varchar(50) NOT NULL,[GUID] uniqueidentifier NOT NULL)
We've lost the identity, the PK,the constraints and the defaults.
If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.
Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?
I created one Database in my server,n i wanted the same database in my local computer.So i copied it and restored in my database. Now i created another one new table in my server. I want to copy that table into my computer. How can i copy a single table in database and how can i restore(paste) that into my existing databse of my local compuer.
I'm trying to copy a table from an odbc db to an sqlserver2005 db. SSIS will not do this for me and says I must write a script. I'm not experienced enough in sqlserver to do this and was unable to find a example of this by searching the web. Does anyone know of any websites with an explanation of how to do this?
Hi y'all, I am doing some searching in the archived threads, but I have a need to copy a table in a database to a new table in the same database, but the new table will be just a table with test data. There are several million rows in the table and I want to do the copy without logging the new inserts in the transaction log.
Is there an easy way to do this? I found this in my search efforts so far, but am just wondering if there is an easier/better way to accomplish what I want to do.
BTW, I normally wouldn't care, but the boss is complaining that it is taking too long to do the copy for a different team, so asked if I knew a way to copy data to a new table without logging. I don't, so here I am ;)
Here is what I found so far:Following 3 things need to be done 1) create table as not logged initially 2) set autocommit=off 3) and activate the not logged initially option Now the inserts happen without the use of transaction logs
I want to know how to copy tables and data from one database to antoher database including table permissions. Presently i am using Integrity security services. Is it having any option in Integration services or sqlserver 2005.
We copy a table from a progress database into our sql server database that we use as a read-only table. We perform this action through a job. Is there a way to have a read only table from another database that is put into your database that will always be insinc with the original copy to use as a read only copy, that as the original table is updated, it will proprogate to your copied table?
I'm struggeling for more than a week now with this problem, without a finding the solution.
I have two databases, MS Access and SQL Server 2005 Express Edition
Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)
I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...
Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?
Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).
the procedure goes like this;
' Create a connection to the MS Access Database Dim connectionToAccess As New OleDbConnection(DBConnectionAccString) strsql = "SELECT * FROM [TABELLA]" connectionToAccess.Open() Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)
Dim DsAccess As New DataSet("ACCESS") DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella") DaAccess.Fill(DsAccess, "Tabella")
' Create a connection to the SQL Database Dim connectionToSQL As New SqlConnection(DBConnectionSQLString) connectionToSQL.Open() Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)
Dim DsSQL As New DataSet("SQL") DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella") DaSQL.Fill(DsAccess, "Tabella")
I'm new to SQL Server 2005 SSIS. I'm trying to do something very simple, but I cannot figure it out, PLEASE HELP!
I have a flat file, which I read and then insert the data in a database table, that works fine. The problem is that I don't want to insert duplicate records. For example; if I run the package again, it will appent to the table. What I need to do is that if the package runs again, check if the record already exist, based one two columns, date and hour, and do not insert the record.
I am using Bulk Copy command for Exporting data table wise from database to csv files and it was working fine. Since last 3-4 days when exporting for some tables data in csv file is coming junk.
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest Set Partner = N'TCP://SQL2.mycom.com:5022'; go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
I've been assigned the task of setting up access to our SQL Server 2005 box. A consultant developing for us has accessing to 2 databases and I've set this up fine. It appears however that one of these databases is re-copied over to the server every night to keep data reasonably current.
I'm not interesting in changing this method as I'm not the maintainer (as yet).
Basically I would like to know if I've setup access to this database (it works fine), when the database is updated (with an SSIS package) the account seems to get deleted. Do the original permissions from the source database overwrite those of its destination?
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
I'm using SQL Server Management Studio Express and I'm trying to figure out how to copy a table(s) from my local database to my web hosting database. I know how to do it in 2000, but it's completely different now. Is this feature not allowed on SSMSE? If so, then how do I deploy database tables to a web host?Also, how do you add local database(s) to SSMSE? I tried to use 'attach database' in SSMSE and it wouldn't allow me to navigate to My Documents folder where the database resides. Thanks...
If I want to copy the data from Table1 in Database A to Table2 in Database B but Table1 column name is code , Table 2 column name is vesselcode. (Code = vesselcode)
How to copy all data from Table1 in Database A to Table2 in Database B ? Do I need to write the SQL statment ? and Can I use Server Enterprise Manager Tool?Thx a lot.
Hi, I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem 1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right? 2) these two table have One Prinary Key column with IDENTITY feature. Any Help Greatly appriciated.Regards.
Hi i have set up two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile table with Id set as the Primery Key as Identity) I want the user to be able to be able to return to their account at a later date and then post multiple reviews of different bands they have seen at a later date. I kept the tables in my example very simple so I could get my head around the concept, but generally, I want to connect the Id (PK) value in User_Profile table to the User_Id filed in the User_Review table, so every review that user writes, will be connected directly to their Id.
Any help you could give would be fantastic a i have no idea where to start!!!
User_Profile
Id int, ( as primary Identity Key)
Name
City
Country
I have a second table called User Reviews
User_Revews
Revew_Id int , ( as primary Identity Key)
User_Id int, ( I want this to contain the Id value in the User profile Table)
