SQL Server 2014 :: Select Query With 5 WHEREs
Jan 23, 2015create table [where] ([where] char(5))
go
insert into [where] ([where]) values ('where')
go
select [where] from [where] where [where] = 'where'
go
drop table [where]
go
ADVERTISEMENT
SQL Server 2012 :: Using Select Query To Get 2014-08-09 11:13:03 From Original Date Records?
Aug 18, 2014I have date field in table and data contain 2014-08-09 11:13:03.340
when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03
Why the 03 is trimming in smalldatefield.
how do I use select query to get 2014-08-09 11:13:03 from original Date records
SQL Server 2014 :: How To Call Dynamic Query Stored Procedure In Select Statement
Jul 23, 2014I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
SQL Server Admin 2014 :: How To Convert Float To Timestamp In Single Select Query
Mar 18, 2015How to convert float to timestamp in single select query
E.g., i have float as 1.251152515236 ,
I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
Wheres The Data?
Jan 8, 2006Ok, this is driving me nuts.
I create a SQL database and populate the fields. I can see the fields in the grid with the data, I tell it to update and it does, at least there is a delay while it does.
Me.AddressesBindingSource.EndEdit()
Numrecords = Me.AddressesTableAdapter.Update(Me.MyFirstDatabaseDataSet.Addresses)
The value of Numrecords is the correct number of new fields. But, if I exit the program and come back in, the new records, or ones I deleted are not there. I was populating the table by reading information from a folder in my program. (The above statements are in the form1.closingform event.)
So, I decided to try their 'example', As shown above, doing it exactly as shown. The database works correct, I can see the information, I can add an entry, but when I exit and come back in, it is not there.
Side note: Before I tried this with SQL, I had it set up as an access connection string and it was doing the same exact thing!!
What am I doing wrong?
Larry
SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?
Jun 27, 2014I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
SQL Server 2014 :: Select All Points Within Polygon
Mar 25, 2014I have a table which hold records that contain all the lat/long points. I want to select all records that are within a polygon. Server 2008...
View 6 Replies View RelatedSQL Server 2014 :: Select On Linked Databases
Sep 18, 2014I am trying to find out what records on in one DB and not the other. This SQL statement works find if the DBs are on the same Server. If I replace on the DBs with a linked DB (on another server) I get the following error.
SELECT *
FROM [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price]
WHERE NOT EXISTS
(SELECT [LocalDB].[dbo].[Sales Price].*
FROM [LocalDB].[dbo].[Sales Price]
WHERE ([LocalDB].[dbo].[Sales Price].[Item No_] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Item No_]) AND
([LocalDB].[dbo].[Sales Price].[Unit of Measure Code] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Unit of Measure Code]) )
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Unit of Measure Code" could not be bound.
SQL Server 2014 :: Select Records Start With %
Jul 28, 2015I need to select records in column that start with '% ' -- %space e.g. column A = % mytest
I tried select column name with like operator but it select records who start with % and ignore space
select columnA from table where columna like '% %';
SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements
Oct 30, 2015When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
SQL Server Admin 2014 :: Select Certain Column To CSV File
Jun 25, 2014I have a database it is 50 gb with hundreds of columns. I would like to choose a certain column and convert the data in it to .csv or excel file. How can I do that I am very new to MSSQL...
View 1 Replies View RelatedSQL Server 2014 :: Select Parent And Child With HiearchyID
Sep 4, 2014How to select parent and child with HierarchyID base on child attribute.
Here's my table :
CREATE TABLE #BOMTbl
(
ItemNo HIERARCHYID NOT NULL,
Lvl AS ItemNo.GetLevel() PERSISTED,
MatID VARCHAR(25) NULL,
CONSTRAINT PK_#BOMTbl PRIMARY KEY NONCLUSTERED(ItemNo)
[Code] ....
I wanna the result like this :
ItemNoItemIDLvlMatID
0x5AC0/1/1/2
0x5AD6/1/1/1/3MA-A
0x5ADA/1/1/2/3MA-A
0x5B40/1/2/2
0x5ADE/1/1/3/3MA-B
0x5B56/1/2/1/3MA-B
0x5B5A/1/2/2/3MA-B
SQL Server 2014 :: Select Columns From Different Tables Without Join
Jan 20, 2015How to join 2 heap tables with out any common fields.
for example tbl1 has
col1
1
2
3
4
5
and tbl1 2 has
col2
a
b
c
I want the output like
col1 col2
1 a
2 b
3 c
4
5
is this possible with out using row_number()?
SQL Server 2014 :: Select Data And Count In View
May 26, 2015How can I select data from a table and row counts from multiple tables in a view. For example:
Select * from Settings -- it gets 1 row only
Select count(*) from NewApps where Status = 'False'
Select count(*) from myUsers where Status = 'Pending'
I just want to get them all in 1 view...
SQL Server 2014 :: Concatenating Rows In A Select Horizontally
Oct 8, 2015I'm using the SQL indicated below:
SELECT
DISTINCT C.Field1 As 'Group',
A.Field2 As 'Security Object'
E.Field3 As 'User'
FROM TableA AS A
[code]...
However I'm having difficulties concatenating the user field in the horizontal form indicated.
SQL Server 2014 :: Adding One More Column For SubTotal In Select Statement?
Feb 13, 2014We have a table with 2 columns 'OrderNo' and 'Amount' as below
ORDERNO | AMOUNT
1D1ZX000 | 9262.5
1D1ZX001 | 9000.0
1D1ZX001 | 9000.0
1D1ZX002 | 10000
1D1ZX003 | 1000
1D1ZX003 | 200.50
1D1ZX003 | 100.50
1D1ZX004 | 500.0
1D1ZX004 | 1000
1D1ZX004 | 2000
1D1ZX004 | 1000
as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column so am writing a select statement with WHERE condition and I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number as below
ORDERNO | AMOUNT | SubTotal
1D1ZX000 | 9262.5 | 9262.5
1D1ZX001 | 9000.0 | 18000
1D1ZX001 | 9000.0 | 18000
1D1ZX002 | 10000 | 10000
1D1ZX003 | 1000.0 | 3001
1D1ZX003 | 2000.5 | 3001
1D1ZX003 | 1000.5 | 3001
1D1ZX004 | 500.00 | 4500
1D1ZX004 | 1000.0 | 4500
1D1ZX004 | 2000.0 | 4500
1D1ZX004 | 1000.0 | 4500
SQL Server 2014 :: Add Multiple Records To Table (insert / Select)?
Jul 31, 2014I am trying to add multiple records to my table (insert/select).
INSERT INTO Users
( User_id ,
Name
)
SELECT ( SELECT MAX(User_id) + 1
FROM Users
) ,
Name
But I get the error:
Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'.
But I am using the max User_id + 1, so it can't be duplicate
This would insert about 20 records.
Why the error?
SQL Server 2014 :: Select Data From Table With A Particular Column Priority
Aug 1, 2015DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[Code]....
i want to select the data priority wise
the o/p should look like below
first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
SQL Server 2014 :: Select All Top Level File Paths From A String Value?
Oct 26, 2015I have a problem where I need to select all top level file paths from a string value in SQL
So I have a column "Locations"
Example Data:
X:folderanotherfolder
X:folderyet another folder
X:foldernameanother folder
X:foldernameyet another folder
I'd want to return only:
X:folder
X:foldername
I need to somehow parse the sting and capture anything before the second ''?
SQL Server 2014 :: Error On Unique Index - Duplicate Key When Insert / Select
Aug 4, 2014I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')
[Code] .....
SQL Server Admin 2014 :: Crystal Report Cannot Select Into Temp Table
Jul 30, 2015I know select * into ##tmp1 should work but I get a database connection error.
View 9 Replies View RelatedSQL Server 2014 :: Select Statement Returning Too Many Rows For Backup Start Date
Jun 3, 2015Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'
[Code] .....
SQL Server 2014 :: Recursive Query Using CTE
Sep 14, 2015sql recursive query, for example purpose i m providing sample table with insert script
CREATE TABLE Details(
parentid varchar(10), DetailComponent varchar(10) , DetailLevel int)
GO
INSERT INTO Details
SELECT '','7419-01',0 union all
SELECT '7419-01','44342-00',1 union all
SELECT '7419-01','45342-00',1 union all
SELECT '7419-01','46342-00',1 union all
SELECT '7419-01','47342-00',1 union all
SELECT '7419-01','48342-00',1 union all
SELECT '7419-01','49342-00',1 union all
[code]....
From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.
NULL,'7419-01',0
'7419-01','52342-00',1
'7419-01','52342-00',1
'52342-00','54342-00',2
'54342-00','54552-00',3
'54552-00','R111-54',4
'R111-54','R222-54',5
'R222-54','52342-00',6
SQL Server 2014 :: Need A Query On Group By Condition
Oct 11, 2013I have one table which contines 3 columns they are
1.emp
2.monthyear
3.amount
in that table i am having 6 rows like below.
emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000
i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.
out put should be like this
1 2000
2 1000
SQL Server 2014 :: Metadata About Current Query
Oct 21, 2015I'm curious if there is a way to gather any metadata about a query that was just ran, or about a dynamic query passed in as a parameter, such as being able to "dynamically" return a list of columns in the query, along the lines of:
SELECT * FROM [myTable];
SELECT @@COLUMNS --Expecting this to return a recordset of columns for the previously run query
I'm not talking about querying metadata about the tables themselves, I'm really looking for what "metadata" might be available about the query itself.
This would be useful for me to dynamically generate some code for ad-hoc queries with a stored procedure call, or to simply return a list of just column names only without the data.
Is this at all possible in SQL Server?
SQL Server Admin 2014 :: Query To Get Cumulative Package
Feb 6, 2014Query to get CU# of sql server Instance ?
View 3 Replies View RelatedSQL Server 2014 :: Truncates When Attaching Query Results
Jul 6, 2014Installed SQL Server 2014 CU1. While testing sp_send_dbmail I noticed the query results, when attached are cut off or truncated. Max file size has been 64k -65k. I set the max file size to 104857600 and set @query_no_truncate = 1.
View 9 Replies View RelatedSQL Server 2014 :: Error In Adding Condition In Query
Apr 4, 2015Following is a working code
declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')
[Code] ...
I am getting the output correctly. My requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id
I want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
When I tried this code I am getting error.
SQL Server 2014 :: Query Store Folder Not Visible
Jul 16, 2015As SQL Server 2016 category is not created so I am posting this query in SQL Server 2014 window.
I have enabled Query Store in SQL Server 2016 using ALTER DATABASE and later tried with Properties of Database too. But I am not able to see the Query Store subfolder under database.
SQL Server Admin 2014 :: Query Multiple Servers With A Scheduled Job Using CMS?
Mar 13, 2014I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.
tell me if they have been successful just using standard jobs and querying against multiple servers?
If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.
I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.
SQL Server Admin 2014 :: Execute Dynamic Query 1 As A Transaction
Feb 7, 2015I have this command :
Exec 'update .... insert ..... delete ..... insert ...'
I Execute these command in one execution.
exec ('...')
Are these commands act as a transaction? If one of them create error , another commands run or rull backed?
SQL Server 2014 :: Query To Combine Two Tables Based Third Table
Feb 18, 2015I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
Table D
SQL Server 2014 :: Query Plan For Partitioned Views Not Running As They Should
Mar 29, 2015I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:
SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.