SQL 2012 :: Select Statement - Check For All Same ID And Return Records

Jan 9, 2015

My example data as this:

ID code1 code2
1 a m
1 b n
1 c T
2 d m
2 e n
2 f n
3 g m
3 h n
3 i T

I would like to have a select stmt that check for all same ID and code2 does not contain T, then return the records

For example data: the query should return
2 d m
2 e n
2 f n

Is it possible to do this?

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

SQL Server 2012 :: Select Statement - Finding Duplicate Records

Feb 18, 2014

I am trying to build a select statement that will

1). find records where a duplicate field exists.
2.) show the ItemNo of records where the Historical data is ' '.

I have the query which shows me the duplicates but I'm stuck when trying to only show records where the Historical field = ' '.

Originally I thought I could accomplish this goal with a sub query based off of the original duplicate result set but SQL server returns an error.

Below you will find a temp table to create and try in your environment.

create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int

[Code] ....

Ultimately I need a result set that returns 'ATMR10 MFZ N', and 'QBGFEP2050 CH DIS'.

View 3 Replies View Related

Select Top Needs To Return 2 Records Only

Jan 10, 2013

I have table that I need to retrieve the top 2 records, the issue is I have 3 records with the same date, but I only want the first 2. Each record looks something like this.

id, team, date, setnr, series
1, 3, 1/1/2013, 1, 1102
1, 3, 1/1/2013, 2, 1231
1, 3, 1/1/2013, 3, 1023
1, 3, 1/5/2013, 4, 1024
1, 3, 1/5/2013, 5, 1123
1, 3, 1/5/2013, 6, 1232
2, 2, 1/1/2013, 1, 1032
2, 2, 1/1/2013, 2, 1221
2, 2, 1/1/2013, 3, 1023
2, 2, 1/5/2013, 4, 1231
2, 2, 1/5/2013, 5, 1112
2, 2, 1/5/2013, 6, 1231

I have to be able to add the series up of only the first two records for each id based on date. Here is a sample query

select sum(series), date from table group by date order by sum(series) desc

This gives me the total for all three and gives it to me in descending order. I need the records for set 1 and 2 of each of the Id. There are many records but the date and the setnr doesn't duplicate.

View 1 Replies View Related

SQL Server 2012 :: Check Records Which Have Negative Capacity Value

Jul 17, 2015

I have a table with a column Capacity which is char(10) and gets populated from user files. I want to check records which have negative Capacity value. So i first checked if its numeric and then for negative.

select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0

BUT still it checks for char fields too giving errors like - Conversion failed when converting the varchar value 'asdf ' to data type int.

View 9 Replies View Related

Select * From Table Does Not Return All Records

Aug 18, 2005

I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.

I used Express Manager ( XM ) to try the select statements. That is how I isolated the problem. Even using a "Select * from timecards where employee = 'test' " returns the inserted test records. I found that if I use a WHERE or ORDER BY clause in the SELECT statement to .fill the .net dataset, all records are returned.

I am familiar with DB2 but I am a newbie at VB.NET and MSSQL

Any suggestions?

Thanks!

GordonG

View 1 Replies View Related

Select Statement To Check Nullable

Mar 6, 2008

greetings

Sir i want to check and use the select statement to select which field have a nullable=false in a table

View 1 Replies View Related

Data Access :: Select After Check Previous Records

May 5, 2015

I have two tables. Users and records. I need to select only the users that not has lines recorded in the other table. How could I do that?

Example:

ID| Name| Access 1|Access 2|
----------------------------
1 | Axel   | True         |False   |
2 | Ivan  | False        |False   |
3 | Bob  | True          |False   |
4 | Sue  | False         |False   |

ID| Points| Month| Year|User_1|User_2|
--------------------------------------
1 |  2      |    5 | 2015|   2  |   1  |
2 |  5      |    5 | 2015|   2  |   1  |
3 |  1      |    5 | 2015|   3  |   1  |

Then I want to run a select in the users table, only with the users that hasn't records in the second table.

In the example, the second table has User_1 as the user who receives the points and the User_2 is the user who give the points. Then I would know what user didn't receive 'points' yet.

View 3 Replies View Related

Return SP In A SELECT Statement Column

Jan 13, 2005

I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement.


I want the following output:


CatID | CatTitle | CatTree
001 | News | exec sp_DisplayTree(@CatID)


My code I tried doesn't work:


SELECT
C.CatID As CatID,
C.CatTitle As CatTitle,
CatTree = (exec sp_DisplayTree C.CatID)
FROM
Cats As C WITH (nolock)



I cannot find a solution to my solution, please help...

View 3 Replies View Related

SQL Server 2012 :: Return Random Records In A Table-valued Function?

Dec 19, 2013

My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.

I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).

CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE

[Code] ....

But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:

Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.

I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...

The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)

but that's clearly not working. Is there a way to do this without resorting to a cursor?

View 9 Replies View Related

Return Number Of Rows In Select Statement As Value

Sep 3, 2007

Can anyone just point me in the right direction.  All I want to do is add some T-SQL to an existing stored procedure to return the number of rows selected into a return value.Does anyone know how to do this? 

View 4 Replies View Related

Return Select Statement Or Values Using SqlDataSource?

Sep 21, 2007

Hello all,
 I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be?
 I have a SqlDataSource"
 <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)"
UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Action" Type="Byte" DefaultValue="2" />
</UpdateParameters>
</asp:SqlDataSource>
 And my SP:/* 09-19-07 Used to update affiliate profile */

CREATE PROCEDURE spUpsertAffiliateProfile
@Action tinyint,
@AffiliateId int,
@MemberId int = -1,
@CategoryId int,
@WebAddress varchar(50),
@Email varchar(50),
@Comments varchar(1500)
AS

SET NOCOUNT ON

-- Find errors first, check is not needed if deleting
IF @Action <> 3
IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%')
BEGIN
SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END
IF @Action = 1
IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId)
BEGIN
SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END


IF @Action = 1 AND @AffiliateId = 0-- insert
BEGIN
INSERT INTO tAffiliateMaster
(member_id, category_id, profile_web_address, profile_email_1, comments)
VALUES
(@MemberId, @CategoryId, @WebAddress, @Email, @Comments)

SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id
RETURN
END

ELSE IF @Action = 2 AND @AffiliateId > 0-- update
BEGIN
UPDATE
tAffiliateMaster

SET
category_id= @CategoryId,
profile_web_address=@WebAddress,
profile_email_1=@Email,
comments=@Comments

WHERE
affiliate_id = @AffiliateId AND member_id = @MemberId

SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id
RETURN
END

ELSE IF @Action = 3 AND @AffiliateId > 0-- delete
BEGIN
DELETE
tAffiliateMaster

WHERE
affiliate_id = @AffiliateId

SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id
RETURN
END
GO

 My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event?
 Thanks!
 

View 3 Replies View Related

Return Variable Name As Part Of Select Statement.

Mar 9, 2008

hey all,

I have the following query:

ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]

AS
BEGIN

DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)

DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)


SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count

FROM article

JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id

GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name

HAVING article_id = @article_id

END
GO

as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.

I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.

But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.

And i cant add a join, because it would return the amount of rows of the comment...

unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?

Cheers

View 6 Replies View Related

Getting A Variable That Has A SELECT Statement To Return Results

May 21, 2006

I have concatenated a long Select Statement and assigned it to a variable. (i.e.)

@a = Select * from foo ---Obviously mine is much more robust

How do I execute @a to actually Select * from foo?

View 3 Replies View Related

Overloading Select Statement Return Values

Nov 14, 2007

Hey guys,

This is what I think and hope is a fairly straight forward SQL question

Essentially, I have a table which has the following columns that are relevent to my question:

PROJID
ACTIVITY_NAME
COMPLETION_DATE

Rows in this table are, for example:

PROJID ACTIVITY_NAME COMPLETION_DATE
1 Prepro 10/12/2007 3:42:30
2 Prepro 10/13/2007 9:16:27
2 QA 10/13/2007 10:00:01
2 Delivery 10/14/2007 09:31:12
etc.

So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that).

(Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID). In other words, ultimately, I would like to return something like this:

PROJID LAST_ACTIVITY
----------------------------------------------
1 Prepro
2 Delivery

where the activity order (in this case) is Prepro, QA, Delivery. So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro

I really appreciate the help

Thanks,
Steve

View 3 Replies View Related

SQL Select Statement To Select The Last Ten Records Posted

Aug 6, 2007

SELECT Top 10    Name, Contact AS DCC, DateAdded AS DateTimeFROM         NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database.  As you know each day someone could add one or two records, how can I write it show the last 10 records entered.

View 2 Replies View Related

SQL 2012 :: Can INSERT Statement Also Return All Columns Inserted As Result Set

Jun 2, 2014

Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?

View 2 Replies View Related

How To Write A Select Query To Return Records In A Many-to-Many Relationship - Junction Table???

Oct 26, 2006

Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!  

View 6 Replies View Related

Return NULL Values In SELECT Statement With INNER JOIN ?

May 16, 2005

If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows. 
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value.  For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank.  Is that possible?

<code>
    Sub BindSearchGrid()
        Dim searchUnitID As String
        Dim searchQuery As String
        searchUnitID = tbSearchUnitID.Text
        lblIDNum.Text = searchUnitID
        searchQuery = "SELECT * FROM Assets " & _
        "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
        "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
        "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
        "INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
        "INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"

        Dim myReader As SqlDataReader
        myReader = Data.queryDB(searchQuery)
        While myReader.Read
            If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
            If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
            If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
            If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
            If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
            If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
            If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
            If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
            If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
            If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
            If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
            If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
            If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
            If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
            If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
            If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
            If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
            If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
            If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
            If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
            If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
            If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
            If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
            If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
            If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
            If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")

        End While
    End Sub</code>

View 1 Replies View Related

SELECT Statement To Return Table Column Names

Aug 16, 2004

Is there a SELECT statement that will return a the column names of a given table?

View 5 Replies View Related

T-SQL (SS2K8) :: Stored Procedure And SELECT Statement Return Different Results

Dec 4, 2014

I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:

USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

When I do this in the same SSMS window:

exec mobile.GetAllMobileDeviceUsers

SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION

[Code] ....

I get two result sets. The first is 422 rows; the second is 467 rows. Why?

View 4 Replies View Related

Transact SQL :: Carriage Return In Email Body Within A Select Statement

Jul 2, 2015

I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:

ALTER TRIGGER [dbo].[SendNotification]
ON [dbo].[TicketsHashtags]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[code]....

View 2 Replies View Related

Select Statement To Return Most Current Syscreated Date On Duplicates

Jun 29, 2006

I have a db that has duplicate customer records.

If I run the following select statment against the appropriate table it returns the duplilcate records in the result set. However, from this list I want to add an additional select statement embedded into the query that will actually return only those records with the most current syscreated date.

Example of script I'm using---

select cmp_fadd1, syscreated, cmp_name, cmp_code
from cicmpy
where cmp_fadd1 in (select cmp_fadd1
from cicmpy
group by cmp_fadd1
having count(1) = 2)
order by cmp_fadd1,syscreated desc

The results is:

Address Syscreated date Customer
1622 ONTARIO AVENUE 2005-06-15 22:19:45.000 RELIABLE PARTSLTD
1622 ONTARIO AVENUE 2004-01-22 18:10:05.000 RELIABLE PARTS LTD
PEI CENTER 2006-01-05 22:03:50.000 P.G. ENERGY
PEI CENTER 2004-01-22 17:57:56.000 P.G. ENERGY

From this I want to be able to select ONLY those records with the most current syscreated date or those records with 2005-06-15 and 2006-01-05

Can anyone help me with creating this?

Thanks

Cyndie

View 4 Replies View Related

Limiting The Records In SELECT Statement

Aug 19, 2006

Hi..

As everyone knows that there's a keyword in MySQL that is used to limit the records in SELECT statement as

SELECT * FROM Table LIMIT 40,10

means that select from record 40 to onward 10 records.

In SQL Server it is a long way with nested select ... can someone provide an alternate or we have to use the Nested SELECT?



Thanks

View 3 Replies View Related

Mark Duplicate Records In A Select Statement ?

Aug 22, 2007

I have a requirement to mark duplicate records when I pull them from the database.
However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.
The code I have below creates a column called Dupes but marks all the duplicates - including the first one.
Is there a way to only mark the 2nd, 3rd, 4th etc record ?
SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,
Dupes = (select count(id) from  CallLogswhere  (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)AND DealerId= 'hdsh' AND CSRStatus IS NULLand datediff(d, logdate, getdate()) <= 21),
FROM CallLogs cl left Join CallStatus cs on cs.Id = cl.CSRstatusleft Join CallStatus cs2 on cs2.Id = cl.Customerstatus Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)And DealerId='hdsh'And Logdate Between '08/01/2007' And '08/31/2007'

View 2 Replies View Related

SELECT Statement Listing Joined Records

Jan 18, 2005

I've searched high and low for info on how to do this... If anyone has an idea, I'd really appreciate it.

I have three tables: PEOPLE, PROJECTS, COMMENTS. I want users from the PEOPLE table to retrieve a list of PROJECTS and be able to add/edit COMMENTS on those projects.

The tables look like:

PEOPLE
people_id (primary key)
first_name
last_name

PROJECTS
projects_id (primary key)
project_title
project_summary

COMMENTS
comments_id (primary key)
projects_id (foreign key)
people_id (foreign key)
comment_detail

I'd like to be able to output something like what I have below, but I don't know how to loop over the comments/people within the select statement for the projects.

DESIRED OUTPUT

Project #1
Comment #1 by Person #1
Comment #2 by Person #3
Comment #3 by Person #8

Project #2
Comment #1 by Person #2
Comment #2 by Person #3
Comment #3 by Person #6

Etc...

I've done it before by just listing the projects and then providing a detail page with all the comments, but it's much less confusing to access all the comments from the same page, grouped by project.

Thanks in advance.

View 1 Replies View Related

SQL Server 2012 :: Inserting Dummy Records Using Loop Statement

Jul 17, 2015

I have the following attributes in this Table A.

1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)

I am trying to create 1000000 dummy records in this this table A.How do i go about do it? I would like my data to be something like this

LOCATION_ID
1

Serial Number
SN-01

KeyID
E1210

Reference_Address
83

SourceTime
2015-05-21 00:00:00 000

SourceValue
6200

View 7 Replies View Related

T-SQL (SS2K8) :: Getting Records In N-Relation Table In One Select Statement?

Jun 9, 2014

I have 2 tables in a 1: n relation. How can i get a select statement that the field in the n-relation with outputs, separated by a semicolon; Example: One person have many Job Titles

Table1 (tblPerson)
Table2 (tblTitles)
1, "John", "Miller", "Employee; Admin; Consultant"
2, "Joan", "Stevens", "Employee, Software Engineer, Consultant"
and so on .... 1 in select statement:

View 1 Replies View Related

UPDATE Records In 1 Table With Result Of Select Statement

Jun 12, 2014

I want to update records in 1 table with the result of a select statement.

The table is called 'MPR_Portfolio_Transactions' and contains the following fields:

[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]

And the select statement is like this:

SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE

This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'

the key is PTR_SEQUENCE

View 3 Replies View Related

SQL Server 2012 :: Select Records Based On Year

Aug 17, 2015

I am trying to select records based on a year.

DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year

--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear

View 5 Replies View Related

SQL Server 2012 :: Select Records Based On Year?

Aug 17, 2015

DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear

View 1 Replies View Related

SQL Server 2012 :: Using Merge Statement Output Records To Rebuild Table On Specific Date

Mar 5, 2015

I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.

Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.

View 2 Replies View Related

SQL 2012 :: Set Default Value For SP Parameter Using Select Statement

Dec 19, 2014

Is there a way to set a default value for a sp parameter using a select statement(see code bellow)

ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int,
SELECT @top = COUNT(col1) FROM Event

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved