sql

SQL - create database and tables in one script

Sorry if already asked, but I can't find anything on this. I am moving something over from MySQL to SQL Server I want to have a .sql file create a database and tables within the database. After working out syntax kinks I have gotten the files to work (almost). If I run IF db_id('dbname') IS NULL CREATE DATABASE dbname it works ...

Insert from table1 to table2 on insert trigger.

I'm very new to SQL so forgive my ineptitude. I'm trying to write a trigger that, on insert to table 1, inserts that record into table 2. table 1 is [rps_lab_dev].[dbo].[Lab_Employee_Time_Off] table 2 is [dbo].[Lab_Employee_Time_Off_Detail] CREATE TRIGGER updatetrig ON [rps_lab_dev].[dbo].[Lab_Employee_Time_Off] FOR INSERT AS ... ...

When making a model using the entity designer (VS2010) how can I maintain the data?

Because as I make small changes it regenerates the the entire DB and therefore removes the data.. I suppose I could manually maintain a script that I run that inserts the test data after I regenerate the schema. Or is there a better way to go about doing this? ...

Adding MySQL alias fields together

Consider a query similar to: SELECT sum(EXPR) as total, sum(EXPR) as total2, sum(total+total2) as grandtotal FROM tablename This comes up and says unknown column total in field list. Is there anyway to reference the alias fields in a calculation without retyping the sum expression because sum(EXPR) on each side is...

Equivalent of a composite index across multiple tables?

I have a table structure similar the following: create table MAIL ( ID int, FROM varchar, SENT_DATE date ); create table MAIL_TO ( ID int, MAIL_ID int, NAME varchar ); and I need to run the following query: select m.ID from MAIL m inner join MAIL_TO t on t.MAIL_ID = m.ID where m.SENT_DATE betwee...

php and database question

I've been working on a website on my own xampp server on my computer with my own database and everything and so far it's been pretty smooth, surprisingly. Now I want to upload it to a host, and I found a free web host and I was able to upload the site through dreamweaver/ftp. I exported my DB into an SQL query and than ran that query o...

SQL SELECT: value that occur > 1

I'm trying to select duplicates from this table: snr zip 01 83 02 82 03 43 04 28 Expected result is just empty table. Cuz it got no duplicates. I've tried with this query: SELECT snr, zip FROM student GRUOP BY snr HAVING (COUNT(zip) > 1) But it says that syntax is error, and that I'm trying to query an aggregate functions, etc.. ...

Storing many bits -- Should I use multiple columns or a single bitfield column?

Hello, I am designing a User table in my database. I have about 30 or so options for each user that can be either "allow" or "disallow". My question is should I store these as 30 bit columns or should I use a single int column to store them and parse out each bit in my application? Also, our database is SQL Server 2008 and 2005 (depen...

Deadlocks - Will this really help?

So I've got a query that keeps deadlocking on me. People who know the system well can't figure out why the sproc is deadlocking, but they tell me that I should just add this to it: SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED Is this really a valid solution? What does that do? ...

Keep value of autonumber column when importing into Microsoft Access database

What I try is to import several tables programmatically from Microsoft SQL Server to Microsoft Access. Each SQL Server table has an identity column, and the corresponding Access tables, an autonumber column, too. Now I want to generate SQL scripts to copy the data from SQL Server to Access and have the autonumber colum the same value a...

SQL Simple Join and I'm stumped

I have a table with columns: JOB_NUM, HM_PH, BUS_PH, CALL1ST Where the job_num is a unique number And the HM_PH, BUS_PH, and CALL1ST columns are 10 digit phone numbers So using the order of columns above, sample data would look like: JOB_NUM, HM_PH, BUS_PH, CALL1ST ------------------------------------ 12345, 4025557848, ...

t-sql create user and grant execute on permission for stored procedures

Hi I have a script which creates a database, stored procs, views, tables, udf. I want to include a script to create a user 'user_1' and give execute permission on the database. update I tried following to create grant exec command for all stored procs use DB; go declare @permission varchar(max) select @permission = COALESCE(@permi...

How to eliminate "duplicate" URL MySQL rows which may or may not start with www?

I've been using a Drupal module called FeedAPI De-Dupe to remove duplicate records from my site's (MySQL) database. The module does a great job, but I'm still left with some "duplicates" when the URLs sometimes contain "www" and sometimes don't. The code handling the duplicate detection is: case 0: //url-based $sql = "SELECT n.nid ...

sql server query to return a value

Below is my query which is updating a record in the User table , I want the query to return the UserId which was updated, How can I do this? UPDATE USER SET GroupID = @New_GroupID FROM USER LEFT OUTER JOIN DOCUMENTS ON User.UserID = Documents.UserID WHERE (Documents.UNC = @UNC) AND...

SQL Case Statment

How do I write a CASE statement within my SELECT to do the following: I have a column called Values. This column can have the value b, c, or a. If it has the value b, I want the SELECT to return big; if c return small, and if a return large ...

SQLyog question

I'm using SQLyog and I used to Database Synchronization Wizard to make a job (which I saved, and it saved it as an xml file). However, the job doesn't show up in the Job Manager. How do I make this happen? ...

SQL how to cut off string

I have column of strings with a ctiy state and number in each. SPOKANE, WA 232/107 LAS VEGAS, NV 232/117 PORTLAND, OR 232/128 Theres many more than just that but I am wondering how either I could cut off the numbers in this column and jsut show the city and state or even better cut off the numbers and make c...

Error in phpmyadmin though runs fine in php script

Ok so this one is strange and I'm thinking there must be a problem in my sql query but I don't see anything wrong with it. What is happening is that I have a script that I am running in a php script that retrieves all categories that a user has signed up for as well as how many other people have signed up for the same category. When I ru...

ms-access record/page level locking

i am baffled as to what is happening with the database i have a split front and back end access database every user has their own front end sometimes some users are able to edit the data and sometimes not. i don't know how to explain this here are the settings: anybody know what is going on here>? ...

PLSQL WHERE date_time is in a range of dates

I have two date strings, in the format MM/DD/YYYY and would like to query an Oracle database for any records between those two dates. How do I do this succinctly and simply? ...