Query Design For Adding / Copying And Updating
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
ADVERTISEMENT
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
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
View Related
May 29, 2007
Not receiving any errors. The update runs perfectly on all fields, except for the added parameter during the sub. I need to change the hidden field's value after parsing out several fields in the form and generating a logfile entry of sorts. This needs to happen after all the form fields are updated, but before the update is executed.
aspx: (relevant parts only...)<asp:SqlDataSource ID="TicketDetails" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"UpdateCommand="UPDATE Tickets SET TicketSuspense = @TicketSuspense, TicketPriority = @TicketPriority, TicketLastUpdated = CURRENT_TIMESTAMP, TicketStatus = 'Assigned', TicketTechnicianNotes = @TicketTechnicianNotes WHERE (TicketID = @TicketID)"><UpdateParameters><asp:Parameter Name="TicketPriority" Type="String" /><asp:Parameter Name="TicketSuspense" Type="DateTime" /><asp:Parameter Name="TicketID" Type="Int32" /></UpdateParameters></asp:SqlDataSource>
codebehind: (once again, the relevant parts only)Protected Sub TicketDetails_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles TicketDetails.UpdatingTicketTechnicianNotesHiddenField.Value = "some text..." & TicketTechnicianNotesHiddenField.ValueTicketDetails.UpdateParameters.Add(New Parameter("TicketTechnicianNotes", TypeCode.String, TicketTechnicianNotesHiddenField.Value))End Sub
Thanks,
- Brad
View 3 Replies
View Related
Oct 7, 2015
Here is the DDL of a table that I am trying to add an identity column and create a clustered index. I am using SQL 2012 SE
CREATE TABLE [dbo].[IPF](
[IPFId] [uniqueidentifier] NOT NULL,
[IPId] [uniqueidentifier] NOT NULL,
[FId] [uniqueidentifier] NULL,
[FName] [nvarchar](50) NULL,
[FItemId] [uniqueidentifier] NULL,
[code]....
The table currently has 220million rows. I am trying to add a new identity new column and create a clustered index on it. I ran the script and its been more than 26hrs and its still running. I ensured there is no blocking.
View 7 Replies
View Related
Jul 22, 2015
I seem to having a really hard time when making schema changes. Adding a new fact table, renaming a dimension column, adding a new measure or renaming an existing one. Somehow these things tend to cause problems at one or more stages - either running the schema wizard or processing the cube.So I ask, what is your overall strategy for adding or updating new schema updates into your DB with regards to the SSAS project?
Another way to ask this - is how often do you find yourself deleting the the DSV and / or the whole cube and starting over because some schema change lead to a cascade of issues that just didnt seem to want to let you correct them?
View 2 Replies
View Related
Nov 3, 2006
Hello,
Nice easy one (hopefully) from a newbie on SQL 2000.
I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;
TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No
I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.
I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,
So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.
What simple thing I am not doing ?
Cheers
Neal
View 2 Replies
View Related
Aug 17, 2015
I have a problem setting the default value of a column. I am trying to set it to
(CONVERT([float],getdate()+(2),0))
However, SQL Server automatically sets it to
(CONVERT([float],getdate()+(2),(0)))
While it functionally does not change anything, we have a tool which compares the database schema against a pre-existing schema and shows this as an error.I have tried setting the value directly and through scripts but it does not work either way.
View 2 Replies
View Related
Nov 6, 2015
I have come up with one scenarios where I have three table like Product, Services and Subscription. I have to create one table say Bundle where I can have some of the product id , service id and Subscription id , i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?
View 3 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 13, 2002
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
View 2 Replies
View Related
Mar 24, 2004
Hi,
I wanted to know a query in sql that would copy a whole table from one database to another database with both the structure and the content.
strangely, my college professor don't know the same.
I tried looking in the help menu in sql but it didn't helping me much.
Help me out...!! I have a deadline to meet. I really would appreciate it. thanxs. --vijay.
View 4 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
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Aug 19, 2003
Can I update a select query using sql server 2000? What I mean is, I want to use a query instead of table or a view.
eg update (select * from tablename) set field='value'
something of this sort. I tried doing some r&d but it didn't work. I want it as single query. If some one knows it please let me know. It is very urgent.
Regards,
Samir.
View 1 Replies
View Related
Jan 9, 2012
I am running a query that returns transactions that were done at a given point.I am using the"connections" part of Excel.I have created a workbook connection
Connection type : OLE DB Quert
Command Type: SQL
Command Text:
Select
t.SP_ID AS Salesperson
,sum(CASE t.Transaction_Date WHEN '01/05/2012' THEN (abs(t.Transaction_Units) * t.Transaction_Price
etc
It runs finds and returns my data into a table in Excel.However, I need to manually update the relevant date in the Command Text.Is there a way that the connection automatically updates the date. as indicated in a cell in Excel, or maybe t-1 given that the file updates automatically every time I open?
View 2 Replies
View Related
Jul 20, 2005
First of all I don't normally use SQL, so please excuse my ignorance.1st QueryWe have a table with a column with data that looks like this:AMTL03256636What I need to do is change the AMTL portion of the column to another set ofcharachters i.e TESTCurrently i'm extracting the data, loading it into excel changing the dataand then bulk inserting it backinto the SQL7 table. Is there away to change the AMTL without the othersteps?Alan
View 3 Replies
View Related
Jul 20, 2005
First of all I don't normally use SQL, so please excuse my ignoranceThe following query Selects data based on another table.SELECT A.DSTRCT_CODE, A.WORK_ORDER, A.WO_TASK_NO, A.WO_TASK_DESC,A.TASK_STATUS, A.WORK_GROUP, A.CREW,A.WORK_CENTRE, A.ASSIGN_PERSON,A.JOB_DESC_CODE, A.SAFETY_INSTR, A.COMPLETE_INSTR, A.COMPLETED_BY,B.DSTRCT_CODE, B.WORK_ORDERFROM MSF623 A ,MSF620 BWHERE B.DSTRCT_CODE = 'TEST'AND A.DSTRCT_CODE = 'AMTL'AND A.WORK_ORDER = B.WORK_ORDERWhat I need to do is update A.DSTRCT_CODE column to read 'TEST'.Currently I take the data selected and dump it into excel, change theDSTRCT_CODE field and then upload load using a BULK INSERT statement.Is it possible to insert into the query an update statement? Is thereany special sytax that I should use.Alan
View 3 Replies
View Related
Nov 21, 2006
I'm using SQL Server 2005.
Is it possible to update two tables in a single update query by comparing rows in two tables?
Something like this:
UPDATE h
SET
CheckAmount =c.[amount1],
c.Updated = 'YES'
FROM tblCheckNumber as c
INNER JOIN tblHistory as h
ON c.Autonumber = h.AutoNumber
WHERE (h.CheckAmount Is Null Or h.CheckAmount=0)
AND h.CheckNumber Is Null
AND h.CheckDate Is Null
AND h.AccountNumber Is Null;
View 1 Replies
View Related
Aug 25, 2007
I am trying to insert a value numeric + 1 in to db table but i get error when i do this
this is the code
Const SQL As String = "INSERT INTO [PageHits] ([DefaultPage]) VALUES (@defaultP)"Dim myCommand As New Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@DefaultP" + "1", DefaultP.Text.Trim())
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
The Error:
Must declare the variable '@defaultP'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@defaultP'.
View 1 Replies
View Related
Sep 14, 2007
i want to add vales in a query
this is my scenario (how can i add the price column values and display as one record) ex. 222 17
table = table1
tep1 price (column)
222 5
222 2
222 10
View 2 Replies
View Related
Aug 20, 2007
table a
id pct
1 0.1
2 0.24
3 0.5
table b
year amount
2001 10
2002 20
2003 30
2004 23
2005 67
2006 80
I want to create a table ret
wiht this
year newamount
2006 80*0.1+67*0.24+23*0.25
2005 67*0.1+27*0.24+30*0.25
2004 23*0.1+30*0.24+20*0.25
2003 30*0.1+20*0.24+10*0.25=9.9
How can i do that.
Thanks.
Jeff
View 5 Replies
View Related
Oct 27, 2007
hi
i am new to t-sql. the problem is i have to subtract a field from a column A in table X from a field in Column R from Table Y and put the subtracted value in the Column K in the Table Z
ex:
Table Name: Order
Field Name: Boxes
Table Name: Despatch
Field Name: Boxes
Table Name: Balance
Field Name: Boxes
the problem is i have to subtract the values(Boxes) in the table Despatch, from the values(Boxes) in the table Order, and the subtracted value has to be put in values(Boxes) in the table Balance.
can any one help?
one more thing: Where i can get the basics of t-sql
regards.,
sekar.r
View 2 Replies
View Related
Mar 12, 2004
Hi All,
I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code:
USE DBMyDatabase
UPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
WHERE IDField=TblOther.IDOld
SELECT Pk, IDField
FROM TblToBeUpdated
What am I doing wrong? The error code I get is:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Thanks.
Henry
View 3 Replies
View Related
Mar 28, 1999
I used this with an access DB and would like to re-write it to work with
sql.
sql = "SELECT funds.itemdisc, Sum(IIf(funds.status='Credit'"
sql = sql & ",CDbl(funds.fundamount),0,)) AS income, Sum(IIf funds.status='Debt'"
sql = sql & ",-1*CDbl(funds.fundamount),0)) AS outgo"
sql = sql & " FROM funds "
sql = sql & "GROUP BY funds.itemdisc"
What I am doing here is collecting the columns and using status as
credit or debt, if its debt I am subtracting from outgo if it is credit
I am adding to credit. This also will only select records so that
there are not any double listings. Here is an example.
DB
desc status fundamount
bill debt 10.00
bill debt 15.00
in credit 10.00
bills debt 5.00
in credit 5.00
paper debt 5.00
When I do the query I should have this
desc fundamount
bill -25.00
bills - 5.00
paper - 5.00
in 15.00
Something like that...
What do you think?
Thanks in advance.
View 1 Replies
View Related
Apr 16, 2008
I am new to SQL and have an issue I can not figure out. I have a query with several JOINS, but know my boss is asking for me to add an entire table to my query to view results. Is it possible to add a table to a query with joins? I have tried subquery but I get an EXIST error message. The only way I was able to get the information was join the table and enter every single column to my query. I hope there is an easier and more efficent way of doing this.
Thanks,
View 2 Replies
View Related
Oct 29, 2013
I have a pretty simple SQL query that has two columns that contains only numbers. Is there a way i can add an extra column that simply subtracts the number in one column from number in the other column.I query the data tables rather than have a live link so the .csv file gets overwritten every time i run the query.
View 4 Replies
View Related
Oct 2, 2007
Hello, I am trying to achieve a query results with not much luck. I am struggling the query design of how to construct to get the right results.
My table has the following data
RoomID Subject StartDate Duration(min) EndDate
201 test 28/09/2007 07:00:00 180 28/09/2007 10:00:00
202 test 28/09/2007 09:00:00 240 28/09/2007 13:00:00
The best way to describe is a scheduling system with the StartDate, Duration, EndDate.
I am trying to query for datetime periods which are allocated, eg the table records show from 7am - 10am and 9am - 1pm are allocated.
When I query on StartDate >= 28/09/2007 07:00:00 AND EndDate <= 28/09/2007 13:00:00, record 201 & 202 shows which is correct because they are within query parameters and are allocated.
When I query on StartDate >= 28/09/2007 07:30:00 AND EndDate <= 28/09/2007 13:00:00, record 202 only shows which is correct because record 201 StartDate is before 07:00:00.
But my problem is I require record 201 to show because the time period between 07:00:00 and 09:00:00 is allocated and the query parameter is 07:30:00 which is still between 07:00:00 and 09:00:00.
I have researched where on similar situations, the use of duration is used, but I am unsure on its application to achieve results.
I have also read of other situations where a lookup table should be used, but again I cannot get my head around the application of alookup table to query the resuults.
Does anyone have any theories, design thoughts or real work solutions they can help me with.
Thanks
Peter Smith
View 9 Replies
View Related
Oct 2, 2005
I am designing an ASP.NET app that can be used to keep track of
attendance at office hours for a class. The purpose of this is
that we need to know if a student is attending office hours by
different people (so that we can flag them as "in trouble"). I
don't know if I have chosen the best database design, and I'm lost as
to how to accomplish a query I need.
I have a table HoursAttendance that has the following design.
Column_Name Data_Type Length Allow_Nulls
TA
char
4
n
Date
smalldatetime 4
n
Start
smallint
2 n
End
smallint
2 n
Student1
bit
1 y
Student2
bit
1 y
Student3
bit
1 y
Student4
bit
1 y
Student5
bit
1 y
I chose to have the students as columns because the students don't
change, and then you add rows of office hours. If students are
the rows, then you would be adding columns as the semester continued
which I thought was odder...? I'm completely open to suggestions
on Database Design, because I really wasn't sure.
Ok, so now I need useful queries. The one that I am stuck on (and
also the first one besides select * from HoursAttendance) is that I
want the names of Students who have attended more than x office
hours. So I need something like
select <column name> from HoursAttendance where count(sum(<column name>)) > x
Granted a better table design could help with this. I'm
relatively new to design, so constructive criticism is desired please
View 7 Replies
View Related
May 27, 2008
Hi everybody have this query that list all the duplicate records meaning the occurence of pubinfid in more than one row. This gets me fine. Iwant thought to insert a where clause that further filters the result where pubid between 30 and 33. I tried placing it after the FROM CLAUSE BUT DOESN'T GIVE me what I want it still includes records other than the between clause. Also I tried placing it after the HAVING clause but it includes records which has only one count.. Where could I place the where clause or is ther an alternative for this.
SELECT a.pubinfid, a.pubid, a.pubcount
FROM pubssubscribe AS a INNER JOIN
(SELECT pubinfid
FROM pubssubscribe
GROUP BY pubinfid
HAVING (COUNT(*) > 1)) AS b ON a.pubinfid = b.pubinfid
thanks
View 8 Replies
View Related
Jan 13, 2004
hello everybody,
I have a small problem and was hipeing someone could help. Want to run a query, where I sellect results from two tables. Once this is done I want to takes those results and update a table that is already created with those results. How would I do this? Thw eonly way I know how to do this is by writing this:
select CompanyName, ContactName, Address, City, Region, ProductName, UnitPrice INTO TABLE2
From Suppliers, Products
But the problem with this is that I can only input the query results into a new table that I would name above. I can't insert it into a table that has already been created.
This is the erroe that I receive:
There is already an object named 'TABLE2' in the database.
Please Help!
thanks
View 8 Replies
View Related
May 22, 2008
Is there any way I can add record number to my query result?
Exp:
Name Add tel
A 1 st 123456
B H ave. 987456
I need:
No Name Add tel
1 A 1 st 123456
2 B H ave. 987456
Thanks
View 9 Replies
View Related
