We have a query that joins column A int which is an int onto column B with contains only int's but was created as a varchar and can't be changed to an int at the moment.
Casting column a as a varchar in the ON of the join to left join seems to void the index altogether and the query just runs for every.
We are talking a few hundred million rows of data in each table.
Temp solution is select into a #Hash table as correct data type and index then use the #Hash table in the join.
I am trying to join two tables together, on the same field except they have different data types, see the properties below
Code: TableCOLUMN_NAMEDATA_TYPECHARACTER MAXIMUM LENGTHCHARACTER OCTET LENGTHCHARACTER SET NAMECOLLATION NAME 1itemClassnvarchar 512 1024 UNICODE Latin1_General_CI_AI 2PGCode varchar 3 3 iso_1 Latin1_General_CI_AS in the code for the join,
Code: left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))
I have tried using the CAST function on one side of the join then on both, to no avail...
I have a requirement of creating nested tables in SQL server. how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.
Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.
Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address
Address (Nested table): with columns : Street, Town, Dist, State
This was done in oracle using Nested tables and user defined data types. what is alternative for this in SQL server. How can I achive this requirement in SQL server.
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped: [Department, DEP_CLEGALCODE] One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
I have an SSIS package that pulls data from a MYSQL DB (Using RSSBus for Salesforce in SSIS to accomplish this). Most of the columns are loading properly, but I have many columns that I need to convert.
I have been using the Data Conversion dataflow task in SSIS to convert the rows.
I have 2 data conversions that work on most of the columns, but the DESCRIPTION column continues to return an error saying "Cannot convert between unicode and non-unicode types", regardless of what I choose on the Data Conversion task. So, basically I want to dump this column data into a SQL table with NVARCHAR datatypes. Here is what I am doing in my SSIS package...
1) Grab subset of data from SOURCE 2) Converts to TEXTSTREAM. (Data Conversion) 3) Converts to STRING. (Data Conversion) 4) Load Destination table. (OLE DB Destination)
I have also tried to simply convert the values to STRING, but that doesn't work either.
So, I have 2 Data Conversions working here that process most of the data correctly. What I can do to load the DESCRIPTION column?
I have created a common table expression. The first query is meant to select a particular months worth of data. The second query is meant to select the following months data but only for the subscribers (MSISDN's) that appeared in the first month.
The query returns result for month 1 but ignores the second month. If I remove the last inner join to the CTE (INNER JOIN Test_Table tt ON tt.MSISDN = B.SUBSCRIBER_NUMBER) then I get a full list for both months but obviously this isn't limited to those MSISDN's in the first month.
The second issue that I'm having is that I can't select a sample of say only a 1000 MSISDN's from the first month...
WITH Test_Table (Report_Month, MSISDN, SUB_SEGMENT) AS ( Select R.Report_Month, R.MSISDN, DV.SUB_SEGMENT
Will writes "I have an employees table, which gives all emp. ids. I have a second table, time_log with tasks each employee has logged:
empID date log_time duration etc ===================================
and a 3rd table - a pivot table with a single column named "i" containing consecutive integers from 0 - 1000
I need to know for each date in a series, e.g seven consecutive days, how much time each has logged. easy if everyone has logged a task for every day, but I need to include every day where they have not logged a task.
so, a cartesian join on all the dates in a series(produced using dateadd on p.i and the pivot table)
SELECT dateadd('d',p.i, #02/19/2007#), e.empID FROM pivot1 p, employees e WHERE i<no_dates
However I need to do a left join with time log where the date and employee ids are the same, and I have summed the durations for each date. The following query does this, but does not include dates and times where nothing has been logged.
SELECT empID, log_date, sum(duration) FROM time_log GROUP BY empEIN, log_date
GIVING, EVENTUALLY, ALL DATES AND EMPIDS AND THE TOTAL AMOUNT OF TIME THEY HAVE LOGGED FOR EACH DAY."
Hi, I'm seeing confusing results coming back from a query and I want to make sure my joins are working as expected.
I have 3 tables, tbl_family, tbl_familyPhone, and tbl_phone. tbl_FamilyPhone is a linking table between families and phones that specifies if it's the primary number.
So a family has many familyPhones and a phone has many family phones. I'm trying to get all the families and their home phone only, if they have one. I don't want families to duplicate and I don't want any left out. Here's what I've got
Code Snippet select [whatever]
from tbl_family LEFT OUTER JOIN tbl_familyphone on tbl_family.pk_familyid = tbl_Familyphone.fk_familyid inner JOIN tbl_phone on tbl_familyphone.fk_phoneID = tbl_phone.pk_phoneid and tbl_phone.fk_phonetypeid = 'E6F1688E-015B-481D-8C41-DCC1FEA5D5AB'
My thinking is the inner join between tbl_Phone and tbl_FamilyPhone will cause any FamilyPhone record without a phone record to be left out (fk_phonetypeid is the id of a home phone). Though some FamilyPhone records may be left out, I will not lose any families because it is left outer joined to familyphone. Is this right? Because if I just do select count(*) from families I get 4517 records, but when running the query above with the joins, I get 4383 records.
I am trying to write a generic stored procedure to return the data type of a given col in a given table in any database.
I would like to place this sp in master.
input parms are @TableName sysname and @ColName sysname ouput parm is @data_type varchar(128)
set @data_type='' select @data_type = data_type from information_schema.columns where table_name = @TableName and column_name = @ColName
when debugging this I always get 0 rows (@data_type remains '')
I have a thought that I can add an input parm for the database, say @DbName sysname But I am having trouble updating the select stmt to be something like ...from @DbName +'.' + information_schema.columns.
I've been writing this stuff for a while, and can't seem to come to the conclusion of how I should be retrieving data and assigning this data to variables.
Since i'm using SQL Server, I'm convinced that I should be using the datareaders GetSqlDouble (or whatever) function, but this would mean i need my local variables to be one of the SQL types. The problem with that is, that there will have to be lots of conversions done by me to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order to retrieve them correctly (Null values included), I need to retrieve them with GetSqlDateTime(), then when it comes time to display the date in a table, i must first check for nulls, then convert to a string. This seems to be very cumbersome. Would I be better off just using GetDateTime(), and the .ToString method, and ignoring Sql Types all together?
so, basically, how are you guys using your sql server data? with the supplied sql types, and doing all of the post-processing work manually? I feel like i'm having trouble conveying my issue...hopefully someone knows what i mean....i'd just like some direction to save trouble in the long run, since i feel like there's got to be a better way...
Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...
Is there a way to change the default data type in SQL Server? If I import a database into SQL Server it makes every non-numeric field a nvarchar field. Is there a way to change this so that these fields would be varchar or char by default? I cannot find any way to redo these as a batch/group update and have to modify every single field manually. Any insights greatly appreciated.
I trust you'll bear with an SQL Server newbie with what may seem a rather inane request. I am designing a web app in Web Designer 2005 Express with SQL Server Express. Unfortunately, I'm finding a little confusing with some of the data types when designing tables. I have tried to find information on the various Microsoft sites (general site, MSDN, here) and while I found one document that had a table comparing data types in different implementations of SQL, it wasn't at all helpful. Most of my confusion is with the various string and char types; the numeric types seem pretty straight forward for the most part. However, it might be helpful to know the difference between money and smallmoney/datetime and smalldatetime, particularly space/size information and formatting options (unless the latter is up to the interface). It would also be helpful to know which string/char types correspond to any counterparts they might have in, for instance, Access (with which I am already quite exprienced). Or any particular quirks or idiosyncracies they might have. I don't expect anyone to write a full tutorial, but if someone could point me in the direction of a good online doc, it would be most appreciated. You might well ask, why not use Access databases? I would answer...I like to learn new stuff! Thanks much.
Hi, Can anybody give me some information on Table data types of SQL2K. How and where can it be used. Does it make the queries faster, in case there are many users using ? Please could anybody give me these details, to make me decide whether I can use it or not ?
I need to create a view of a sql table, but change the data types. I knowthe syntax below is not correct, and can't figure out if it is wrong or ifyou just can't do this. I have only created views before with the same datatype.CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01FROM F0005 AS F0005WHERE DRSY = '41' AND DRRT = 'S1')Thanks!!
Hi Can anyone point me to a document somewhere that shows a mapping ofSQL Server 2000 datatypes to C datatypes? I am writing some extendedstored procedures which need to be able to process pretty much anydata type, so I want to make sure I am taking them from SRVPROC andstoring them in the correct C data type.Thanks,Bruce
Hi,Can anybody tell me if it is possible to return a table data type (asopposed to a temporary table) from a stored procedure.I am currently studying for 70-229 using the Thomas Moore book but itdoesn't really make it clear, as the text suggests you can't returnthem but some of his sample questions suggest you can.I can't make it work in Query Analyser so I am assuming that you can'treturn them. Is that correct?Thanks in advance for any help,Simon.
I´m building an aplication with VB.net and SQL Server 9, but in the future it will be compatible with PostgreSQL (by another developer), so my question is, if anyone knows what "data types" in SQL Server 9 i must use to maintain compatibility with PostgreSQL, so that when the time come for the conversion i don t have to chage the "Data Types" on each table.
We are developing customer support application. We will have so many customers after launching this product. But my problem is how will i store data of all these customers in SQL Server. Please suggest me.
I need to populate some SqlParameter but I temporarily need to populate with a default value for the data type in question. Of course I can work them out, but I was wondering if there is already a way of doing this, from the .Net Classes or from SQL Server but without making a trip to the DB.
i have created asp.net page, one feild of this page text area. when i insert some text through this page in "text feild" of SQL server on few words of this feild cut and inserted to text feild of SQL server but all text that i have written in text area feild.
can u please help me how to handle so that i can all text in text feild of SQL server data type text/next.
I have a simple Integration Services project and the problem is that decimal fields are importing as real (I'm loosing the digits behind the decimal point).
The project contains a data flow task importing a flat file (.csv) to an SQL Server destination. My .csv file has two decimal type fields. Example:
Field 1: 12345.67
Field 2: .123456
My database table that I'm importing to has two fields. The only way that I can get this data to import is to define the fields as "float" in both the text file and database table. I want the database table fields to be defined as decimal or numeric, not float:
Field 1: decimal(7,2)
Field 2: decimal(6,6)
When all fields are defined as decimal (in both the flat file and database file), I get the following results:
Field 1: 12345.00
Field 2: .000000
How does one import decimal data from a flat file (.csv)?
These two T-SQL statements return the same results.
If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN
-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER
USE AdventureWorks2008R2; GO SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder ;
USE AdventureWorks2008R2; GO SELECT DISTINCT Production.WorkOrder.ProductID FROM Production.Product INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID
What is wrong with my syntax?I want to return the value of the AchiveYear Value based on records in theCall that match.
SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear FROM tblCall INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch ON tblCall.DATEPART(yyyy,Call_Date) = Arch.ArchiveYear
I came across this structure today and haven't seen it before:
SELECT blablabla FROM T1 FULL OUTER JOIN T2 ON T1.Col1 = T2.Col1 AND T1.Col2 = T2.Col2 ON T3.Col1 = T1.Col1 AND T3.Col2 = T1.Col2 ON T4.Col1 = T1.Col1 AND T4.Col2 = T1.Col2
I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).
the join is like - JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK) ON User_Count_Id = Analyst_Id_fk WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk )
Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.
I have two tables tabA (cola1, cola2, cola3) and tabB(colb1, colb2, colb3, colb4) which I need to join on all 3 columns of table A.
Of the 3 columns in tabA, few can be NULL, in that case I want to check the joining condition for the rest of the columns, so its conditional joining. Let me rephrase what I am trying to acheive - I need to check if the columns in joining condition is NULL in my 1st table (tabA), If so I need to check the joining condition for the rest of the two columns, if 2nd column is again NULL, I need to check the joining condition on the third column.
What I am trying to do is as below. Its working, but is very slow when one of the tables is huge. Can I optimize it or rewrite in a better way ?
--- First Create two tables Create table tabA (cola1 nvarchar(100), cola2 nvarchar(100), cola3 nvarchar(100)) Insert into tabA values (NULL,'A1','A2') Select * from tabA create table tabB
