sql

Need help with a SQL query that returns the lasts records matching a certain criteria.

I have the following table: CREATE TABLE "posting" ( "id" integer NOT NULL PRIMARY KEY, "amount" real NOT NULL, "balance" real NOT NULL, "account_id" integer NOT NULL REFERENCES "account" ("id"), "asset_type_id" integer NOT NULL REFERENCES "asset_type" ("id") ) For this table, I manually generate ids in a w...

casting multiple integer values in sql

Hello - I would like to cast multiple variables which are coming from .net solution to integer. Something like this: I am using IN keyword for selecting more then one keyword, because I don't know how many variables are selected by the customer and I am putting them in nvarchar variable. Example: StatusID selected: 30,40,50 '30,40,5...

SQL Server 2008 data types: which ones should i use?

I am trying to figure out which data types I should use for my tables. Does someone have a very good, simple, tutorial on SQL Server 2008 datatypes (has to be practical!) ? ...

Transferring data from XML to SQL

I have Created One XML File..... Node : <Patient>Kanna</Patient> <Id>12</Id> And I transferred those values to SQL Db. Using C#.Net Now I added One More Value in Same XML File <Patient>Kanna</Patient> <Id>12</Id> <Patient>Raja</Patient> <Id>13</Id> Question Is : How to append New Value to SQL Db.... Can U Suggest Me any Idea ? ...

How do you find the ID of the parent stored procedure within a nested procedure?

If I am within a nested stored procedure, I would like to be able to get the procedure ID of the procedure that is one level up. Example: create proc procedure1 as print 'Current proc id: ' + @@PROCID print 'Parent proc id: ' + @@PROCID_PARENT --or something? go create proc procedure2 as exec procedure1 go exec procedure2...

Executing a prepared PDO statement with the like clause

I am new to PHP, and am trying to learn to use PDO to connect to a test MySQL db. I have the following: try { $db = new PDO('mysql:dbname=MYDBNAME;host=MYHOST', 'USERNAME', 'PASSWORD'); $query = "select * from books where ? like '%?%'"; $stmt = $db->prepare($query); $stmt->execute(array($searchtype, $searchterm)); } c...

Estimating SQL Transition from 2000 to 2008

I need to estimate a project to convert a SQL Server 2000 database with database replication to SQL Server 2008. Anyone have thoughts on how I can estimate the level of effort to perform the conversion? I'm thinking of outsourcing the task so that I have the expertise of someone that's been through this before. I would like to estimat...

SQL Procedure to run shapshots when the mirror is connected

Hi all, first i appreciate your help, is my first time on this forum. I don't have experience writing procedures on sql, i need to schedule a job that verify the conection status on sys.databese_mirroring, if the status is 'SYNCHRONIZED' y need drop a specific snapshot and create a new one. If not i leave the last snapshot running. I us...

limiting row output in sql

I have this code in sql: SELECT DISTINCT O.custid, P.productname, TO_CHAR(MAX((quantity) * D.unitprice) AS "Revenue" FROM corp.Orders O LEFT JOIN corp.Order_Details D ON O.orderid = D.orderid LEFT JOIN corp.Products P ON D.productid = P.productid GROUP BY O.custid, P.productname HAVING P...

Whats faster in Oracle? Small table with tree structure vs. Huge flat table.

Hi All. I'm designing an application that will use Oracle, and we have this department hierarchy that we need to map in our database. Some thing that looks like this (I'm pretty sure you all know what I'm talking about but I'll include a piece of the ERD just in case): So it will have data stored that looks like this: [1 | 0] [2 | ...

Oracle query to extract distinct routes

Burning out a brain cell... there has to be a simple way to do this. I've inherited the following tables: approval_path approval_path_steps applications application_roles requests role_approvals A user requests an application role for an application, which must go through an approval path, the steps of which are defined in approval_p...

show relationships like Access

Is there a way to show table relationships as can be done in Access? Consider two tables: Services serviceid application id Application application id application name I have already set up the diagram. When opening the table service id I want to see the related application details like in Access. Is this possible? ...

SSIS bulk insert into a single table concurrently

I have a SSIS package with a task to load an out-of-process application to bulk-insert data into one table. The problem is that multiple out-of-process applications will run when multiple files arrive at the same time. This will result in insertion failure. Can SQL Server Broker Service queue the inserting data? Does SQL Server or SS...

SQL Server 2008: BULK INSERT csv - is it possible to choose fields?

I am doing: BULK INSERT CSVTest FROM 'c:\csvtest.txt' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n') GO --Check the content of the table. SELECT * FROM CSVTest GO --Drop the table to clean up database. SELECT * FROM CSVTest GO Is it possible to insert only specific fields FROM the csv? ...

What is the real cost of a SQL transaction?

This requires a bit of background. I'm creating a web app (ASP.NET/VB.NET with SQL Server backend) that will accept an application to receive funding, and this form has contact info fields for three people. Because the information is the same for all three people (name, email, phone), I chose to store contact info in a table separate f...

sql server 2008: is it possible to specify format instead of using formatfile?

USE AdventureWorks2008R2; GO INSERT INTO myTestSkipField SELECT * FROM OPENROWSET(BULK 'C:\myTestSkipField-c.dat', FORMATFILE='C:\myTestSkipField.fmt' ) AS t1; GO i dont want to specify a formatfile!!! i just want to specify the format inline. is this possible? ...

sql server: can i insert a $ into a money field

i am inserting values like '$5.99' (or trying to insert) into a money field. it doesnt like the dollars sign i am actually doing a bulk insert from a csv file. one of the columns in the csv file has money in it with a dollar sign can you please help me figure out how to do this bulk insert with the $ sign into a money field? Msg 4864,...

sql server 2008: importing data from excel 2003 file

i have a very simple excel file that i need to import into a table in sql server 2008. one of the fields is a bit complex and i dont think it can be saved effectively to a csv, since it sometimes has comas and single quotes in it. it screwed up the formatting when i save to a csv. so i would like to try to import directly from the xls f...

Using analytics with left join and partition by

Hi guys, I have two different queries which produce the same results. I wonder which one is more efficent. The second one, I am using one select clause less, but I am moving the where to the outter select. Which one is executed first? The left join or the where clause? Using 3 "selects": select * from ( select * from ( select ...

Should I store the parent and child in a row or only the child

Let's say I'm building a car rentals application, and at a point I have to show all available cars. In the DB, I have the following tables: Brand Model Cars In Model, there is a FK to Brand, let's say Brand_id So my question is: Cars table should have Brand and Model columns? Or only a Model column given that I could get the Brand...