Copying Columns To Table With Different Design
Jun 17, 2006
I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design. I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data. Here are the two tables involved in the column copy:
Documents Table
DocID (Primary Key)
DocNo
SequenceNo
YearGiven
MonthGiven
DayGiven
FullDocuments Table
FullDocID (Primary Key)
DocNo
SequenceNo
SectionText
YearGiven (empty €“ no data)
MonthGiven (empty €“ no data)
DayGiven (empty €“ no data)
After the copy column procedure I want the FullDocuments table structure to look like this:
FullDocuments Table
FullDocID (Primary Key)
DocNo
SequenceNo
SectionText
YearGiven (full of transferred data)
MonthGiven (full of transferred data)
DayGiven (full of transferred data)
The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table. This is because each document has four types €“ the primary Text document and three supplemental documents (Background, Report, and Index). Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types). Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns. Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).
I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables). This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization.
I tried exporting the data using the Export Wizard using the €śCopy data from one or more tables€? option. The transfer failed. The most relevant lines of the Error Report are:
·Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)
·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
·Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
(SQL Server Import and Export Wizard)
I then tried the €śWrite a query to specify the data to transfer€? option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck. Questions:
1. Is the query approach the best way to achieve my objective, or did I miss something in the €śCopy data€? option in the Export Wizard?
2. If the query approach is the best way, any suggestions on what the query will look like?
Thanks for any help you can provide.
View 5 Replies
ADVERTISEMENT
Dec 30, 2007
Hello to all,
this is my first post on this forum, and I hope you'll be able to solve my problem. I'm working in industrial field and only from time to time I have to work with SQL. My problem is the following:
I have two tables. Both tables have same number of rows but different number of columns. Table 1 has 5, and table2 has three columns.
I need SQL statement to copy two columns A and B from table 1 to table 2 with condition, for example table2.C <> 2?
I don't know if I was clear enough. Basically I need to copy data from two columns of one table to other. Tables have no common data which I can use to join tables.
I tried something like this:
update table2 set (A,B)=(select A,B from table 1) where table2.c <>2
but don't know how to use syntax properly...
Thanks in advance
View 18 Replies
View Related
Jan 24, 2008
Hi
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 3:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
Your help is greatly appreciated.
Thanks
View 3 Replies
View Related
Jan 24, 2008
Hi
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 4:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.
Your help is greatly appreciated.
Thanks
View 4 Replies
View Related
Jul 8, 2013
I have a rather complex query (to me at least) that I need to create but I am unsure of where to start. The query requires me to copy existing data into a new row (which will then create a new ID) as well as update all existing records with the newly created id. More specifically, I need to separate the data associated with LocationID 219 from it's parent, CompanyID 992.
Ideally I want to copy the data associated with LocationId 219 and then make a new CompanyId with the copied data (which will also create a new LocationID). Since this new record is no longer going to be associated with CompanyID 992 I will want to remove/delete/drop it from that record.
Finally, and perhaps most difficult of all, I need to update all tables that reference the old ID's together (992 / 219) to reflect the newly created Company ID and Location ID.
View 3 Replies
View Related
Dec 13, 2007
I need to copy all the data from all the tables in a database to a copy of this database on another server.
What feature of SSIS should I take advantage of to accomplish this?
We have an SLA for 8am, most times the data warehousing jobs complete at 8:05am. Adding an additional process/set of tasks to this package would obviously make matters so I'm trying to update/copy/replicate the data in the fastest manner. Typically we're talking 2 marts (10-20GB) with 2 large tables (5-10 mill records) and 20 marts (0.5 - 5 GB) with many more smaller tables (~40 tables with record count ranging from 1 to a million)
Additionally please indicate if the design/feature you suggest can handle (pushing schema changes and additions to the target server) schema changes or new tablesviews added to the source database.
My only idea so far...is using the import wizard (in Management Studio) to create an SSIS package (top copy all the tables from one server to another) and saving it to the server, Then executing this package after the job is complete. However this would not work if the schema of a table changed, or if a a table is added. Moreover I don't think I can edit this package in visual studio.
View 3 Replies
View Related
Dec 29, 2006
I know this is no problem for anyone who has ever done a bit of programming in SQL, but for me it is a problem because I'm rather new in SQL. My question is: how to copy column in the same table in query? I need move one column to another and then to delete the first column (but not the whole column, just one part so I can't use Copy - Paste in Enterprise Manager). I know how to select the part that I want, all I need now is to copy columns.
Thank You all in advanced!
Sincerely,
Ivan
View 4 Replies
View Related
Jan 18, 2007
I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:
DECLARE @pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @pss OUTPUT
SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator
Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.
View 5 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Sep 23, 2015
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.
View 7 Replies
View Related
Feb 15, 2005
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
any ideas on how i could do this easily?
thanks
View 1 Replies
View Related
Aug 23, 2005
Normally when I have a "Many-toMany" or linkage table where the primary key consists of a foreign key from two different tables, I do not bother to make a separate identiy column instead.Does anyone see a reason why an identity column would be more or less desireable ? For exampleTable Person PK - PersonIDTable Car PK - CarIDTable PersonCar PK (PersonID, CarID)Or would it be better to make an Identity Column such as PersonCarID so then the table would look like the following:Table PersonCarPK - (PersonCarID)FK - PersonIDFK - CarIDCreate Unique Constraint on Person and CarIDAny feedback is appreciated
View 46 Replies
View Related
Nov 3, 2006
Even though I select "Column Names" in Design View when creating a query (or view), only "* (All Columns)" appears in the table box.
In InfoPath, when I connect a combo-box, err drop down box, to the database, I am unable to connect directly to a table... no tables are shown. If I select a different database, these problems do not exist.
I can not find any setting to allow these columns to be shown in the design view or any setting that will "expose" the tables in InfoPath.
I tried creating a new database and exporting the data, tables and data, from the troubled DB to the new DB; however, the new DB exhibited the same behaviour. The system tables, Master and Model, have the same behaviour. Please help me with your ideas and suggestions... thank you very much for your time.
This database was upsized from Access 2003 to SQL Server 2000 SP4.
rogge
View 7 Replies
View Related
Jun 9, 2015
I would like to alter view to add columns. My base table is populated by application and the base table name is always change. Is there any way to add some extra columns to this view.
One way is to create a sp to add columns and use the view inside the sp. But I would like to know is there any way to alter the view.Â
View 4 Replies
View Related
Jun 3, 2015
I am adding a table within my vb.net program using New datatable(tblname) function, then adding 22 columns (col01 to col22) to this table using .columns.add (colname) function without any error.The program however throws an exception when trying to assign a value to column number 14 (col14) saying this column does not belong to table tblname. Assigning a value from col01 to col13 is working fine.Is there a limitation on number of columns can be added to a table using code?
View 2 Replies
View Related
Apr 23, 2015
I have two columns "Project Name" and "Flag".Â
Now i want to find out all the project names which contain a particular substring in it, like say - "SRM" and update the "Flag" column to value 1 for all those projects.
View 15 Replies
View Related
Aug 18, 2015
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. Â How to design the table..Â
Product name  : Â
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |
CTN, NOs
Price:
50,20,5 |
40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos Â
| CTN=8 Nos
View 3 Replies
View Related
Nov 23, 2007
Hello guys..
Can u plz help me by giving me an idea how i can copy the temp table data to permanent table
Thanks,
sohails
View 1 Replies
View Related
Feb 9, 2006
Hello,
Probabaly a silly question yet as a DOTNET developer, I'm trying to simulate DTS when for example, I don't have permission to perform DTS on a production server.
In particular and interested regards caching of rows before the service decides to flush the buffer and write to the target table. Safe to assume DTS is cursor based?
View 1 Replies
View Related
Mar 6, 2015
I have a ipaddress column is there where i need to split the column into two columns because of values like below
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
I have written the below query but it will throuh some error.
 select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0Â
  from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
View 16 Replies
View Related
Aug 25, 2015
We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table. Â This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.
For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables? Â While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.
View 4 Replies
View Related
Nov 4, 2015
I am trying to Import data into SQL server 2008 using management studio. The source data is an Access dbase. I am trying to do this with queries as the tables do not match but I do need to copy specific columns for the source to the destination. Any brief example selecting a column from the source table, and just entering a dummy value for other columns(other column of data for destination table does not exist in source table).Â
For the example
Source access dbase, just two columns but no primary key, T2dbase, Employee
New table.
First Name, Description
 Tom         , manager
Destination dbase SQLServer (T1dbase,Employee table), note 4 columns
Primary key NameID, FirstName nvarchar (20), LastName nvarchar (20), Description nvarchar(20)
View 5 Replies
View Related
Jul 17, 2015
I'm trying to run the following sql
SELECT DISTINCT
Anvendelseskoder.[Usage Code] AS [Building Code],
Anvendelseskoder.[Usage Code Value] AS [Building Description],
HeleDanmark_DAWA.KVHx
FROM Anvendelseskoder
RIGHT JOIN HeleDanmark_DAWA
[Code] ...
It gives me the following error:
Msg 4104, Level 16, State 1, Line 26
The multi-part identifier "aa.KVHx" could not be bound.
When clicked, it marks the first time i call "aa.KVHx"Â
Which I do in: "WHERE S2.Nr=2 AND s2.KVHx=aa.KVHx) AS Kode2,"
View 2 Replies
View Related
Feb 22, 2006
I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB: CompanyID, FacilityID, EmployeeID, FirstName, LastName, [Password] = 'nippert', Role = 'Employee' I tried the advice on this website but to no avail:http://www.w3schools.com/sql/sql_select_into.asp
View 1 Replies
View Related
Jul 20, 2005
I need to update a table by copying a column from another table(having the samestructure, but on another database), from the record having the sameprimary key.1 - What is the correct query?2 - I tried copying them record by record, but the datatype is ntext,(it displays <longtext> in the result pane), and trying to update it results in thefollowing errormessage:The text, ntext, and image data types are invalid in this subquery oraggregateexpression.I tried variations of the following:UPDATE TABLESET column0 = (SELECTcolumn0FROManotherDB.dbo.TABLEWHEREanotherDB.dbo.TABLE.column1 = column1)WHEREanotherDB.dbo.TABLE.column1 = column1
View 1 Replies
View Related
Feb 20, 2008
select * into dbo.ashutosh from attribute where 1=2
"USE WHERE 1=2 TO AVOID COPYING OF DATA"
//HERE "ASHUTOSH" IS THE NEW TABLE NAME AND "ATTRIBUTE" IS THE TABLE WHOSE REFERENCE IS USED//
//the logic is to use where clause with 1=2 which will never be true and hence it will not return any row//
View 3 Replies
View Related
Jan 2, 2008
I have "inherited" a project working on a SQL 2000 database. The project calculates commissions based on data from an invoice header table and an invoice details table. The goal is to extract data from the primary database tables, perform limited manipulations, and store the resultant data into a table in a different database for reference and reporting. I have the query complete that extracts and manipulates the data, but I am stuck in trying to add this data to the final storage/reporting table. I would also like to do error checking to be certain that a record is not "re-inserted" from the source data to the destination table. Thanks in advance, Barry
View 3 Replies
View Related
Apr 17, 2007
I don't know if this is the right forum but...
In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?
View 3 Replies
View Related
Mar 5, 2001
I have 2 databases on the same sql server. Both have the same tables. I need to copy the contents of tableA from the source database to TableA on the destination database. Can I do this using a SQL script or some kind of script, and if so how and what would the syntax be.
please reply via e-mail also...res2100@yahoo.com
View 4 Replies
View Related
Mar 18, 2005
Hi, I'm pretty new to SQL so am still learning the syntax etc.
I have 2 tables, one called Railways and another called Sheet1$ (an import from Excel).
In Railways, I have 5 Fields:
ID
First_Name
Last_Name
File
Full_Name
At the moment, all are populated with data (300 records) but Full_Name is showing values of <NULL>.
I want to populate Full_Name with the Data in Sheet1$ which has the field Full_Name (which is populated with the data I need)
What is the query I need to run to get SQL to copy the data from Sheet1$ (Full_Name) into Railways (Full_Name) - replacing all of the <NULL> values?
I've tried using the import wizard but without success.
Thanks in advance.
View 5 Replies
View Related
Jan 8, 2008
just wondering how can i script out so that i can copy a table from a database but not the data itself..
.
View 11 Replies
View Related
Jan 9, 2004
I am assuming there is some functionality to copy and paste a table in sql server..however I am not seeing where I can do this what I want to do is this:
create table1 as select * from table 2;
Please let me know how I can create a new table as a replica of an existing table. Thanks so much!
View 2 Replies
View Related
Sep 14, 2006
Hi,o
I found an option to copy a table without having to script the table, now I can't find it. Is there an option to do this? When I selected the option, it didn't work. Any ideas? Also how do you turn off trace and statistics?
thx,
Kat
View 1 Replies
View Related
