T-SQL (SS2K8) :: Update A Field In 3rd Table From 2 Joined Tables

Jul 29, 2015

I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.

UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
FOR XML PATH('')),1,1,'')
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a

I should end up with something like this in the con_mailings field:

'Mailing1, Mailing2, Mailing3'

View 9 Replies


ADVERTISEMENT

Update On Joined Field

Jun 6, 2006

Is it possible to update the filed used in the inner join
 
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.name
 

View 1 Replies View Related

T-SQL (SS2K8) :: Delete And Merge Duplicate Records From Joined Tables?

Oct 21, 2014

Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?

SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
, A3z103st State

[code]...

View 1 Replies View Related

Getting An Average From Field Across Joined Tables

Jun 11, 2008

I'm managing an amature online university and I've been charged with creating a deans list. I have a table for exam results for each course.. currently totaling 5. I have an employeeID column and a total_points column in each table. Sooooo I need to join all the tables and get an average for total_points where the employeeID matches across tables. I have no idea how to write this select.. any help?

View 1 Replies View Related

T-SQL (SS2K8) :: Get Rows And Sum In Joined Table?

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount, where I want purchasedamount to be the sum of the `amount` for THAT item, based on giftregistrypurchases.itemid=giftregistryitems.id:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here's my table definition and data:

USE [tt]
GO
/****** Object: Table [dbo].[giftregistry] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistry](
[id] [int] IDENTITY(1,1) NOT NULL,

[code]....

Desired results:

purchasedamountidregistryidtitleogimgdescriptionURLamountpricecreatedate

[URL]

View 9 Replies View Related

How To Update A Row Based On Left-joined Tables?

May 12, 2008

Each row of my datagrid comes from two tables, A and B, which are (left) joined: not every row from table A has a corresponding row in table B. I think this is quite a common scenario.If I want to edit a row in my datagrid which contains data from both Table A and Table B then presumably I can just use an UPDATE statement behind the scenes.But what happens if I want to edit a particular row in the datagrid which contains data from Table A but no corresponding data from table B? I can't use an UPDATE statement because the record in Table B doesn't yet exist. So what do I do?Does anyone know the answer to this, or could you point me to a good tutorial please? 

View 4 Replies View Related

Update Query When Joined Tables Are Involved

Sep 30, 2013

I have a query written that filters on joined table data. The SELECT looks like this:

SELECT *
FROM tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
ORDER BY a.bol_number DESC

If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:

UPDATE tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
SET accrueamt='1348'
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')

View 4 Replies View Related

Set-based Update - Table Joined On Itself

Dec 15, 2005

Guys - sorry for the long post - hope it's clear

DDL/DML below

I want to update the startdate column (for all rows) so that when period is 0 then the new value
is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the
enddate column for the row of the previous column (with the same freq)

ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq

create table #periods (period int , startdate datetime , [enddate] datetime , freq int)
insert #periods ( period , startdate , enddate , freq)
select 0 , '01-Jan-1900' , '31-Jan-2001' , 1
union all
select 1 , '01-Jan-1900' , '28-Feb-2001' , 1
union all
select 2 , '01-Jan-1900' , '31-Mar-2001' , 1
union all
select 3 , '01-Jan-1900' , '30-Apr-2001' , 1
union all
select 4 , '01-Jan-1900' , '31-May-2001' , 1
union all
select 0 , '01-Jan-1900' , '31-Jan-2002' , 3
union all
select 1 , '01-Jan-1900' , '28-Feb-2002' , 3
union all
select 2 , '01-Jan-1900' , '31-Mar-2002' , 3
union all
select 3 , '01-Jan-1900' , '30-Apr-2002' , 3
union all
select 4 , '01-Jan-1900' , '31-May-2002' , 3

select * from #periods -- gives

periodstartendfreq
01900-01-01 00:00:00.0002001-01-31 00:00:00.0001
11900-01-01 00:00:00.0002001-02-28 00:00:00.0001
21900-01-01 00:00:00.0002001-03-31 00:00:00.0001
31900-01-01 00:00:00.0002001-04-30 00:00:00.0001
41900-01-01 00:00:00.0002001-05-31 00:00:00.0001
01900-01-01 00:00:00.0002002-01-31 00:00:00.0003
11900-01-01 00:00:00.0002002-02-28 00:00:00.0003
21900-01-01 00:00:00.0002002-03-31 00:00:00.0003
31900-01-01 00:00:00.0002002-04-30 00:00:00.0003
41900-01-01 00:00:00.0002002-05-31 00:00:00.0003



Desired result
select * from #periods -- gives

periodstartendfreq
02000-12-01 00:00:00.0002001-01-31 00:00:00.0001
12001-01-31 00:00:00.0002001-02-28 00:00:00.0001
22001-02-28 00:00:00.0002001-03-31 00:00:00.0001
32001-03-31 00:00:00.0002001-04-30 00:00:00.0001
42001-04-30 00:00:00.0002001-05-31 00:00:00.0001
02000-12-01 00:00:00.0002002-01-31 00:00:00.0003
12002-01-31 00:00:00.0002002-02-28 00:00:00.0003
22002-02-28 00:00:00.0002002-03-31 00:00:00.0003
32002-03-31 00:00:00.0002002-04-30 00:00:00.0003
42002-04-30 00:00:00.0002002-05-31 00:00:00.0003


/*
I know I need a case statement to test for column 0 and to join the table on itself and have put something together
but it fails for column 0 and updates to NULL - I think it must be to do with the join ??

This is what I've got so far :

UPDATE PA1
SET
PA1.Startdate =
CASE
WHEN PA2.period = 0
THEN
2000-12-01 00:00:00.000
ELSE
PA1.Enddate
END
FROM #periods AS PA1
JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1

Any help gratefully received as always
*/

View 5 Replies View Related

How To Update Joined Table Using Instead Of Triggers

Jun 25, 2007

Hi
i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data
some people says you can update joined tables using instead of triggers
how is that ?is there any example ?

thanks in advance.

View 4 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006

Hello

Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!

Worf

View 2 Replies View Related

UPDATE Into JOINed Table - Access Vs. SQL Server Syntax

Sep 13, 2007

I am trying to get a SQL statement to work with both Access 2000 and SQL Server 2000.

The statement that works in SQL Server is:

---
UPDATE [myTable2]

SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0

FROM [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]

WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---


(names have been changed to protect the innocent)


The statement that works in Access is:

---
UPDATE [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]

SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0

WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---


It seems that neither will accept the other format. Can anyone suggest how I can rearrange the statement so that it works in both?

View 2 Replies View Related

Tables Joined On Multiple Columns, Exclude Records Found In Table A

Nov 7, 2006

I'm using SQL server 200

Table A has columns CompressedProduct, Tool, Operation

Table B in a differnt database has columns ID, Product, Tool Operation

I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.

But I want to be able to select any records that are in table A but not in Table B.

ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.

This code return all the records from A. I need to filter out the records found in Table B.

---------------------------------------------------------------------------------------------------------------------------------

SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
JOIN tblProduct p ON ts.ProductID = p.ProductID
JOIN tblTool t ON ts.ToolID = t.ToolID
JOIN tblOperation o ON ts.OperationID = o.OperationID
WHERE ts.ToolID=66
') oq
LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation

View 1 Replies View Related

T-SQL (SS2K8) :: Update Field Value For Numbers Only

May 13, 2014

Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:

Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223

Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999

There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.

View 8 Replies View Related

T-SQL (SS2K8) :: Update A Field With Condition

Aug 10, 2014

Table A - data example
Idno Entity Flag
1 1 y
2 1 n
3 1 y
4 1 y

Table B - data example
Idno Entity
1 1
2 1
3 1

Table C - data example
Idno Entity
4 1

I want to update the value flag in table A.Update flag from n to y if idno A in table B and and idno A not in table C, so from table A above, first record and 3rd record remain unchange but 2nd record, flag will be changed from n to y. 4th record where idno A not in table b and in Table C so flag will change from y to n. is it possible, or do i need to use the "fetch" method to compare record by record.

View 4 Replies View Related

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query

Oct 15, 2007

Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy



Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View 1 Replies View Related

T-SQL (SS2K8) :: Update 2 Columns From 2 Different Tables

May 15, 2015

I have task where i need to update the 2 columns from 2 different tables. I need to get one column from one table and update to the other table.If the column name do not match....

View 4 Replies View Related

T-SQL (SS2K8) :: Update DateTime Field With Date-inserted From Previous Record?

Jan 14, 2015

My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.

While trying to get to the correct previous record, I wrote the query below.

P.S. The attached .txt file includes a create and insert tbl_tmp sampling.

select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,

[code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Procedure That Create Views With Table Name And A Table Field Parameter?

Aug 4, 2015

I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).

However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.

Below code.

ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

[code]....

View 9 Replies View Related

Update Field In Two Tables

Jul 25, 2006

This is my curren code for updaing ESN number. But this is incorrect.

<asp:SqlDataSource ID="ESNTrackingDataSource"
UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId"
OnInit="ESNTrackingDataSource_Init"
OnUpdating="ESNTrackingDataSource_Updating"
OnUpdated="ESNTrackingDataSource_Updated"
runat="server">
<UpdateParameters>
<asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>

this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.

This is my query that returns the ones are not assigned to and
Asset

SELECT DISTINCT EsnId, EsnNumber
FROM dbo.ESNTracking
WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))

View 1 Replies View Related

T-SQL (SS2K8) :: Error Altering Field Spec In A Table

Apr 18, 2014

I have a db1.tPersonJobHist where I have two columns

[WorkflowCoordinatorFlag] [dbo].[shrsFlag] NOT NULL,
[HRBusinessPartnerCode] [dbo].[shrsCode] NULL,

Our developer mistakenly made HRbusinesspartnercode field as nullable. He now wants to change it to Not Nullable.

So there were 4 rows where the values were not null(not sure how). We edited those rows and changed those values to NUll. Now we have no Nulls in that column.

So we brought up the table designer and made the change but got the following error

'tPersonJobHist' table
- Unable to modify table.

Cannot insert the value NULL into column 'HRBusinessPartnerCode', table 'BD6578.dbo.Tmp_tPersonJobHist'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Question is why wouldn't it let me alter the table design. and what is tmp_tpersonjobhist.?

How can I make this column Not-nullable.

View 2 Replies View Related

T-SQL (SS2K8) :: Find Primary Key Table Name And Field Name Of Foreign Key

Apr 10, 2015

How can i find the primary field name and primary table name of a given foreign key.

For example

Table A:
IDa
Column1
Column2
Column3

Table B:
IDb
column1
column2
column3 <---- this is foreign key to table A's IDa

What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?

View 4 Replies View Related

Optional Parameter On Joined Field Which Could Be Null?!

Jul 20, 2005

I have two tables: eg. a person-table (no nulls allowed), with an idand so on, and a person_course table (an intermediate table in amany-to many relationship between person table and courses tables),with two fields person_id and course_id.But I want to make ONE multipurpose stored procedure, which has ONLYoptional parameters on all fields in the person table AND the fieldcourse_id in the person_course table.I have no problems making optional parameters on the person table (eg.P.ID=ISNULL(@PersonID, P.ID ) ) BUT the problem is, when I try to addan optional parameter on the field course_id it dosn't produce theright results. Some times the course_id is null (because some personshavn't joined a class yet).Is there a way around it?Ole S. Pedersen

View 1 Replies View Related

T-SQL (SS2K8) :: Cursor Table Update

May 6, 2015

DECLARE @id VARCHAR(10)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [ServersList] AS 'ID'
FROM dbo.Servers

[code]...

How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.

View 1 Replies View Related

Transact SQL :: Conditional Update A Field From Multiple Tables

Sep 9, 2015

Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.

View 13 Replies View Related

Update Joined View

Aug 15, 2014

I create a view with 2 joined tables:

with cte as (
select
DataID,
Name,
SubType,
FileType,
MimeType,
VersionID,
Version

from dtree A1, dversdata A2
where A1.dataid=A2.Docid And A1.Subtype='144'
AND
A2.mimetype='application/news-message-id'
update cte set MimeType = 'application/x-outlook-msg', Subtype=749

what I want to do is to update two columns. Both are from different tables and I get an error..

View 1 Replies View Related

T-SQL (SS2K8) :: Update One Column For One Table From Another Table?

Jul 8, 2014

I have to tables say 'employee1' and 'employee2'

employee1 has lastname
employee2 has firstname, lastname

i have to update firstname in employee1 from firstname in employee2 table and the common field for both tables is 'lastname'

View 8 Replies View Related

Syntax Error With Joined Update

Mar 24, 2008

Hello all,
 Iv been making a lot of progress on my first functional webapp, but I cannot get this bit of code to work correctly.  I think my UPDATE SQL statement is where the problem is.  It works fine the first time through when there is no Session("estimateid") set, but after that is set it gives me error this on line 40:
Incorrect syntax near '('.
 1 Dim CustID As Integer
2
3 Dim DbConnection As SqlConnection
4 DbConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("harringtonairdb").ConnectionString)
5 DbConnection.Open()
6 Dim DbCommand As SqlCommand
7
8 If Session("estimateid") = 0 Then
9 Dim DbSqlInsert As String
10 DbSqlInsert = "INSERT INTO tblcustomers (strname, strAddress1, strAddress2, strCity, strState, strZip, strPhone, strEmail, strContact) VALUES (@name, @address1, @address2, @city, @state, @zip, @phone, @email, @contact)" & "SELECT @@IDENTITY AS CustID"
11 DbCommand = New SqlCommand(DbSqlInsert, DbConnection)
12 Else
13 Dim DbSqlUpdate As String
14 DbSqlUpdate = "UPDATE tblcustomers SET (strcustname=@name, straddress1=@address1, straddress2=@address2, strcity=@city, strstate=@state, strzip=@zip, strphone=@phone, stremail=@email, strcontact=@contact) JOIN tblestimates ON pkcustomerid WHERE pkestimateid=@estimateid"
15 DbCommand = New SqlCommand(DbSqlUpdate, DbConnection)
16 DbCommand.Parameters.AddWithValue("@estimateid", Session("estimateid"))
17
18 End If
19
20 DbCommand.Parameters.AddWithValue("@name", txtCustName.Text)
21 DbCommand.Parameters.AddWithValue("@address1", txtCustAddress1.Text)
22 DbCommand.Parameters.AddWithValue("@address2", txtCustAddress2.Text)
23 DbCommand.Parameters.AddWithValue("@city", txtCustCity.Text)
24 DbCommand.Parameters.AddWithValue("@state", txtCustState.Text)
25 DbCommand.Parameters.AddWithValue("@zip", txtCustZip.Text)
26 DbCommand.Parameters.AddWithValue("@phone", txtCustPhone.Text)
27 DbCommand.Parameters.AddWithValue("@email", txtCustEmail.Text)
28 DbCommand.Parameters.AddWithValue("@contact", txtCustTimes.Text)
29
30
31 If Session("estimateid") = 0 Then
32 CustID = Convert.ToInt32(DbCommand.ExecuteScalar())
33 DbCommand.Dispose()
34 Dim DbSqlInsert As String
35 DbSqlInsert = "INSERT INTO tblestimates (fkcustomerid) VALUES (@customerid)" & "SELECT @@IDENTITY AS EstimateID"
36 DbCommand = New SqlCommand(DbSqlInsert, DbConnection)
37 DbCommand.Parameters.AddWithValue("@customerid", CustID)
38 Session.Add("estimateid", Convert.ToInt32(DbCommand.ExecuteScalar()))
39 Else
40 DbCommand.ExecuteNonQuery()
41 End If
42
43 DbConnection.Close()
44 DbCommand.Dispose()
 

View 3 Replies View Related

T-SQL (SS2K8) :: Generating One Table From Two Tables

Jan 14, 2015

There are two tables as below:

Table 1
IDValue
F001A,B,C
F002B,C,D
F003A,C

Table 2
IDValue
D001A
D002B
D003C
D004D

what is best way to generate one table as below:

New table
F001D001
F001D002
F001D003
F002D002
F002D004
F002D003
F003D001
F003D003

View 4 Replies View Related

Joining Two Joined Tables

May 15, 2002

Hi all -

Heres the deal,

I have six tables that are loosly related,

four in one join, two in the other....

in the first join there is a column called commentID -
and is based on a join of the four tables, the results of the
query is based on a where clause

and this needs to be tied into the second set of joined
tables (they are joined on a commentID themselves)

I have both the queries and joins working -

The primary query (4 joined tables) needs to do a lookup on the second (2 joined tables) and add its selected value, into the final result set....



thanks a million for the help!!

take care
tony

View 1 Replies View Related

Problem With Joined Tables

Feb 1, 2008

I posted Wednesday thinking a SELECT Distinct would solve my problem but it didn't. I have a stored procedure that is used to grab data from 4 tables that I need to join.
The 1st table (Application) holds a job applicant's name and some other data
The 2nd table (Jobs) holds the Job name and test type
The 3rd table (Locations) holds the locations
Then there is a foreign key many to many table (Application_Locations) that holds the applicants UserID and a LocationID. This table may have multiple rows for the same applicant with different locations in each row.

When the procedure is ran I want all the data that I am requesting from the Application table, and all the data that I am requesting from the Jobs table but only the 1st returned result of the Join on the Locations and Application_Locations table. What do I need to do to correct this so that I only display 1 row for each UserID no matter how many locations thay may have applied to. (You will notice that there are some IF statements so only the 2nd and 4th queries in the sproc are the ones that apply )

Here is the SPROC that is currently in place but is displaying a row for each location.


CREATE PROCEDURE sp_AdminListApplicants

@LocationID int,
@FolderID smallint,
@JobID int,
@SortOrder char(1)

AS


IF @JobID <> 9999
BEGIN
IF @LocationID <> 9999
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
AL.LocationID= @LocationID
AND A.FolderID= @FolderID
AND A.JobID = @JobID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END

ELSE
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
A.FolderID= @FolderID
AND A.JobID = @JobID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
END

ELSE
BEGIN
IF @LocationID <> 9999
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
AL.LocationID= @LocationID
AND A.FolderID= @FolderID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END

ELSE
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
A.FolderID= @FolderID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
END
GO




Miranda

View 5 Replies View Related

How To Specify A Row Filter On Joined Tables

Dec 7, 2006

I am using SQL Server 2005 to publish joined tables for SQL Mobile subscribers for merge replication and column level tracking.

Using Management Studio I am trying to join tables and specify row filters on the joined tables. I.E. table 1 is joined with table 2. I need to define row filters for table 1 and row filters specific to table 2.

An example would be: Table 1 is a customer table that I filter on a specific customer. Table 2 might be an orders table that I need to join to get the customers orders but I also want to filter for open orders only.

When I specify the row filter for table 2 the join appears to be ignored and I receive the complete table 2 with the row filter applied.

I have searched the online books and the web and I have not run accross an example of using both joins and row filters where the filters are specified for both joined tables.

Is this possible via the Management Studio?

Thanks,

Ron

View 4 Replies View Related

T-SQL (SS2K8) :: Update Table With Multi-line Comments?

Apr 14, 2014

How to update column with multiline comments.

eg. I need to update Notification column in my table as below.

Exit Formalities Notification sent to:

Employee: LastName, FirstName
EmployeeEmail: EmployeeEmailAddress

View 4 Replies View Related

T-SQL (SS2K8) :: How To Quickly Update XML Column In A Table To NULL

Mar 9, 2015

I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:

UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null

Currently it is taking around 6 hours.

Is there a quicker way to do this?

View 1 Replies View Related







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