sql

Create sql trigger dynamically and rollback if error

I'm creating a stored procedure that will create 3 triggers (insert, update, delete) given a table name. Here is an example to illustrate the issue I'm experiencing : CREATE PROCEDURE [dbo].[sp_test] AS BEGIN BEGIN TRAN -- Create trigger 1 DECLARE @sql NVARCHAR(MAX) = 'CREATE TRIGGER test1 ON TableXML AFTER INSERT AS B...

Automatically format sql dates

What I'm trying to do is automatically change the output format of dates in my sql queries without having to specify it (PHP/Oracle). I'm using to_char(pi.date_of_birth , 'YYYY/MM/DD') as date_of_birth to change the formatting of my queries. What I'd like to be able to do is apply this to any date returned by the '*' operator. My probl...

Single Bridge Table or is there a better way?

I've been trying to find an answer for this and I don't know the best way to describe it. Basically, I have 3 source tables each with uniqueidentifier keys. We'll call these tables Skill1, Duty2, Custom3. They are not linked (easily) in any way with each other other than the fact that they are attributes of a particular job. I want t...

SQL wildcards not returning data?

I have a select statement that is used in a gridview via a stored procedure: SELECT * FROM T_Computer WHERE (ISTag LIKE '%' + @ISTag + '%') AND Status <> 'Trashed' AND Status <> 'Sold' AND Status <> 'Stored' The @ISTag, an nchar is determined by a textbox value, if the textbox is empty I have the default value set to %, which in my mi...

Android Adapter for 2 Database Sources

I have 2 Android Databases in my app. For the sake of an example, lets say Database #1 is called JOBS and has the following columns: "Jobs Primary Key" - "Job Title" - "Job Description" Database #2 is called PEOPLE and has the following columns: "People Primary Key" - "First Name" - "Last Name" - "Jobs ID Key" (Yes I know these are n...

I'm in need of a sanity check on a sql query that will return a value if it exists in the table or return a default value if it doesn't

What I'm trying to do is return a mailbox location for a select few users and a catch all mailbox location for everyone else. In postfix you can pass it a query in the of the form SELECT mailbox FROM virtual_mailboxes WHERE email = '%s' and it will replace %s with the email user it's looking for. I have a table that contains a set of ...

Powershell -like expression from a SQL LIKE expression

I have a table in my database that includes some columns that are used for LIKE expressions in SQL. The process I'm working with uses those expressions to exclude certain SQL objects from processing. I'm now putting similar code in Powershell, but I'd like to keep the columns consistent. I'm going to need to do something along the lines...

JPA left join to find unused entries

I'm sure I'm being stupid but I can't seem to figure this one out... I have two tables: department( did, name ) employee( eid, first, last, did ) they have corresponding entities JPA managed entites Department and Employee. Employee has a Deparment field, Department doesn't maintain an Employee list. What I want to do though is find a...

Only inserting a row if it's not already there

Hello, I had always used something similar to the following to achieve it: INSERT INTO TheTable SELECT @primaryKey, @value1, @value2 WHERE NOT EXISTS (SELECT NULL FROM TheTable WHERE PrimaryKey = @primaryKey) ...but once under load, a primary key violation occurred. This is the onl...

SELECT IN for a large set

In my web application, I want to find out which of a user's friends on Twitter are already existing on the system... Currently what I am doing is getting the list of Twitter IDs the user is following (Twitter API returns the IDs 5000 at a time), and doing: SELECT userId FROM users WHERE userId IN (COMMA_SEPARATED_LIST_OF_IDs); I don't...

how to insert multiple checkbox list values into database in single column using c#

i use this to select one checkbox to isselected column how i convert this to multi checkboxlist to single column i use many ways more than 3 days without success private void BindCheckBoxList() { DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection(GetConnectionString()); try ...

distinct() function (not select qualifier) in postgres

I just came across a SQL query, specifically against a Postgres database, that uses a function named "distinct". Namely: select distinct(pattern) as pattern, style, ... etc ... from styleview where ... etc ... Note this is NOT the ordinary DISTINCT qualifier on a SELECT -- at least it's not the normal syntax for the DISTINCT qualifier...

Combine multiple rows in table into 1 resultset row

I have a table that stores records like this: name | stat | value -------------------------- object1 | stat1 | val1 object1 | stat2 | val2 object1 | stat3 | val3 object1 | stat4 | val4 But I would like to query the data so it returns rows like this name | stat1| stat2| stat3| stat4 ------------------------------------- ob...

Problem updating table using IN clause with huge list of ids

Hi I am having a problem when trying to update a table using an IN clause, I have a big list of clients that should be updated 4500+. Update table set columnA = 'value' where ID in ( biglistofids ) //biglistofids > 4500 ids I am getting this error "String or binary data would be truncated." I tried the same script with fewer ids le...

MYSQL CONCAT() is failing but I don't know why...

Need some help understanding why my concat() is failing and how to fix it. I've never used concat() but came accross a situation where I needed to get a unit_nbr from another table and concatenate another field to it to make one field in the main select. Here's the CONCAT() I used: CONCAT(b.name, ' - ', unit_nbr) as lease_name The out...

Combine XML from T-SQL

Hi All, I have two separate tables TVs and Receivers that I am using the FOR XML PATH commands to build XML off of. My issue is that I want to combine the output of my TV XML Build with my Receiver XML Build to create one XML output. So I would have something like this(Which allows me to keep the TVs and Receivers Tags Separate within...

Scripts for moving schema changes from development database to production database

I'm trying to head this one off at the pass. I've got two database servers (DEV and PRD) and I have my database on the DEV server. I am looking to deploy v1 of my application to PRD server. The question is this: Say in two months, I am ready to ship v1.1 of my application, which introduces two new VIEWS, six new fields (three fields in...

Dynamic Linked Server Query

Is it possible to construct a dynamic query to for a linked server (and if so how)? For example: @linkedServer varchar(50) @var1 varchar(10) @var2 varchar(10) select * from openquery(@linkedServer, 'select c1,c2 from t1 where p1 = @var1 and p2= @var2...

Oracle to_date function with quarter-format

I need to find some records created in a range of quarters. For example, I'm looking for all records created between the 4th quarter of 2008 and the 1st quarter of 2010. I have this in my WHERE-clause: ...and r.record_create_date between to_date('2008 4','YYYY Q') and to_date('2010 1','YYYY Q') but Orac...

sql direct way to get number of rows in table

Hi again people of stackoverflow. I have a routine that has a step that I find unnecessary lets say you want to get all the images from a gallery, and limit a certain number of images per page. $db = PDO object $start = (pagenum x images per page) $limit = (images per page) $itemsdata = $db->query("SELECT id,name FROM gallery LIMIT $sta...