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 :/
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 having problem when performing the alter table switch. Both tables are identical and have pk.
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresults' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part' .
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON TResultsScheme (testresult_id) ) ON TResultsScheme (testresult_id)
I have performed the function on other tables successfully, but this is the first that has an identity column involved. Is there anything special that needs to be done?
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
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?
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 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.
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
New table.
First Name, Description  Tom         , manager
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 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 business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):
Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.
This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.
Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.
Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.
Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
Hi I have used the create user wizard to create a registration page my table stores the user details and user id. I am also using the login wizard to create a log in page . I now want to display the details of the currently logged in user usind details view and allow them to view and edit their details. where and how do i create the session varible anh how do I wtire the sql select statement say select first name from table1 where (the userid I stored earlier in a table when the user registered ) = (this should be the currently logged in user'id). I am a novice so I would appreciate code snippets My code in asp page for the details looks like this asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource1" Height="50px" Width="125px"> <EditRowStyle BackColor="#CCFF99" /> <AlternatingRowStyle BackColor="#FFCCFF" /> </asp:DetailsView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" SelectCommand="SELECT [FirstName], [LastName], [City], [Listing] FROM [UserDetails] WHERE ([UserId] = @UserId)"> </asp:SqlDataSource>
novice This shows no data when I test it. I have tried the folling in the .vb page no luck. Protected Sub DetailsView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles DetailsView1.DataBound Dim UserId As Integer = Me.DetailsView1.DataItem("userID")Session("_UserID") = UserId End Sub
DECLARE @strComma VARCHAR(1000) SET @strComma = 'cmpnt_name,ASC,1,2' SELECT CAST(RIGHT(LEFT(@strComma,Number-1), CHARINDEX(',',REVERSE(LEFT(','+@strComma,Number-1)))) AS CHAR(30)) FROM master..spt_values WHERE Type = 'P' AND Number BETWEEN 1 AND LEN(@strComma)+1 AND (SUBSTRING(@strComma,Number,1) = ',' OR SUBSTRING(@strComma,Number,1) = '')
however i getting the data in one field which is like
cmpnt_name ASC 1 2
i would like them to be in their specific column that is 4 different field
This is related to SQL Server 2005 I have a table named BranchDetails having a column named branchnumber.
The data in the branch is in the following order 2007-90 2006-9 2007-67 2006-8 2007-98 2007-56 2006-1 Null
I want to write a T SQL query that will bring the result in the following descending order with null at the top Null 2007-98 2007-90 2007-67 2007-56 2006-9 2006-8 2006-1
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report. However, I only want to select specific data from the dataset to fill textboxes and others. I cannot change the stored procedure, but the sample of the data is shown below:-
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories. In my first table, I tried putting the cells as follows:- (expressions are highlighted in italics and bold)
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value. Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
and it give me an error The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
i have a table Animals with column names id and Name eg1 Tiger2 Lion3 Elephant4 Cheetah5 Zebra i want the order to be anything of my chossing when i select from it. eg how could i get it to display results in this order (when i do select * from Animals): CheetahElephantZebraTiger Lion Thanks
I have a table, multiple columns, thousands of rows.
Six of the columns is the data that i need to work with...
col1, col2, col3, col4, col5, col6
col1 and col2 - go together - example. col1 = amount col2 = description col3 and col4 - go together col3 = amount col4 description col5 and col6 - go together col5 amount and 6 description
i need to pull search the table based on an auto number "id" and pull in the necessary two columns that correspond with a set value in the description.
example:
if col4 has "fee applied" in the description i need to pull the amount.