Transact SQL :: Merge Two Columns From Different Tables?

Oct 26, 2015

I have two temp. tables. I am trying to show the agents how makes the sales and the ones how didnt make sales based on the time that they clock in. One table is called #sales which has only the agents that make sales and other tables is #hours which has both agents that do not make sale. the problem is that I can not get both agents to show on my report. I tried different ways but I could not. #sales table uses (select statement from AmountStats table that stores only the agents who make sale). #hours table uses different tables to store all gents who makes sale and ones that are not making sale.

declare
@start datetime,
@end datetime,
@campaignId uniqueidentifier,
@campaignName varchar(250),
@segment varchar(50)

set @start = '2015-10-07'
set @end = '2015-10-20'

[code]....

how to merge these two columns from #sales and #hours tables without duplication.

View 6 Replies

Merge Tables On Unique Key Using Different Columns

May 15, 2008

hi guys,

i have 3 tables (T1, T2, T3), each with the same structure:
ID1 -> char(10)
ID2 -> char(12) NULL
ID3 -> char(10)
Value1 -> money
Value2 -> money
Value3 -> money
Note1 -> Text
Note2 -> Text

ID1+ID2+ID3 is the clusterd unique key in each table

what i want:
ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1

what i tried is to get all possible rows with

SELECT ID1,ID2,ID3 FROM T1
UNION
SELECT ID1,ID2,ID3 FROM T2
UNION
SELECT ID1,ID2,ID3 FROM T3

but i dont know how to join or add the other columns. maybe with

WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...

any ideas?
thx

View 10 Replies View Related

Data Mining :: Merge Two Or More Columns In Separate Tables

Jul 14, 2015

SQL 2008R2

Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.

What is example of best method to do this ?

View 2 Replies View Related

Transact SQL :: Merge Two Tables Data On Behalf Of ID In Comma Separated Manner?

Jul 2, 2015

Picture tells all what i need. Anyway i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012 ....

View 5 Replies View Related

Transact SQL :: Top 10 Columns In All The Tables

Oct 21, 2015

In our production database we are looking top 10 columns in all the tables, for this using the below script, output was showing only one table how we get the all the tables top 10 ...

select top 10 * from ProductIdList

View 6 Replies View Related

Transact SQL :: How To Count Where Two Tables Multiple Columns Match

May 4, 2015

There are two tables

TABLE 1 (NAME - Groupseats)

id session course groupcode sub1 sub2 sub3

1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His

From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.

Here is the TABLE 2 Structure and sample data:

table 2 (NAME - tblstudetail)

id studentID session course sub1 sub2 sub3

1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco

AND so no..........................

Now i just want to COUNT the Number of Groups Filled in tblStudateil.

View 10 Replies View Related

Transact SQL :: Multiple Inserts Different Columns And Tables Into One Table

Oct 12, 2015

I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.

If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),

[code]...

View 19 Replies View Related

Transact SQL :: Comparing Two Tables By NOT NULL Cells In Different Columns

Oct 22, 2015

View 2 Replies View Related

Transact SQL :: Find Unmatching Columns Between Two Tables While Applying A Change Column Prefix Table

Nov 16, 2015

For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.

In the below example, want to end up with three records:

1, View1, Column4
2, View2, Column4
2, View2, Column5

I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.

I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.

I want to end up with these three records as non-matching:

View1, Column4
View2, Column4
View2, Column5

View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.

/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;

[Code] ....

View 3 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Merge Columns

Jun 20, 2006

I have 2 columns 'Col1' (char) and 'Col2' (integer)

I'd like to combine these 2 columns and put the result in 'Col3' (char)

please help!!!

View 5 Replies View Related

Merge Two Columns

Jun 16, 2007

I want to combine a column with Id and other with Name.

For eg: If Id is 01 and Name is 'Tom', the output should be 01-Tom.

I am not able to execute it using || or CONCAT since Id has the data type int and Name is a string.

Do you have a solution?

`RG`

View 4 Replies View Related

Merge 2 Columns Into 1

Dec 7, 2007

hi guys,

i have a table with 2 columns say:

First_Name Last_Name
David Reinjal

i need to merge these 2 columns and put into a single column with a ','(comma) seperated. it will look loke this:

column_name
David, Reinjal

how can i do this? can anybody help me in this?

thanks,
David

View 4 Replies View Related

How Can I Merge Both Columns?

Aug 3, 2007

I have a column called email_address in table: mailing_list and another column called member_email in table: members and I want to do a SELECt to list the result of both columns in a new column

View 8 Replies View Related

Merge Two Columns

Feb 4, 2008

Hi!

I'm using IS with SQL Server 2005.
I need to know how to merge two columns (or more) into a single on as output.

For example:

#ID#Value1#Value2#

would be the input table. Now I want to map the column Value1 as 'Value' - as well as the column 'Value2' so there is only one output - and two rows generated by one row of the originate table.

I tried to map those columns to the same name, but IS refused that. My aim is to transform data from an old table to a new, relational one where all those values have to fit into relational tables (n:m relations).

How do I do that?

Thank you in advance!

View 3 Replies View Related

Merge 2 Columns To One?!

Nov 7, 2006

Hello everybody!
I�m trying to put together two columns of integer into one. They are coming from the same source table, so the "merge"-Task doesn�t work. This is what they look like:
column1: <123>
column2: <0123456>
The result should be:
merged_column: <1230123456>
Is there a task for that kind of operation or do I have to write a script? I would like do do that within the data flow.

thanks in advance!

View 5 Replies View Related

Transact SQL :: How To Merge Two Rows Into One Row

Sep 24, 2015

I have a table that has columns like following

trans_code
gl_code
12qw3
a1235
12qw3
a6578
12qw3
t531
e34g6
gt25
e34g6
y7327
e34g6
v23409

Is there an easy way to turn this into format below ??

trans_code
gl_code_1
gl_code_2
12qw3
a1235
a6578
12qw3
a1235

[code]....

View 5 Replies View Related

Is It Possible To Merge 2 Columns Into 1 In SQL Server

Nov 2, 2007

Hi..
    is it possible to merge 2 columns into 1 to hold data like this... When the user imports the file in particular file they will be
ACT_ID1 Tot_ACT1   ACT_ID2 TOT_ACT2 ...... until 15
BB            1245.45    CT              some amount .....
The 2 letter character may change prob for each file.. at leat i know of somethem may change
So while i transfer the data to the production database can is it possible to do some thing like
COL1 BB 1245.45
COL2 CT 12456.12 etc..

Any help will be appreciated.
Regards
Karen

View 5 Replies View Related

Merge 2 Columns From The Same Table

Sep 19, 2006

Hi *,

I have a table with 2 columns A1 and Firm. A1 holds "dirty data" and there are no NULL values. Firm holds "clean data" for some of the rows in A1 but not for all. So there are quite a few NULL values in this column. I want to replace the value in A1 if there is any data in Firm (value <> NULL).

Rigth now, I solve this issue with a simple VB.NET script:
Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' If Row.Firm_IsNull Then Row.Standard = Row.A1 Else Row.Standard = Row.Firm End If ' End SubEnd Class

Any ideas how to solve this issue without custome code?

Thanks!

View 1 Replies View Related

Transact SQL :: Merge Statement Failed

Sep 17, 2015

I have a stored procedure that runs (SQL Server 2012 (SP1) Standard Ed) daily and I never had any problem with this stored procedure. However, there is MERGE statement on the stored procedure and I see an error saying that the MERGE statement failed..Here are the stored procedure and error message:

-- FlushQueue
CREATE PROCEDURE [dbo].[FlushQueue] (@RowCount as int = 10000)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

[code]....

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. [SQLSTATE 42000] (Error 8672). The step failed.

Table definition:---CREATE TABLE [dbo].[ImportDefinitions]
(
[NodeName] [varchar](20) NOT NULL,
[ProcedureName] [varchar](100) NOT NULL,
[FilePrefix] [varchar](20) NOT NULL,
[ImportDelay] [int] NOT NULL CONSTRAINT [DF_ImportDefinitions_ImportDelay] DEFAULT ((0)),

[code]...

View 13 Replies View Related

Transact SQL :: Merge With Unique Identifier As PK

Jul 2, 2015

Using SQL Server 2014 i try to merge data from database [Susi] on server2 to database [Susi] on server1. Server2 is a linked server in server1. The PK of the table Core.tKontakte is uniqueidentifier with rowguidcol.

I wrote the following script and get error 206: "uniqueidentifier ist inkompatibel mit int".

    INSERT Core.tKontakte (KontaktID, AnredeID, Titel, Nachname)
   SELECT KontaktID, AnredeID, Titel, Nachname
   FROM [Susi].MSCMS.Core.tKontakte AS Client
   WHERE NOT EXISTS (SELECT KontaktID FROM Core.tKontakte AS Host WHERE Host.KontaktID = Client.KontaktID);

[Code] ....

View 8 Replies View Related

Transact SQL For Creating Merge Subscription

Sep 13, 2006

I am trying to create a merge publication in sql server 2005 and a merge subscription in sql server 2005 mobile through transact sql using sql server query analyzer.

I am succeeded in creating the publication but not subscription.

I am using sp_addmergepublication and sp_addmergesubscription.

sp_addmergepublication is working fine.

But the problem is in creating sp_addmergesubscription.

Can anyone please guide me how to create subscription to sql server 2005 mobile database using transact sql.

View 1 Replies View Related

Merge Replication Filtering Columns

May 25, 1999

I think I want to use merge replication but filter out one column but when I'm setting up the publication I only see row filtering available?

Is this just not possible?
Thanks for any help
Dan

View 1 Replies View Related

Merge 2 Columns T-SQL Ninja Needed

Apr 16, 2008

I have this code below, one thing i am trying to do is merge 2 columns, it seems what i have now is suming them can someone give me a hint or two thanks.

Code Snippet
SELECT a.*, b.*, a.Company + b.[Customer Suffix] AS AdditionalColumn
FROM (
SELECT
abCUSA AS Company,
abCUSB AS Account,
0 AS DeliverTo,
abcnam AS [Account Name],
abcad1 AS [Line Address 1],
abcad2 AS [Line Address 2],
abcad3 AS [Line Address 3],
abcad4 AS [Line Address 4]
FROM
LIVEAS400.S65C422B.WRFDTA.PARNADR
) a
inner JOIN
(
SELECT
SLDATE as Date,
SLCUSA as [Compny Number],
SLCUSB as [Customer Suffix],
SLDELN as [Deliber To Number],
SLTREF as [Trans Reference],
SLCNAM as [Customer Name],
-- SLTRT2 as [Trans type],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLGDSV * -1 ELSE SLGDSV END as [Goods Value],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLVATV * -1 ELSE SLVATV END as [VAT],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLTOTV * -1 ELSE SLTOTV END as Total,
SLDESC as [Trans Description]
FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR
) b ON a.Account = b.[Customer Suffix]

View 4 Replies View Related

Merge Replication Columns Limit??

Dec 4, 2006

Hi,

I am experiencing a wired problem withe merge replication and SQL 2000 SP4.

We had replication working for 4 years without a problem, one of the table that we replicate has been growing in columns, now it has 55 columns.

I have noticed a problem, when I update the last column on the table and waits for the change to be updated on the subscriber the change is not there. But if I replicate the first 40 columns it works.

Changes made:

1) Upgraded to SP4
2) Added some extra fields using sp_addreplcolumn

Is this a known bug? Any ideas?

Regards

View 3 Replies View Related

Upstream Columns Not Showing In Merge

Aug 8, 2007

why wouldn't an upstream column show up in the merge editor?

I can see the column in the metadata by clicking the green arrow going into the merge, but it doesn't not show up as a selectable value in any of the drop downs.

this is true for input1 and input2 in the merge

anyone know how I can force it to show up?

View 4 Replies View Related

Transact SQL :: Update Statement In Merge Does Not Work

Jul 29, 2015

In the following t-sql 2012 merge statement, the insert statement works but the update statement does not work. I know that is true since I looked at the results of the update statement:

Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type

[Code] ...

Thus can you show me some t-sql 2012 that I can use to make update statement work in the merge function?

View 3 Replies View Related

Transact SQL :: Current Flag On Merge Statement

Nov 12, 2014

I was using Type 2 for one of our Fact table.... and need to put a flag to know which one is the Current record... I couldn't able to figure how to implement logic in the merge statement... This is an example Query ....I was using like this for my fact table...

Basically I need to track CustomerName and City... So I need a Currentflag (Y) for latest record....

MERGE INTO [dbo].[TargetCustomer] AS TRG
USING [dbo].[MyCustomers] AS SRC
ON TRG.[CustomerID] = SRC.[CustomerID]
AND TRG.[CustomerName]=SRC.[CustomerName]
AND TRG.[City]=SRC.[City]

[Code] .....

View 7 Replies View Related

Transact SQL :: Insert On Merge Statement Not Working

Oct 7, 2015

In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.

Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number

[code]...

Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?

View 10 Replies View Related

Transact SQL :: MERGE Function And OUTPUT To A File

Sep 14, 2015

I used the MERGE function for the first time. Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.

What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.

View 5 Replies View Related

Transact SQL :: Merge 2 Rows Into One Line Output

May 21, 2015

How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.

CREATE
TABLE [dbo].[MRMReadings](
[SiteIncomeID] [int] IDENTITY(1,1)
NOT NULL,
[SiteID] [nchar](5)
NOT NULL,

[Code] ....

Is it possible to return the data in the following format merging 2 lines of data into one output:

SiteID   ReadStartDate ReadEndDate    ReadStartIncome     ReadEndIncome
L0020     19/05/2015 05:00 20/05/2015 05:00    85.98     145.98
L0101     19/05/2015 22:07    20/05/2015 22:14         1,936.08       1,438.89
L0102     20/05/2015 21:16   19/05/2015 21:48   143.65 243.5

I am using SQL 2008 R2.

View 12 Replies View Related

Transact SQL :: Merge Multiple Rows Onto A Single Row

May 13, 2015

I have a set of data which contains individual logon and logoff data. The table is as follows:

AGENTID, EVENTTIME, CURRENTSTATE
1234, 2015-05-12, 15 (For Logon) or 25 (For Logoff)

I'm hoping to extract this data as follows:

AGENTID, LOGON DATE/TIME, LOGOFF DATE/TIME, DURATION

View 19 Replies View Related

Transact SQL :: Script To Merge Similar Content?

Jun 17, 2015

I have a table which contains single legs. E.g.

Row Leg-ID From To On DateFrom DateTo DOW

1 ABC123 AAA BBB CCC 01JAN15 01JAN15 1

2 ABC123 AAA BBB CCC 07JAN15 07JAN15 1

3 ABC123 AAA BBB CCC 14JAN15 14JAN15 1

4 ABC123 XXX YYY ZZZ 21JAN15 21JAN15 1

I now want to merge those single records, which follow a pattern. For the above case this would be Row 1+2+3, so the result should be:

Row Leg-ID
From To
On DateFrom
DateTo DOW
1 ABC123
AAA BBB
CCC 01JAN15
14JAN15 1
3 ABC123
XXX YYY
ZZZ 14JAN15
14JAN15 1

The pattern is, that the legs from Row 1 and 2 have identical attributes (Leg-ID, From, To, On, DOW) and are on consecutive weeks on the same weekday. I was doing this through a while-look:

check if there is a record matching the following week (a.DateTo = dateadd(d, 7, b.DateFrom))if there is a match, then update the previous week record DateTo with the following week DateFromdelete the following week record but this is very slow, for 50T rows it runs approx. 6 hrs to shrink everything..

View 4 Replies View Related