Kairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?
Thank-you very much for your time and expertise,
Kairn"
I need a trigger to know who and when a char(1) column is changed. Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test]( [Customer] [varchar](12) NULL, [Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values )
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/
Quote: Msg 295, Level 16, State 3, Line 1 Conversion failed when converting character string to smalldatetime data type.
I am not sure why it is trying to insert into the Last_Update Column, which is the only smalldatetime field in the table. but in the format file, i do not have any data going into that column.
it looks like everything is set correctly in the format file
in the SQL Server Library Link, section "Using an XML Format File" 2nd example, it looks to be the same thing as I am?
I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...
Is their a way to force 1 column to always be unique?
Heres the query:
SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name', (SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2 WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID ) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased', CUSTOMER.customerForename AS 'Customer Forename', CUSTOMER.customerSurname AS 'Customer Surname' FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER WHERE ITEM.ItemID = ORDER_ITEM.ItemID AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID AND ORDER_T.CustomerID = CUSTOMER.CustomerID GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName, CUSTOMER.customerForename, CUSTOMER.customerSurname ORDER BY Total_Purchased DESC
The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.
Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:
ItemID 83630Mathew Smith 8 366Tony Wattage
Which is kinda annoying.... is there anyway i can prevent this?
And also apart from the Where Joins... is there a more efficient way of writing this?
I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
I have an SQL .bak file and I would only like to restore specific columns as one of the columns is a free text field and is substantially increasing the size of the file. I can't restore it due to disk space constraints so dropping the column isn't possible if I can't get the table into a database locally.
Hi, I'm a newbie doing my very first UPDATE procedure. I want to UPDATE a specific row in my Sql db table, WHERE FirstName = "John" AND LastName = "Smith". What am I missing in my code below to do this specific update: Most greatful for any help, thanks.<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @User_ID "> <updateparameters> <asp:parameter Name="User_Name" Type="String" /> <asp:parameter Name="FirstName" Type="String" /> <asp:parameter Name="LastName" Type="String" /> <asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" /> <asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue"/> <asp:parameter Name="User_ID" Type="Int32" /> </updateparameters> </asp:SqlDataSource>
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
write a query which retrieves only unique rows excluding some columns.
IdStatusmanager Team Comments Proj number Date 19391New XUnassigned One 3732.0 16-Apr-14 19392Can YCustomer Two 3732.0 17-Apr-14 19393Can YCustomer Two 3732.0 17-Apr-14 19394Can YCustomer One 3732.0 18-Apr-14 19395New YCustomer One 3732.0 19-Apr-14 19396New YCustomer One 3732.0 21-Apr-14 19397New ZCustomer One 3732.0 20-Apr-14
In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is
IdStatusmanager Team Comments Proj number Date 19391New XUnassigned One 3732.0 16-Apr-14 19392Can YCustomer Two 3732.0 17-Apr-14 19394Can YCustomer One 3732.0 18-Apr-14 19395New YCustomer One 3732.0 19-Apr-14 19397New ZCustomer One 3732.0 20-Apr-14 19396New YCustomer One 3732.0 21-Apr-14
I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.
i.e. EmployeeID: 001
week1: 40 week2: 24 week3: 24 week4: 39
This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?
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
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.
How do I do this?
I have searched the forum for some ideas, but did not find any.
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto period6_subject_id.
Please see my query below:-
SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No FROM Week_Day LEFT JOIN Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
I am using Microsoft SQL 2005, I need to do a BULK INSERT from a .csv I just downloaded from paypal. I can't edit some of the columns that are given in the report. I am trying to load specific columns from the file.
bulk insert Orders FROM 'C:Users*******DesktopDownloadURL123.csv' WITH ( FIELDTERMINATOR = ',', FIRSTROW = 2,
ROWTERMINATOR = '' )
So where would I state what column names (from row #1 on the .csv file) would be used into what specific column in the table.
I saw this on one of the sites which seemed to guide me towards the answer, but I failed.. here you go, it might help you:
FORMATFILE [ = 'format_file_path' ]
Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:
The data file contains greater or fewer columns than the table or view.
The columns are in a different order.
The column delimiters vary.
There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.
Background: After Insert Trigger runs a sproc that inserts values into another table IF items on the form were populated. THose all work great, but last scenario wont work: Creating a row insert based on Checking that all 22 other items from the prior insert (values of i.columns) were NULL:
IF EXISTS(select DISTINCT i.notes, i.M_Prior, i.M_Worksheet, ... from inserted i WHERE i.notes IS NOT NULL AND i.M_Prior = NULL AND i.M_Worksheet = NULL AND...)
I have a table where I want to prevent user from deleting or setting a flag on a field to "y" with a database trigger (sql 2000). I understand the trigger for just one (stopping the delete, or stopping the field being changed to "y"). Should I have 2 seperate triggers or would there be a way to handle both.
I am trying to import an XLS to a table in my database. Sounds simple, but the import should be an UPDATE, not an INSERT. In other words, I want to transfer data from the XLS sheet, to specific field in my database.
Let me explain more clearly.
I have an XLS sheet with a manager ID and a employee ID. The table in the database is already populated with all employee ID's but the field manager ID is still empty. Every employee has a different manager.
The import should place the manager ID's in the fields where the employee ID is the same as in the XLS sheet.
I am building a data warehouse. I have many columns I want to populate in a fact table using integration services. Sample fields are: companyID, companyName, companyNumberOfAccounts, company, NumberOfUsers.
In the integration services package, I would like to keep this fact table in place and populated with data and to build integration services packages that update each of the existing row's specific columns (e.g. companyNameOfAccounts) one by one. For example, I have a source of data that has companyID and companyNumberOfAccounts data.
Is it possible to use the SQL Server Destination or OLE DB Destination Integration Services elements to import companyID and companyNumberOfAccounts data so that existing records just have their companyNumberOfAccounts column updated?
Hello, does anyone know a website, where I can read something about the syntax of Computed Columns? I don't know how to enter the following expression in the computed columns field of MS SQL Server:
I have a text file which I used as an initial load of data, containing a little over a million rows. I would like to produce a daily "update" file, which contains changes to existing rows, and creation of new rows. What is the best method of updating the existing table in power pivot?
Hello, I am new to SSIS. I am trying to write a simple package to export data from some SQL 2005 tables and into a flat file. In my data flow, I am using the OLE-DB data source and then the flat file destination.
This all works fine except that I cant get the package to write the columns out in the order I want. Even when I drive the OLE-DB source by a query, they columns are getting written to the flat file in a different order than I want.
How is SSIS determining what order to write the columns in and, more importantly, how can I change it to do it in the order I want? Please help if you can. As mentioned I am new to SSIS so please give clear+simple answers.
I am new to this level of coding in SQL SERVER 2012, but I am looking to update the TITLE_YEAR field in the temp table with the Year the employee is in that title. For example for employee 11127 the data should look like this:
EMP_IDTitle DateValue TITLE_YEAR 3 Senior Consultant 2009-01-01 00:00:00.0001 3 Director 2010-01-01 00:00:00.0001 3 Director 2011-01-01 00:00:00.0002 3 Director 2012-01-01 00:00:00.0003 3 Director 2013-01-01 00:00:00.0004 3 Senior Director 2014-01-01 00:00:00.0001
Hi..This may be silly but i am stuck at this for some time. I have 10000 rows in a table and i want to copy the value of column 1 in all the rows to column 2 ( which contains NULL presently in all the rows ). The two columns are of the same datatype.
Hello all and I look forward to reading your replys. Currently, I'm trying to update a table in my SQL database by writing a script that will go get the field name and correct info, verify it has the correct field by using two other columns and then update the information.
I do not know how to use tell SQL what to update when the field name can change. I've used the following in the past.
UPDATE [Import Table]
SET [Import Table].[???] = [Correction_Table].[Correct_Info]
WHERE [Import Table].[UNIQUE ID] in (select [USO Unique ID] from [Correction_Table])
IN this new instance, I'm reading the Field_Name from the Correction_Table so I don't know what to put in my SET statement.
Please any help or pointers in the right direction would be greatly appreciated.
I have a table Products with their prices, and I have another table Sales, that have the prices of the products but they are null, I need to make an update that update the tables Sales, inserting the prices from the table Products
I need to do an update. Can you tell me if it's possible to update multiple tables in a single statement when joined, as with a select, or this has to be taken care of in seperate statements with the same join? I'm sure this is a simple YES / NO answer.
Here is what I'm using, with the same structure as somewhere else... why do I get 'incorrect syntax near q' on that very first line?
-- Do the update update ccs_quotes q set ccs_quo_user_date#1 = j.completed from "ken-sql-002".hettonhosttestdatabase.dbo.job j inner join ccs_quotes q on left(j.cid,10) = q.ccs_quo_ref_number#2 where completed is not null and ccs_quo_ref_number#2 = 'SHGD40021'
I need to copy data from one column that has a max size of 45 characters toa column that has a max size of 20 characters.What is the syntax to say fill the first 20 characters out of 45 fromcolumn 1 into column 2?jeff--Message posted via http://www.sqlmonster.com
Hi folks. I'm trying to update a single field in a database table with the input from a single text box, but I'm getting "Insert Error: Column name or number of supplied values does not match table definition". I know at least part of the problem is that I haven't specified the name of the table field, but I'm not sure where to do this. The table in question has many fields, but I only want to update one. The submit button is "sendMail", which calls UpdateDB.I'm learning and trying to limit myself to 1 idiotic question per week. Please have mercy. Thanks. Chrisprivate void UpdateDB(string cmd) { // point to connection string in web.config string connectionString = "Data Source=localhost;Initial Catalog=databaseName;Integrated Security=True"; // create connection object; initialize; open System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); try { connection.Open(); // create sqlcommand object and assign connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(); command.Connection = connection; command.CommandText = cmd; command.ExecuteNonQuery(); } finally { connection.Close(); } }
