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'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.
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?
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..
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
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.
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.
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.
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?
Hi: At the moment, I know how to copy a db to another (create a .bak file), but I am not sure what is the best way to copy one the table (with it data) from a db to another.Would u please give some links or suggestions?Thanks.jt
i have a table called t_CustomerAcct in 'Dev' db and want to copy the data in t_CustomerAcct table in 'Production' db. but i have some records in the table in 'production' db which i dont want to be updated. my primary key in both tables is 'email' i bit lost on how to do this and i dont want to loose any data from production db accidentally.
hi. i'm trying to copy data from one table to another. the table has already been created but i just want to update some of the information in it.
here is what i have:
INSERT INTO DefendantCaseBAK
SELECT * FROM DefendantCase WHERE DefendantCase.StatusID=2 when i run this query, i get this error: The column prefix 'DefendantCaseBAK' does not match with a table name or alias name used in the query. what am i doing wrong? i looked up some examples on the internet and i copied them exactly. thanks for your help!
I need to move data from a large table (350GB/395,000 rows) to another table within the DB, but onto another disk. The table has an image data type so I have to create a new table onto the new disk. My original table looks like this:
USE [PD51_Data] GO /****** Object: Table [dbo].[SCANNEDDOCUMENTS] Script Date: 05/27/2008 11:26:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SCANNEDDOCUMENTS]( [DocID] [int] IDENTITY(1,1) NOT NULL, [CaseID] [int] NOT NULL, [DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Doc] [image] NOT NULL, [DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TopicID] [int] NULL, [ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, PRIMARY KEY CLUSTERED ( [DocID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[SCANNEDDOCUMENTS] WITH NOCHECK ADD CONSTRAINT [ISCANNEDDOCUMENTS2] FOREIGN KEY([TopicID]) REFERENCES [dbo].[TOPICS] ([TopicID]) GO ALTER TABLE [dbo].[SCANNEDDOCUMENTS] CHECK CONSTRAINT [ISCANNEDDOCUMENTS]
My new table looks like this: USE [PD51_Data] GO /****** Object: Table [dbo].[SCANNEDDOCUMENTS2] Script Date: 05/27/2008 11:27:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SCANNEDDOCUMENTS2]( [DocID] [int] IDENTITY(1,1) NOT NULL, [CaseID] [int] NOT NULL, [DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Doc] [image] NOT NULL, [DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TopicID] [int] NULL, [ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [SECONDARY] TEXTIMAGE_ON [SECONDARY]
GO SET ANSI_PADDING OFF
My plan was to use import/export wizard to move the data over to the new table, while set to Bulk Log recovery, drop the original table, then create the indexes/constraints on the new one. With this much data, I'm wondering what else I should do. Anyone have a better idea? What am I missing?
There are some data in a table with TypeID=23 and I need copy all rows with TypeID=23 to the same table, I mean append at the end, and make the copied rows TypeId=24 for some tests purpose.
Write a query that will grab bin# from table A and if its missing on table B copy it. I tried and update query but it doesn't work and also a select Into and failed too.
I want to move or copy a row of data from one table to another, but not sure how to go about it! I have SQL 2000, and using Enterprise Mangager with SQL query to run the script. This will be used in a procedure once I get the Query to work.
Hi All I have two tables one is old and another new table. I need to copy the data from old to new table. using stored procedure. How can I do the same. Please help me.
I have a table that I need to move all data to a secondary data file. I have to copy all data from the table to another table to make this work since I have an image data type. My initial table is set up like this:
USE [PD51_Data] GO /****** Object: Table [dbo].[SCANNEDDOCUMENTS] Script Date: 05/27/2008 11:26:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SCANNEDDOCUMENTS]( [DocID] [int] IDENTITY(1,1) NOT NULL, [CaseID] [int] NOT NULL, [DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Doc] [image] NOT NULL, [DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TopicID] [int] NULL, [ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, PRIMARY KEY CLUSTERED ( [DocID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[SCANNEDDOCUMENTS] WITH NOCHECK ADD CONSTRAINT [ISCANNEDDOCUMENTS2] FOREIGN KEY([TopicID]) REFERENCES [dbo].[TOPICS] ([TopicID]) GO ALTER TABLE [dbo].[SCANNEDDOCUMENTS] CHECK CONSTRAINT [ISCANNEDDOCUMENTS2]
My new Table is set up like this: USE [PD51_Data] GO /****** Object: Table [dbo].[SCANNEDDOCUMENTS2] Script Date: 05/27/2008 11:27:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SCANNEDDOCUMENTS2]( [DocID] [int] IDENTITY(1,1) NOT NULL, [CaseID] [int] NOT NULL, [DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Doc] [image] NOT NULL, [DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TopicID] [int] NULL, [ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [SECONDARY] TEXTIMAGE_ON [SECONDARY]
GO SET ANSI_PADDING OFF My plan is, once I move or copy the data to the new table, I will then drop the original table, rename new table to the original, create the indexes and FKs. What do you think would be the best way to move the data to the new table?
On the OLE DB Destination, I used fast load option with table lock checked, and check constraints to copy data to table_destination. Both table_source and table_destination have the same table definitions. After the dtsx package ran, the number of rows copy over to the destination is not same. I got different results from different runs. This only happens when the source table is over 1 million rows and when I'm copying from the transaction database where user's activities are heavy.
Any ideas how I can modify the package so it can copy correct data? Thanks!
Hi, All, I have agentID in product table. Now I add agentID column in transaction table. Now I want to copy all agentID from product table to transaction table based on the order_id in both table. Can you show me an example? Thanks Betty
We need to get the data from our third party CRM vendor. CRM vendor will provide us with .txt file on their folder each night and we need to get those data into our database. I know that SSIS should be able to do this for us. Is there any tutorial of how to do this job in SQL Server 2005? Thanks in advance.
This requirement is for our development and test environment. We have a database server which we use for development hence many changes are done on the database objects on daily basis like tables def changes, view def changes, sp changes, new sp etc.
On the other hand our testing team connects to another database server where they perform testing hence any internal test release would need the changes like tables def changes, view def changes, sp changes, new sp etc.
Since testing team already have their test data set in the test environment hence they want to keep their data but new test release should only provide with datbase object changes. The difficult part is, we have over 500 tables and about 1000 views and somewhere about 400 stored procs, so its not possible to manually perform the above task.
I dont do database admin job hence clueless as what would be the best way to accomplish the above. Pl advice.
we are using sql server 2005 on both test and dev environemts.
Hi all, We have a website with a database connected to a SQL server. I need to copy that database without the data onto a CD so I can place the program on another server?
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?
