Hi AllWonder if you could help, I have a bog standard table called STOCKPRICESthat has served me well for a while, but now I need to change the structureof it and because a number of users have used it in it's present form I needto so the following in SQL script:a) Grab a snapshot of the current SQL data.b) Re-structure the STOCKPRICES table.c) Post this grabbed data back, but in the new format.My script plan was to firstly to rename the current STOCKPRICES table toSTOCKPRICESOLD (you can do this can't you), create a new STOCKPRICES tablein the new format and then somehow extract the data from STOCKPRICESOLD andsquirt it into STOCKPRICES.The current schema for STOCKPRICES is as follows:# --------------------------------------------------# Table structure for table 'STOCKPRICES'# --------------------------------------------------DROP TABLE IF EXISTS `STOCKPRICES`;CREATE TABLE `STOCKPRICES` (`STOCKID` VARCHAR(30),`CURRENCYID` VARCHAR(30),`HDNETAMOUNT` DECIMAL(10,3) DEFAULT 0,`HDTAXAMOUNT` DECIMAL(10,3) DEFAULT 0,`RRPNETAMOUNT` DECIMAL(10,3) DEFAULT 0,`RRPTAXAMOUNT` DECIMAL(10,3) DEFAULT 0,`NETAMOUNT` DECIMAL(10,3) DEFAULT 0,`TAXAMOUNT` DECIMAL(10,3) DEFAULT 0,INDEX `indxCUURENCYID` (`CURRENCYID`),INDEX `indxSTOCKID` (`STOCKID`));Like I said it's very basic.My new table wants to be like the following:# --------------------------------------------------# Table structure for NEW table 'STOCKPRICES'# --------------------------------------------------DROP TABLE IF EXISTS `STOCKPRICES`;CREATE TABLE `STOCKPRICES` (`STOCKID` VARCHAR(30),`CURRENCYID` VARCHAR(30),`PRICELEVELID` VARCHAR(30),`NETAMOUNT` DECIMAL(10,3) DEFAULT 0,`TAXAMOUNT` DECIMAL(10,3) DEFAULT 0,INDEX `indxPRICELEVELID` (`PRICELEVELID`),INDEX `indxCUURENCYID` (`CURRENCYID`),INDEX `indxSTOCKID` (`STOCKID`));The new re-structure means that PRICELEVELID will include a unique referenceto the HD, RRP, standard prices (plus 3 others that I'm going to create).I know this probably very simple data architecture to you guys, but I'm sureyou can appreciate why I need to change the structure to this method so thatI'm not creating redundant data fields if the user only enters a standardprice I won't be storing nothing for the 2 x HD and 2 x RRP price fields.I don't think I've got a problem renaming the old one and re-creating thenew one, but how do I get the data from one to another?My problem is that I have:code, currency, hdnet, hdtax, rrpnet, rrptax, net, taxIVP GBP 2.00 0.35 200.00 35.00 100.00 17.50etc...and I need to get it into the format:code, currency, pricelevelid, net, taxIVP GBP hd 2.00 0.35IVP GBP rrp 200.00 35.00IVP GBP standard 100.00 17.50etc...Any ideas?RgdsLaphan
Hi !I know how to save a DTS as a structured storage file and how torecreate a DTS using that file.What I can't find is a command, either in t-sql or from a DOS level,with which I can save the DTS as a structured storage file, for examplein a scheduled job for backup purposes.Hopefully some out there has an answer !!!ThanksDavid Greenberg*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
My Requirement is Update Table 1 set Column::No=Table 2.ID
based on Exact Match of
Table1.Name=Table2.Name and
Table1.Add=Table2.Add
It means Get back the Id for Source Table 1
2nd Data flow Source(Table1:Name, Add,No) |
--LOOKUP(Table2:Name, Add::Matched Look Columns Name, Add and Tick Mark on ID) |(Match)
-->OLEDB Command: update Table1 set N0=? where RowID=?(Here Param_0= NO ,Param_1=RowID)
Here My Issue is if Table 1 had Duplicates(same Name, Add, but Row Id is different it is Updating Same ID for Table 1.No It means Get Back ID correctly not updating Result::
Table 1:
------- ----- ---- ---- Name Add No RowID ------- ----- ---- ------- aa #a-1,India 1 10 bb #a-1,India 2 11 aa #a-1,India 1 12
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 am going to use the backup and restore function to copy data from one server to the other server. We would like to keep the servers in sink at this point (not instantaneously but update the server say once a day) and I would like to do this by using the back up transaction logs. I have tried to back up from individual transaction logs but in also seems to restore the full database also. The database is roughly 6 gig and transaction logs are about 25- 50 meg. I really do not want to have to restore the database every time.
I know I could set up replication but this has been more of a pain administering this on a daily basis. I would like to do a schedule and forget type of thing. This is going to be done on 6.5.
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 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
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?
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 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.
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.
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?
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.
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..
