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
Query to copy column data from one table and put it in another???
View 6 Replies View RelatedI 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 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.
I have a column of digits I'd like to copy into another column in thesame table. How would I do this?Thanks,Bill
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 RelatedHow do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies View RelatedHi 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
I need to copy existing row data from one table into a new row in a different table, both in the same database. Can this be done in a stored procedure where the selected row is passed in as parameter value?
Thank you,
Hi,
I need to recover a table.
I have restored completed database to a temp DB.
I do know,how to use export and import.
Any one can help me?
Regards,
Vinoth
Hi.Like the title says - how do i do this?I was given the following example:INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'The above statement threw the following error:An explicit value for the identity column in table 'TABLE2' can onlybe specified when a column list is used and IDENTITY_INSERT is ON.Then, after filling in all the column names in my above selectstatement I kept getting an error to the effect that the number ofsource and destination columns don't match. This is because one column"confirm_hash" does not exist in the destination table, just thesource table.could somebody show me how to get this to work?thanks!PS - MS SQL SERVER EXPRESS 2005
View 10 Replies View RelatedHi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
Hi all,
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.
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
[MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
Hi,
How can I copy a database table with all its data, indexes and constraints to a new table in the same database in sql server 2005
I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?
View 2 Replies View RelatedI thought I saw this done once before. So today I hunted around inBooks OnLine and did a Google search. So far I have found nothingclose. So if you know how to do it, please tell me or if cannot bedone, I'd appreciate know that too.Thanks in advance,IanO
View 2 Replies View RelatedHi: 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!
hello all,
I don't know how to update table A with data from table B without specifying every column.
These two tables have the same fields and same structure.
I know that it's possible to do the following:
update table A
set A.name = B.name,
A.job = B.job
from table B
But I have many columns and don't want to describe every column, is that possible?
Thanks!
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????
Let'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 Related