I have different versions of essentially the same database on two different computers. From Computer B I need to copy one table AND its data to Computer A.
I've read several articles and have tried several things but can't figure out how to do it. Incidentally, I'm running SQL Server Express 2005 on both machines.
Robert Werner
Vancouver, BC
I want to create a clean copy of my DB now that it is done. So it can be moved to another Server. It has some sample data in some tables that I would like to keep and some in other table that I don't. How can I do this?
View 1 Replies View RelatedThe only way the job success is if I select the option add rows wich is copying all rows even the ones that are already there, I tried to drop amd recreate the table option but i just dosn't run that way, help please..
View 1 Replies View Relatedwhich ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?
View 2 Replies View RelatedI want to drop table and then recreate. It's referenced by many table and I dont want to drop all constraints referencing to it. Is there any feature like "switch off/on constraints" in MSSQL?(6.5)
View 1 Replies View RelatedJust wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies View RelatedI'm having a heck of a time trying to upload data to an excel spreadsheet. This works perfectly in sql 2000 but I've been having problems with 2005
SSIS package "Package1.dtsx" starting.
Error: 0xC002F210 at Drop table(s) SQL Task, Execute SQL Task: Executing the query "drop table `GRE`
" failed with the following error: "Table 'xxx' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Drop table(s) SQL Task
Error: 0xC002F210 at Preparation SQL Task, Execute SQL Task: Executing the query "CREATE TABLE `xxx` (
`TEST_REC_NBR` Decimal(29,0),
`PROCESS_DT_GRE` LongText
)
" failed with the following error: "Invalid precision for decimal data type.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Preparation SQL Task
SSIS package "Package1.dtsx" finished: Failure.
Hi All,
I have 2 tables People & PeopleCosts.
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..
Kindly help. thanks in advance.
Myl
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
View 4 Replies View Relatedi 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.
View 3 Replies View RelatedI have a table Product BasicInfo, which has following fields:
ProdID (PK)
ProdName
Description
Manufacturer
Store
ProdPrice
Now i have another table TempPrice, having fields:
TempID (PK)
ProdID (FK)
TempProdPrice
TempPrice table has the original values, which are needed to copy into ProductBasicInfo column "ProdPrice" (which is entire NULL column so far).
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.
Table_A
idx Bin
1 CSR010101A
2 CSR010101B
3 CSR010102A
4 CSR010102B
5 CSR010201A
6 CSR010201B
7 CSR010202A
8 CSR010202B
Table_B
idx Bin
1 CSR010101A
2 CSR010101B
3 Null
4 Null
5 CSR010201A
6 Null
7 CSR010202A
8 NUll
Hello,
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.
Thank you,
Ernie
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.
Thanks
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 Source, I have as following:
select field1, field2, field3 from table_source
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 have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data?
I can't find any option in the Import Data Wizard that only copies the Table structure.
Any help is appreciated. Thanks
This is probably a dumb question.. but here goes.
I'd like to make a copy of several tables before changes are made to them so that I can 'roll back' if necessary.
What I 'thought' i could to was this:
select * into mytable_temp from mytable
and then to roll back..
truncate table mytable
Select * into mytable from mytable_temp
When I try and select back into my original table, it says I can't because the object already exists.. What is a better way to accomplish this????
Query to copy column data from one table and put it in another???
View 6 Replies View RelatedLet's say, I have Table1 and Table2, both with the exactly samestructure and data in it, except that Table1.Field1 is empty in databut Table2.Field1 have data in it.How could I copy all the data from Table2.Field1 to Table1.Field1?Is there any simpler way except looping through all the records to doit?
View 5 Replies View RelatedI am working on a migration project.
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?
Hi,
I was wondering if you can help.
In my vb.net form I am running a query to insert data from one database table to another.
However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.
I have managed to use @@ identity to get the id of my first sql insert statement
but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @@identity value to my second sql statement.
My second sql statement is a follows:
sql2 = "INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" & " select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId
I hope you can help
Cheers
Mark :)
I have a table called 'Articles' whose columns are articleId (int), authorName (varchar) and article (binary). The primary key is articleId. I tried using DTS wizard available in Enterprise Manager 2000, to import the data from this table into a remote database table, but the data copy failed. I am thinking the binary column is causing the DTS data copy to fail.
If someone could tell me what else I could do to transfer binary data to remote database(like an example of a data transfer query), then that would help me greatly.
Hi,
I've a column col1 of image data type in table1. I would like to copy the data from col1 to another image column col2 in table2. Before moving the value, checking has to be done to specify which col1 data from table1 is needed and the destination has to be checked too.
Example: insert into col2
(select col1 from table1 where table1_id =5)
where table2_id =6
Hence bcp wouldn't work. Can anyone suggest me a way to do it. I tried using writetext but then, i've to get data from col1 in a variable, which is not possible. Any suggestions would be very helpful.
Thanks in advance.
Ramya.
I am trying to insert data from staging table to production table. In the staging table I only have period or date but no primary key.
This is my staging table
Create stagingtable(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
Create Production(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
I get this every month. What can I do to make sure only unique record are loaded into production table with no duplicate from previous month.
I have a database that I have been creating and testing. I have added some junk data and some data into lookup tables. Is there a way to create a clean copy of the db and keep the lookup table data? Also will I be able to create the db under a new name?
View 1 Replies View RelatedHi,
I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values.
I tried using bcp Command as follows..
bcp "select colA,colB,colC,colD from A" queryout "c: empA.dat" -t" " -r"" -c
I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know.
This is very critical. Appreciate your help.
Thanks,
Ramya.
Hi there,
Can u please tell me how to copy data from table A(database A) to table B(databaseB) which table A contain 10 fields but table B consist of 11 fields. I have to insert current date and time into another field in Table B (which has extra field compare to tableA) automatically every hour or so.
Please help.
Thanx
how to copy a content of a table from one remote server to another,. server A does not see server B (B doesn't see A) - I cannot even ping to one from another.I do have SQL Studio installed on server C, which IT team configured to allow access to both A and B.So what I did so far is to periodically:
1. connect from the studio on server C to server A
2. run the following script on server A: SELECT * FROM A.myTable FOR XML PATH('ROOT')
3. copy the result
4. connect from the studio on server C to server B
5. to write something like
DECLARE @xmlData XML;
SET @xmlData = pasting here my result from item 3 above
6. INSERT INTO
SELECT
ref.value .....
FROM @xmlData.nodes('/myElemnet/ROOT')
xmlData( ref );
so it works. now there is a requirement to schedule this update to run periodically and I need to implement it..