Query Syntax

48K

Solutions

15

Articles & Videos

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

In the following @table...

I need to set active = 1
Where ExternalID is duplicated and the ExternalID2 rows are not equal

As an aside.. the duplicated ExternalID LESSER IndividualID datarow's ExternalID2 will always be 0 and the second row will be > 0

DECLARE @Table TABLE
    (
        BusinessID INT ,
        IndividualID INT ,
        ExternalID INT ,
        ExternalID2 INT ,
        Active BIT
    );

INSERT INTO @Table (BusinessID ,IndividualID ,ExternalID ,ExternalID2,Active)
VALUES (12345 , 100 ,222 ,0 ,1) ,
       (12345 ,101 ,223 ,0,1) ,
       (12345 ,102 ,223 ,555,1) ,
       (12345 ,103 ,322 ,0,1);


SELECT * FROM   @Table;

Open in new window


SP
0
Turn Insights Into Action
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Hi have a query that I need to match on titles selecting that latest value

select top 10 itemtypeid,ItemType from ItemsTypes where ItemType like '%' + 'Mud Pump' + '%' order BY itemtypeid desc

this would return the below results,

12239      Radiator [Mud Pump Engine]
12238      Mud Pump Valve [Discharge]
12237      Mud Pump Valve [Bleed Off]
12236      Mud Pump Remote Station
12235      Mud Pump PTO
12234      Mud Pump Hydraulic Drive Pump
12233      Mud Pump Engine Transmission
12232      Mud Pump Engine Torque Converter Cooler
12231      Mud Pump Hydraulic Drive Motor
12230      Mud Pump Engine Torque Convertor

If I were to use this Title "Radiator [Mud Pump Engine]"  in the query no results would be retuned.

select top 10 itemtypeid,ItemType from ItemsTypes where ItemType like '%' + 'Radiator [Mud Pump Engine]' + '%' order BY itemtypeid desc

How can I make a better query to return data if the titles might contain square brackets or not ?

Thanks
0
I am running the below query to count number of appointments for each day of the month and you can see the output below that.  I want to add a sub query to break out the total appointments for hour 17 for each day and have them show in the Hour17 column by each day.  I am currently getting the total number of appointments for the month in the Hour17 column.  I understand why but can't come up the solution to break it on down by day.

select Monthname = 'May',DATEPART(DD,ApptStart) as day,COUNT(DATEPART(DD,ApptStart)) as Total,
Hour17= (select COUNT(DATEPART(hh,ApptStart)) as HTotal
from Appointments apptb
where apptb.ResourceId = '560' and DATEPART(M,apptb.ApptStart) = 04 and DATEPART(yyyy,ApptStart) = 2017 and DATEPART(hh,apptb.ApptStart) = 17)
from Appointments appta
where appta.ResourceId = '560' and DATEPART(M,appta.ApptStart) = 04 and DATEPART(yyyy,appta.ApptStart) = 2017
group by DATEPART(DD,ApptStart)


May      3      10      77
May      4      8      77
May      5      4      77
May      6      8      77
May      10      7      77
May      11      7      77
May      12      12      77
May      13      3      77
May      17      6      77
May      18      12      77
May      19      11      77
May      20      19      77
May      24      9      77
May      25      6      77
May      26      11      77
May      27      9      77
0
I have a proc that generates / suggests indexes that are needed.

I have an individuals table...
Went from 25000 to 600k+ records

And selects on where FirstName = '%%' simply bogged down to a timeout.

So...
IIT was suggested I add this
Create NonClustered Index IX_Individuals_missing_1852 On [EverywareV3].[dbo].[Individuals] ([FirstName]);

I executed and it has been running 2 3/4 hours.

Is there anything more efficient?

This is the code that generated the "missing" index.

SELECT   t.name AS 'affected_table' ,
            'Create NonClustered Index IX_' + t.name + '_missing_' + CAST(ddmid.index_handle AS VARCHAR(10)) + ' On ' + ddmid.statement + ' ('
            + ISNULL(ddmid.equality_columns, '') + CASE WHEN ddmid.equality_columns IS NOT NULL
                                                            AND ddmid.inequality_columns IS NOT NULL THEN ','
                                                        ELSE ''
                                                END + ISNULL(ddmid.inequality_columns, '') + ')'
            + ISNULL(' Include (' + ddmid.included_columns + ');', ';') AS sql_statement ,
            ddmigs.user_seeks ,
            ddmigs.user_scans ,
            CAST(( ddmigs.user_seeks + ddmigs.user_scans ) * ddmigs.avg_user_impact AS INT) AS 'est_impact' ,
            ddmigs.last_user_seek
FROM     sys.dm_db_missing_index_groups AS ddmig
            INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs ON ddmigs.group_handle = 

Open in new window

0
Hi,

We want to create a task manager using ASP.NET. Basically, when a user logs in, this person can:

1. create a task, schedule a task, put note. Similar to Outlook
2. email(s) will be sent out in the morning to remind this person what this person needs to do that day.

I know Outlook might do the trick, but we want to add more custom fields into the system. Any ideas?  thanks
0
Looking to complete the following - export ADFS configuration from an existing ADFS 2.0 with database hosted on SQL Server to a new instance of ADFS 2.0 with configuration database hosted on WID.
What tools would you recommend for backup/restore - or rather import/export. More to the point, is there a KB that goes into step-by-step process?
0
I am trying to INSERT data from a form into a table in Access using SQL in VBA.  I am getting an error telling me "Too Few Parameters - Expected 3".  

Here is my statement:
strSQL = "INSERT INTO tblHeadcountRequestRepository (LastEditedBy, NH_Region, NH_Function, NH_Office)VALUES(" & frmUser & "," & strReq_Region & "," & strReq_F3 & "," & strReq_OFC & ")"
            db.Execute (strSQL)

Here are the values from the immediate window:
INSERT INTO tblHeadcountRequestRepository (LastEditedBy, NH_Region, NH_Function, NH_Office)VALUES(unganm,Americas,URT,Burlington)

Is my SQL statement written incorrectly?  

Thanks
0
I need to create a primary key (number type) for a table that previously did not have one and then number the fields starting at 1.  Can somebody give me the ALTER STATEMENT sql code to to this?
0
To enable someone to send email directly from SQL Server, is adding execute permissions on DatabaseMailUserRole the only step?
0
hi,

any one has any problems on running SQL server data warehouse on VMWare 6.5?

usually how much CPU and RAM a typical SQL server data warehouse will takes up?
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

How can I estimate the local area network utilisation involved in remotely executing a simple SQL Server Stored Procedure ?

I have a windows application that uses ADO to execute a stored procedure on a Microsoft SQL Server 2005 database server.
The stored procedure is simple and returns a single result in an  integer output parameter.

I am executing this procedure every 10 seconds and have been asked to estimate the "network utilisation".  There are no input parameters to the stored procedure. I will be answering the question as "virtually no network utilisation" but I anticipate being asked how to justify this statement.
0
So I've inherited a somewhat small environment where I manage Active Directory but the former DBA apparently had all these scripts doing tons of things against AD and everything else in the environment.  
Problem is one of the things that's happening is we have students and alumni in this small campus. Well the script, which I believe is the one below, is moving people to the Alumni OU even though they are a student.
I can ascertain that some of this is partially SQL query but I was hoping someone with a bit of experience could help translate what is going on here and what criteria/reason is moving someone from one OU to another OU or vice versa.

From the script, I see that one important variable appears to be:
$StudentsThatShouldMove = Fill-Dataset $IntegrationConnString $Sql_StudentsThatShouldMove
   
Does that mean I have to look to the SQL side to see what's going on?
#. \\scorch01.VelCollegenet.edu\C$\Users\Public\Documents\Scripts\_CreateAcctsAndDistGroupsV4.ps1
# .\_CreateAcctsAndDistGroupsV4.ps1
#########0#########0#########0#########0#########0#########0#########0#########0
#
# # Description: Move students and alumni accounts to the correct OUs
#
# History:
#	???????? EWM Initial Creation
#   20111213 EWM Sql Server Agent Job 
#				 [Active Directory NEW students daily].[Shell Game (move stuff around where it goes)]
#				 Occurs daily at 9:00:00 PM with no end date
#
#########0#########0#########0#########0#########0#########0#########0#########0

Open in new window

0
Notice in my query below where my 2nd Join statement includes the field B.ID in functionC...that throws a "B.ID could not be bound" error. I just need to be able to pass the B.ID coming from my 1st Join statement (or even the A.ID from tableA would be alright) to the nested query that makes up my 2nd JOIN table. I considered making my nested select-query in the 2nd join its own function, but I'm not sure if that would even work inside a join statement with the ID being passed into that function as a variable (ie. "B.ID" instead of a literal ID like "1234").
SELECT A.Name, B.ID 
FROM tableA AS A 
LEFT OUTER JOIN tableB AS B ON A.ID = B.ID 
LEFT OUTER JOIN (select top 1 Car from functionC(B.ID) where color = 'green' order by orderDate desc) C 
     ON C.color = A.color 

Open in new window

0
I have been tasked to build an online audit questionnaire/survey. I am fairly new to asp.net. I want to create the questions and server controls dynamically with data from sql server. For each questions I will need to dynamically create two  (Yes/No) radio buttons. Any help would be greatly appreciated. I am using asp.net forms.
0
I have a SQL Server table with column FullName VARCHAR(300)

I have a .net import from file process that runs fine except...
Where there are accent characters
It substitutes a ? as it goes into SQL.
Exemple   'PIñERO RODRIGUEZ' goes in as 'PI?ERO RODRIGUEZ'

However..if I run a simple update...
Things update correctly in SQL and can be selected with no issues

UPDATE dbo.Individuals
SET LASTNAME = UPPER('PIñERO RODRIGUEZ')
WHERE IndividualID = 204140


Or I do something simple in SQL like...
INSERT INTO Individuals
VALUES ( UPPER('PIñERO RODRIGUEZ'))   - (Abbreviated code of course...)

Inserts fine

So...

Do I need to change my import Sub (VB.NET) ?

Possibly the "AddWithValue" should actually be this type of format???
oCom2.Parameters.Add(New SqlParameter("@" + header(k), SqlDbType.VarChar, 300)).Value = returnRecords.Rows(i).Item(k).ToString()

This is how its sendingthe insert right now
Dim returnRecords As DataTable = Session("sheetData")
Dim test As String = hdJsonData.Value
 Dim header As String() = test.Split(",")
Dim columnNames As String() = returnRecords.Columns.Cast(Of DataColumn)().[Select](Function(x) x.ColumnName).ToArray()

Using... blah...blah...blah...
oCom2.CommandText = "InsertImportContactStaging"
 oCom2.CommandType = CommandType.StoredProcedure
oCom2.Parameters.AddWithValue("@ImportID", GUID)
oCom2.Parameters.AddWithValue("@ImportRecordType", Session("ImportFileType").ToString() + "")

             …
0
Hello:

Below is my code.  As you'll notice, I have hard-coded the YEAR as '2017'.

The KBUK..GL10110 table contains the YEAR as the field YEAR1, and I have aliased the table as gl.  But, when I try to have gl.YEAR1 represent the YEAR (instead of hard-coding the year), most of the results return NULL.

Why is that, and how can I overcome it?

Thanks!

John


select gl2.ACTIVE, Accts.ACTINDX, gl3.ACTNUMST as [Account], gl2.ACTDESCR as [Account Description], 
'2017' AS [YEAR], Period.PeriodNumber, 
case when Period.[PeriodNumber] = 0 then 0 ELSE ISNULL(SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC)-
(gl.DEBITAMT-gl.CRDTAMNT), 0) END as [Opening Balance], ISNULL(gl.DEBITAMT,0) as Debit, ISNULL(gl.CRDTAMNT,0) as Credit, 
ISNULL(gl.DEBITAMT - gl.CRDTAMNT,0) as [Net Change], ISNULL(CASE WHEN gl.PERIODID = 0 THEN gl.PERDBLNC ELSE
SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC) END,0) AS [Ending Balance] 
FROM (select distinct ACTINDX from KBUK..GL00105) as Accts
CROSS JOIN (select 0 as PeriodNumber union all select 1 as PeriodNumber union all select 2 as PeriodNumber union all
select 3 as PeriodNumber union all select 4 as PeriodNumber union all select 5 as PeriodNumber union all
select 6 as PeriodNumber union all select 7 as PeriodNumber union all select 8 as PeriodNumber union all
select 9 as PeriodNumber union all select 10 as PeriodNumber union all select 11 as PeriodNumber union all
select 12 as PeriodNumber) as Period
LEFT OUTER JOIN

Open in new window

0
I am importing a file into SQL Server table

Some of the Nast names are Hispanic with the ~ over the n?  

In file it is fine
Once it is in SQL it gets replaced with a ?

AQnt ideas?
0
Hi

Is it possible to drop any key that may exist on a table in SQL using a SQL statement (not manually), or do you have to specifically know what that primary key is called?

Thanks
0
I have Microsoft SQL Server 2012 installed in a production environment.
The TempDB has grown to tak up the size of the C Drive (20GB in growth).
How can I shrink the TempDB in the production environment without impacting live users?
0
Hi

I am trying to drop a primary key in my SQL table [Machines]. The following isn't working

ALTER TABLE Machines DROP CONSTRAINT pk_Machine_Number

This is strange because with SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
I can see "Machine Number" so I am not sure what I am doing wrong
0
I have table

 USER_ID  ROLE_ID  
    1            11,32,44
    2             34, 11, 32, 44
    3             11
    4             44, 22, 32
   5             32
  6              32, 44


If i pass 11, 32 role_id in where condition then it should fetch column which contains 11 and 32 , only 11 or only 32 should not be fetched , whatever role_id  i pass all should be present, please help me with query oracle , it will really helpful for me
0
I would like to take this code and have it be reused for different stored procedures. This section of code would be called for each the the button on the form below. I have all the datatable, dbcommand, executenonquery, executescalar all in one c# class and call them when I need them I need to do this for the dataset code below

String connString = “”;

string sql = “select” – I would like to use a procedure so I can use the code in more than one place.

// create connection
Sqlconnection conn = new sqlconnection(connstring);

Try
{
Conn.open();

Sqldataadapter da = new sqldataapapter(sql, conn);

Dataset ds = new dataset();

Da.fill(ds, “”);
 
(would like to return ds.table
}
Catch
{
}
Finally
{
Conn.close();
0
Hi,

I've just been told I'm now in charge of and managing two SQL databases and have never used SQL before.

One of them we can remove as it is a backup and is no longer required. How do I safely back this up and then delete it so we can, if required, restore it?

Also: The db has a total size of 6,927Mb. 6021Mb of which is transaction log space. How do I go about reducing this log space? We are nowhere near using up the space allocated to logs but we are short on disk space on the server.

I've tried backing it up by doing a full backup and also a transaction log backup but no reduction in transaction log space.

So I'm looking for 3 things:

1) Reducing transaction log space
2) How to create a full backup that we can restore from
3) How to take the db offline and then delete it from Studio (assume Tasks > Offline, then delete)

Thanks and apologies for the noob questions but this has literally landed on my desk.
0
Dear Experts,

Based on your experience could you please advise in which scenarios worth to use this "SELECT in JOIN" technique:
SELECT
	t1.LIFNR AS VendorNumber,
	t1.NAME1 AS VendorName,
	SubQuery.BUKRS AS CompanyCode
FROM
	AP.LFA1 t1
INNER JOIN
	(SELECT AP.LFB1.LIFNR, AP.LFB1.BUKRS FROM AP.LFB1 WHERE AP.LFB1.BUKRS = 'SX1') SubQuery ON t1.LIFNR = SubQuery.LIFNR

Open in new window


instead of doing the same filtering in WHERE section?
SELECT
	AP.LFA1.LIFNR AS VendorNumber,
	AP.LFA1.NAME1 AS VendorName,
	AP.LFB1.BUKRS AS CompanyCode
FROM
	AP.LFA1
INNER JOIN
	AP.LFB1 ON AP.LFA1.LIFNR = AP.LFB1.LIFNR
WHERE
	AP.LFB1.BUKRS = 'SX1'

Open in new window


Thanks in advance,
0
I want to return the Top 5 vCount for/in each vGroup in this query, but I'm having trouble figuring out where to put it ?

		/****** Region ******/		
		SELECT	vGroup, vType, count(*) AS vCount
		FROM (
		SELECT        'Region visits' AS vGroup, Districts.name AS vType
		FROM             (v_Visits INNER JOIN Customers ON v_Visits.customerId = Customers.id) INNER JOIN Districts ON Customers.districtId = Districts.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

		/****** Brand ******/
		UNION ALL
		SELECT	vGroup, vType, count(*) AS vCount
		FROM(
		SELECT        'Brand visits' AS VGroup, Products.brandName as vType
		FROM            v_Visits INNER JOIN Visits_r_Products ON v_Visits.id = Visits_r_Products.visitId INNER JOIN Products ON Visits_r_Products.productId = Products.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

Open in new window

0

Query Syntax

48K

Solutions

15

Articles & Videos

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.