SQL Server 2012 :: Return Descriptions From Table2 In Same Format As Codes From Table1?
Apr 22, 2015
Table1 contains a CODES column which may store multiple codes separated by commas.
The descriptions for these codes are stored individually in a column in in Table2.
I would like to return the descriptions from Table2 in the same format as the codes from Table1.
Table1 Stores multiple codes per ID in a single column.
ID CODES
-- --------------
01 Code1,Code2,Code3
Table2 Stores the definitions for the codes in Table1
CODE DESCRIPTION
----- ---------------
Code1 Description1
Code2 Description2
Code3 Description3
The tables are what the tables are. Here is what I am trying to accomplish:
ID CODES DESCRIPTIONS
-- ----------------- --------------------------------------
01 Code1,etc Description1,Description2,Description3
I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?
View 7 Replies
ADVERTISEMENT
Apr 23, 2014
I want to update table2 by checking table1.
if trauma has atleast 1 then clm2 in table2 would be 1
if infec has atleast 1 then clm2 in table2 would be 2
table1
Clm1 Clm2
Trauma 1
Trauma 1
Trauma 1
Infec 2
Infec 2
Trauma Null
table2
clm1 clm2
Trauma 1
infec 2
I have to update table2 based on table one ny checking multiple columns.
View 2 Replies
View Related
May 5, 2008
Hi,
How do you express neither no in TSQL. I am trying to create a view that gathers work orders that are neither in one table nor in another
I tried the following few appraoches but feel there must be something better. thanks!
1.
Select *
FROM xyz
WHERE Status = Open
AND (Number
NOT IN (SELECT WONumber
FROM dbo.Table1)
OR Number
NOT IN (SELECT WONumber
FROM dbo.Table2))
2.
Select *
FROM xyz
WHERE Status = Open
AND Number
NOT IN (SELECT WONumber
FROM dbo.Table1)
UNION
Select *
FROM xyz
WHERE Status = Open
AND Number
NOT IN (SELECT WONumber
FROM dbo.Table2)
View 1 Replies
View Related
May 20, 2005
Is there anyway or a sql statement that can do something like, Select * from table1 where table1 not in table2.which means i get all the rows in table1, as long as they r not in table2
View 2 Replies
View Related
Sep 23, 2005
Hi,
I have a problem I have two tables, table1 my main table and table2 my secondary table.
Table1 has lots of records with a field for a unique transaction number, table2 also has a field for a transaction number.
Table 2 only has a 10 entries in with the same transaction number of
10 of the entries in table1.
My question is how do I get all the records from table1 that DONT have a corrisponding transaction number in table2.
Thanks
Paul
View 2 Replies
View Related
Nov 22, 2007
Hi
I have table1 with tow columns ( col1 and col2)
table2 colums (col1 and col2)
Table1 -- col1--- col2
-----------A ---- dfg
-----------B ---- ght
Table2-- Col1--- col2
-----------A ----- jjk
-----------B ----- lkl
How do I update col2 of Table1 with the data from col2 of Table2 in a Single Query
View 3 Replies
View Related
Mar 9, 2004
i have tow table same structure
how i can copy the data in table1 to table2
in T-SQL statment
thanx
View 1 Replies
View Related
Oct 1, 2004
Hi,
I'm using MSSQL and I have 2 tables with same structure. Can any one tell me the easiest way on how to select a row from table1 and insert it into table2.
Many thanks.
View 1 Replies
View Related
Feb 1, 2014
I have two tables with similar two columns as shown below
table1
code | organisation
256 | abc
832 | xyz
893 | tax
921 | abc
951 | abc
table2
code | organisation
951 | abc
832 | xyz
256 | abc
893 | tax
921 | tax
Now, I want to check whether all the codes in table1 existing in table2 and list them, and if both columns from table1 is matching with the both columns in table2. For e.g. 256|abc in table1 is matching with 256|abc in table2
The output should be :
921 | tax
For e.g. 256abc is there or not in table2,
View 5 Replies
View Related
Jun 26, 2014
I have a table table1
ID Date In Out
22 01/01/201410:00 11:00
22 02/01/201410:00 11:00
22 03/01/201410:00 11:00
23 01/01/201410:00 11:00
23 02/01/201410:00 11:00
23 03/01/201410:00 11:00
24 01/01/201410:00 11:00
24 02/01/201410:00 11:00
24 03/01/201410:00 11:00
25 01/01/201410:00 11:00
25 02/01/201410:00 11:00
25 03/01/201410:00 11:00
Table 2
ID Date In Out
22 01/01/201410:00 11:00
22 02/01/201410:00 11:00
22 03/01/201410:00 11:00
23 01/01/201410:00 11:00
23 02/01/201410:00 11:00
23 03/01/201410:00 11:00
24 01/01/201410:00 11:00
24 02/01/201410:00 11:00
24 03/01/201410:00 11:00
I want to insert only those record from table 1 to table 2 which is not there in table 2
View 3 Replies
View Related
Oct 17, 2014
HOW TO SELECT LAST ROW IN GROUP BY CLAUSE AND JOIN LAST ROW WITH ANOTHER TABLE IN ONE QUERY?
INPUT:
custidlivingstatusdate
1single2014-01-01 00:00:00.000
1married2014-01-02 00:00:00.000
1married_kids2014-01-03 00:00:00.000
2married_kids2014-01-04 00:00:00.000
2married2014-01-05 00:00:00.000
2single2014-01-06 00:00:00.000
Query 1 -- select last record
SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Custid ORDER BY Date DESC) AS Seq,*
FROM Living_Situation
)t
WHERE Seq=1
Output:
Seqcustidlivingstatusdate
11married_kids2014-01-03 00:00:00.000
12single2014-01-06 00:00:00.000
Table Customer:
custidsexesurname
1mjansen
2mpietersen
How to link Query 1 with table Customer in one query?:
Desired output in ONE query:
custidsexesurnamelivingstatus
1mjansen married_kids
2mpietersen single
View 3 Replies
View Related
Dec 17, 2013
I'm trying to update a value into a table a sum. The two tables have ID values. These ID values appear once in Table1 and multiple times in Table2. I'm currently trying to sum up the values in Table2 where the IDs are equal to Table1 and then update the value:
UPDATE [Table1] SET
[Total] =
(SELECT SUM([Table2].[QTY]) FROM [Table2],
[Table1] WHERE [Table1].[ID] = [Table2].[ID]
GROUP BY [Table1].[ID])
FROM [Table1], [Table2] WHERE [Table1].[ID] = [Table2].[ID]
View 2 Replies
View Related
Jul 23, 2005
Hi guys,i have a little problem here.im attempting to write a stored procedure that compares two tables ofthe same data structure and adds (inserts) extra records that exist intable1 to table2.My problem is that i dont have a unique identifier between the tables.i think someone said that i needed to build up a keyany ideas greatly appreciated ??C
View 1 Replies
View Related
Oct 10, 2006
We have an inventory table (Items) that contains item_no and qty_on_handfields.Another table (Item_Serial) contains serial numbers for any item that hasserial numbers.If an item has 10 qty_on_hand, it should have 10 records in Item_Serial, oneunique serial number for each item.I am trying to find items where the number of serial numbers does not equalthe qty_on_hand.Here is a query I'm trying to use and, of course, it does not work:select Items.item_no, Items.qty_on_handfrom Items inner join Item_Serial on Item_Serial.item_no = Items.item_nowhere Items.qty_on_hand <count(Item_Serial.item_no)Anybody know how to do this?Thanks.
View 4 Replies
View Related
Aug 16, 2007
Hello,I have 2 tables, Table1 and Table2. I have copied all data from Table1to Table2.However Table1 is dynamic it has new rows added and some old rowsmodified everyday or every other day...How can I continue to keep Table2 up to date without always having tocopy everything from Table1?Basically from now on I would only like to copy new rows or modifiedrows in Table1 to Table2 and skip rows that are already present andhave not been modified in Table1. I would like to not do anything forany rows that were removed in Table1 and continue to keep a copy ofthem in Table2.Is using a DTS package the best way to automate this update of Table2to make sure Table2 is always up-to-date with Table1?Thanks for any help or advise :-)Yas
View 14 Replies
View Related
Jul 20, 2005
Hi,I am getting errors in the following... Is it even possible to join onCONTAINS?SELECT ListA.ContentFROM ListA LEFT OUTER JOIN ListBON CONTAINS(ListB.Content, ListA.Content)WHERE ListB.Content IS NULLThanks!
View 4 Replies
View Related
Mar 21, 2008
Hi,
I have 2 tables. Table1 and Table2.
In Table1 i have 10 rows of data and in Table 2 i have 1 row of data (which also appears in Table1).
How do i delete from Table1 the data that appears in Table2 (without using an ID key)?
Example:
I want it to be like when i use "insert into".
Insert into Table1
select * from Table2
I want it to be something like that:
Delete from Table1
select * from Table2
View 11 Replies
View Related
Aug 29, 2000
Is it possible to move rows from Table1 in Database1 to Table2 in Database2?
Say, I have Database1 containg Table1:
ID ¦ Cars
---------------
1 BMW
2 Mercedes
3 Toyota
1 Opel
etc....
And Database2 containg Table2 (that is empty just at the moment):
ID ¦ Cars
---------------
no data added yet...
How can I copy say everything from Table1 that has ID=1 (the parameter I send in with the stored procedure at execution), and insert this in Table 2?
This would be, I insert the row with the BMW thingy and the row with the Opel thingy as 2 new rows in table 2.
Note that the two tables are located in 2 different databases.
I am using SQL Server 7 and am trying to implement this in a stored procedure.
Thanks for all help!!
Mark Nelson
View 1 Replies
View Related
Jul 9, 2013
I have two user tables, one is in a catalog1 another is in catalog2. How would I copy catalog1.users into catalog2.users (not duplicate, but copy some fields over...).
I've tried variations of thi sample code below, with #temptable and with @temptable, inside the "use master" and outside of....
Code:
USE master
DECLARE @temptable TABLE (name nvarchar(5))
USE [Users]
[Code]....
View 1 Replies
View Related
Jun 9, 2015
I'm trying to get the records in table1 not in table2 the query is
Select * from table1 where not exists (select * from table2)
--table1 and table2 ,structure are same
It will get a null result
Strange thing , while I add a where condition in the subquery, it worked.
Select * from table1 where not exists (select * from table2 where table1.ID =table2.ID)
It will get the result as expected.
I just thought it would compare all the fileds for the outer query and inner query, but seems not. Why, I add a condition, it worked ?
View 4 Replies
View Related
Jun 17, 2008
hello friend
i have table1 and table2 and table3..
i have category and product number and name of colums for per table
i want to do this.. select category from table1 where category= textbox1.text and category from table2 where category= textbox1.text and category from table 3 where category= textbox1.text
and if there are category for per category colums of per table, gridview should displayed table1 and table2 and table2 in category and name and product number of colums:)
if there is category of coulums for table1, gridview should displayed just table1 in category and name and product number of colums:)
i want to 3 bind for gridview1 :) it should be without join because i dont need join.. columns name is same for tables but value of category is different but it can be same :)
cheers
View 6 Replies
View Related
Oct 30, 2013
I have two tables T1 and T2
T1 is having User : Visiting Date
T2 IS having User : Last Visited Date
Now I have to update T2 with last visited date based on T1 Entry.
View 1 Replies
View Related
Jan 11, 2008
Hi everyoneI am trying to create a DELETE Trigger. I have 2 tables. Table1 andTable2. Table 2 has all the same fields and records as Table1 + 1extra column "date_removed"I would like that when a record is deleted from Table 1, the triggerfinds that record in Table2 and updates the date_removed filed withcurrent time stamp.The primary key on both is combination of domain,admin_group and cn.CREATE TRIGGER [tr_updateTable2] ON [dbo].tbl_Table1FOR DELETEASUpdate Table2SET date_removed = getDate()I'm stuck here, how do I manipulate on Table2 only the records thatwere deleted on Table1, so to only update date_removed filed for themin Table2?I guess i need to compare domain, cn and admin_group, but I don't knowhow.Any help would be greatly appreciatedThanks! :-)
View 3 Replies
View Related
Nov 8, 2006
I need help in writing a query.
The query should get top 10 items and their values from current year and the values for the same items from previous year table.
I was able to write the code for 1st part that gets values from 1st table but I don't know how to get the values from 2nd table.
The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.
I am attaching some images below to give more information.
Image of results from my query.
Image of how the final output should look like.
The Store Procedure code is:
ALTER Procedure [dbo].[free_customsHS4](
@TblName1 varchar(20),
@TblType varchar(20),
@District varchar(6),
@Month varchar(3)
)
AS
Begin
SET NOCOUNT ON;
Declare @SQuery nvarchar(3000)
set @TblName1 = '[' + @TblName1 + ']'
set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description,
sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1
where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1, b.descrip_1
order by [Amount] desc'
EXEC sp_executesql @SQuery
END
View 2 Replies
View Related
Jun 29, 2015
I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
View 11 Replies
View Related
May 8, 2008
Good day., please help me,in a formview control, i set it in Insert Mode, so it should display info from table 1 but when i click on the insert button, it will insert it in table 2.btw, table 1 and table 2 are in the same database?? how about if they are not in the same database?how?please help me,Thanks.,SALAMAT PO.,
View 3 Replies
View Related
Dec 29, 2004
If I want to copy the data from Table1 in Database A to Table2 in
Database B but Table1 column name is code , Table 2 column name is
vesselcode.
(Code = vesselcode)
How to copy all data from Table1 in Database A to Table2 in Database B ?
Do I need to write the SQL statment ? and Can I use Server Enterprise
Manager Tool?Thx a lot.
View 1 Replies
View Related
Mar 21, 2008
Hi,
I am using SQL Server 2005 and would like to get all the rows that exist in
(Table1 - Table2) without the answer being distinct, what do i do?
Example:
Table1:
Products: A, B, C, D, D, D, E
Table2:
Products: A, C
select * from Table1
except
select * from table2
will give me: B, D, E
But i want it to give me: B, D, D, D, E
Any way that would work will be helpful. Thanks in advance.
View 11 Replies
View Related
Feb 9, 2014
I have data like this
"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}
I want to get only data in double codes in using sql query.
View 9 Replies
View Related
Mar 2, 1999
I apologize for the length of this message, but I think I need to include all this info so that the problem is understood. I am having what appears to be a problem capturing the return code from a failed BCP.
I create a stored proc to use BCP to load a table:
create procedure sp_bcp_load as
declare @RC int
execute @RC = master..xp_cmdshell "bcp JON..W4KPV in e:inetpubftprootfinreslaw4kpv.g4000.data /Sdbmtss1 /m 0 /f d:mssqluserdatafinresW4KPV.fmt /Usa /P /e d:mssqluserdatafinrescp1.err /t""|"" /r "
select 'Return code from bcp = ', @RC
if @RC <> 0
BEGIN
print 'BCP Error.'
return (8)
END
GO
If I execute the SP, and encounter a transaction log full error, the return code is still zero:
1000 rows sent to SQL Server. 45000 total
1000 rows sent to SQL Server. 46000 total
Msg 1105, Level 17, State 2:
Server 'DBMTSS1', Line 1:
Can't allocate space for object 'Syslogs' in database 'Jon' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
(54 row(s) affected)
----------------------- -----------
Return code from bcp = 0
If I execute the SP again, it correctly returns a non-zero value:
Msg 1105, Level 17, State 2:
Server 'DBMTSS1', Line 1:
Can't allocate space for object 'Syslogs' in database 'Jon' because
the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
(6 row(s) affected)
----------------------- -----------
Return code from bcp = 1
(1 row(s) affected)
BCP Error.
Does anybody have an idea why this behaves this way? Any suggestions on how to trap an error on the first call?
Thanks,
Jon Carter
View 2 Replies
View Related
Aug 28, 2007
Aloha !
I am posting this information simply as an FYI. This is in reference to the MS-SQL command "CONVERT"
I spent over 2 hours :rolleyes: screwing around trying to find out different ways of formatting dates from MS-SQL into something that makes sense for what I needed. I googled everything I could think of and found multiple references that said the info is available on MSDN.. but I could not find it. What I did find were thousands of relatively useless references to "format codes" for converting dates, but with no references to what the different format codes would ultimately yield, or what format codes were available to use.
What I ended up doing was writing a small script to generate a list of all of the variations I could find.
Below is the script, and the output that it yielded.
Now, before I get bombarded with "there is a better way" I know there probably is. But this is the way that I needed to do it this particular time. If there are technical errors in my explanation, anyone is welcome to correct them. But after 2 hours of messing with this for what should have been a super simple single .0009 second command, I am just irritated beyond belief that it had to be this complicated to find any useful information on the subject. That is why I am creating this. Hopefully it helps someone else.
The format for the MS SQL CONVERT command is :
CONVERT( length_of_output, date, format_code )
length_of_output : is exactly that . the number of characters that you want returned as your result. If you use a length of 6 you will only see the first 6 characters that are returned. I found the longest valid length to be 28 characters, but I went as high as 128 just for giggles and to see if it revealed any secrets.
date : is a valid date, I used directly the getdate() function
format_code : well.. that's the tricky part. See below.
What I did was ran a script that originally went from 1 to 20,000. It crashed at 15. Apparently the format codes are not totally sequential. So I put in an on error resume next.
What I found is that :
1) the codes are not uninterrupted sequential numbers.
2) the code output repeats every 255
3) negative numbers can be used, but its pointless.
4) useful valid codes are in the ranges of : 0-14, 20-25, 100-114, 120, 121, 126, 130 and 131
5) 0-25 typically represent "short dates" with the year being only 2 digits, but there are exceptions
6) 100 and above always returned a 4 digit year. the exception was 130 and 131, I don't know what it was trying to do.
Here is the script i ran
<%
on error resume next
for iintCounter = 0 to 256
SQL = "SELECT CONVERT(CHAR(128), getdate(), " & iintCounter & " ) as TheDate"
Set rsTheDateFormat = TheDatabase.Execute(SQL)
response.write SQL & " = " & rsTheDateFormat("TheDate") & "<br>"
set rsTheDateFormat = nothing
next
%>
and here is the output
SELECT CONVERT(CHAR(128), getdate(), 0 ) as TheDate = Aug 28 2007 6:46AM
SELECT CONVERT(CHAR(128), getdate(), 1 ) as TheDate = 08/28/07
SELECT CONVERT(CHAR(128), getdate(), 2 ) as TheDate = 07.08.28
SELECT CONVERT(CHAR(128), getdate(), 3 ) as TheDate = 28/08/07
SELECT CONVERT(CHAR(128), getdate(), 4 ) as TheDate = 28.08.07
SELECT CONVERT(CHAR(128), getdate(), 5 ) as TheDate = 28-08-07
SELECT CONVERT(CHAR(128), getdate(), 6 ) as TheDate = 28 Aug 07
SELECT CONVERT(CHAR(128), getdate(), 7 ) as TheDate = Aug 28, 07
SELECT CONVERT(CHAR(128), getdate(), 8 ) as TheDate = 06:46:45
SELECT CONVERT(CHAR(128), getdate(), 9 ) as TheDate = Aug 28 2007 6:46:45:507AM
SELECT CONVERT(CHAR(128), getdate(), 10 ) as TheDate = 08-28-07
SELECT CONVERT(CHAR(128), getdate(), 11 ) as TheDate = 07/08/28
SELECT CONVERT(CHAR(128), getdate(), 12 ) as TheDate = 070828
SELECT CONVERT(CHAR(128), getdate(), 13 ) as TheDate = 28 Aug 2007 06:46:45:507
SELECT CONVERT(CHAR(128), getdate(), 14 ) as TheDate = 06:46:45:507
SELECT CONVERT(CHAR(128), getdate(), 20 ) as TheDate = 2007-08-28 06:46:45
SELECT CONVERT(CHAR(128), getdate(), 21 ) as TheDate = 2007-08-28 06:46:45.540
SELECT CONVERT(CHAR(128), getdate(), 22 ) as TheDate = 08/28/07 6:46:45 AM
SELECT CONVERT(CHAR(128), getdate(), 23 ) as TheDate = 2007-08-28
SELECT CONVERT(CHAR(128), getdate(), 24 ) as TheDate = 06:46:45
SELECT CONVERT(CHAR(128), getdate(), 25 ) as TheDate = 2007-08-28 06:46:45.540
SELECT CONVERT(CHAR(128), getdate(), 100 ) as TheDate = Aug 28 2007 6:46AM
SELECT CONVERT(CHAR(128), getdate(), 101 ) as TheDate = 08/28/2007
SELECT CONVERT(CHAR(128), getdate(), 102 ) as TheDate = 2007.08.28
SELECT CONVERT(CHAR(128), getdate(), 103 ) as TheDate = 28/08/2007
SELECT CONVERT(CHAR(128), getdate(), 104 ) as TheDate = 28.08.2007
SELECT CONVERT(CHAR(128), getdate(), 105 ) as TheDate = 28-08-2007
SELECT CONVERT(CHAR(128), getdate(), 106 ) as TheDate = 28 Aug 2007
SELECT CONVERT(CHAR(128), getdate(), 107 ) as TheDate = Aug 28, 2007
SELECT CONVERT(CHAR(128), getdate(), 108 ) as TheDate = 06:46:45
SELECT CONVERT(CHAR(128), getdate(), 109 ) as TheDate = Aug 28 2007 6:46:45:913AM
SELECT CONVERT(CHAR(128), getdate(), 110 ) as TheDate = 08-28-2007
SELECT CONVERT(CHAR(128), getdate(), 111 ) as TheDate = 2007/08/28
SELECT CONVERT(CHAR(128), getdate(), 112 ) as TheDate = 20070828
SELECT CONVERT(CHAR(128), getdate(), 113 ) as TheDate = 28 Aug 2007 06:46:45:930
SELECT CONVERT(CHAR(128), getdate(), 114 ) as TheDate = 06:46:45:930
SELECT CONVERT(CHAR(128), getdate(), 120 ) as TheDate = 2007-08-28 06:46:45
SELECT CONVERT(CHAR(128), getdate(), 121 ) as TheDate = 2007-08-28 06:46:45.943
SELECT CONVERT(CHAR(128), getdate(), 126 ) as TheDate = 2007-08-28T06:46:45.990
SELECT CONVERT(CHAR(128), getdate(), 130 ) as TheDate = 15 ????? 1428 6:46:46:040AM
SELECT CONVERT(CHAR(128), getdate(), 131 ) as TheDate = 15/08/1428 6:46:46:040AM
SELECT CONVERT(CHAR(128), getdate(), 256 ) as TheDate = Aug 28 2007 6:46AM
View 11 Replies
View Related
May 17, 2007
Where can I find a list of all the format codes, complete with their definitions?
View 7 Replies
View Related
Dec 13, 2006
Hi all,
I have one application through which i m installing SQL Server 2005 Express Edition.
If some failure occures, all I get is one exitCode which is a return code of SQL Express installer. Is there any way by which I can find out the exact reason of failure.
One example is error code 28062.
This error code is for "sql is unable to install on compressed folder / drive".
Please help
Thanks and regards,
-Anup
View 1 Replies
View Related