T-SQL (SS2K8) :: Combine 2 Selects - Insert Results In Two Columns Of New Table
Sep 11, 2014
DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)
SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'
SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')
_______________________________________
This is part of a bigger project but I am stuck on this part. I get back 2 result sets
Data Data
101 2013
102 2014
103
104
105
I want to insert the results in a new table 2 columns and get the results below.
New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014
View 2 Replies
ADVERTISEMENT
Oct 8, 2015
How can I get the results of this query>>
SELECT
RoleName = pr.name
,RoleType = pr.type_desc
,PermissionType = pe.state_desc
,Permission = pe.permission_name
,ObjectName = s.name + '.' + o.name
[Code] .....
WHICH RESULTS IN THIS>>
RoleNameRoleTypePermissionType PermissionObjectNameObjectType
publicDATABASE_ROLEGRANTDELETEdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTINSERTdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTSELECTdbo.AgencyUSER_TABLE
[Code] ....
View 3 Replies
View Related
Aug 12, 2014
I have written some SQL that relates to the (partial) result set below -
I am having an incredibly difficult time trying to work out why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.
My query looks like the following -
go
declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
[Code].....
View 1 Replies
View Related
Apr 29, 2015
I want to combine 2 columns from different table.
let said my table1:
id: A1 customername: WesternDigital
id: A2 customername: Sony
id: A3 customername: Samsung
my table2 :
id: A1 customername: Rose
id: A3 customername: John
My output is like that:
customername
WesternDigital, Rose
Sony
Samsung, John
my sql as below:
select table1.customername + table2.customername as customername
from table1
inner join table2 on table1.id = table2.id
how to make the table1 sony appear also even it does not exist in table2?
View 1 Replies
View Related
Jan 18, 2004
It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.
But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.
select col1 from table1
go
select col2 from table2
go
Now I want to combine them so table3 is made of col1 and col2.
View 4 Replies
View Related
Jul 13, 2012
I'd investigate the performance differences between two similar queries (written against AdventureWorks) :
select FirstName, LastName from Person.Contact
where LEFT(LastName,2) = 'Mc'
-- cost .877619
-- after index.0931743
select FirstName, LastName from Person.Contact
where LastName Like 'Mc%'
-- cost .875662 - missing index - add a nonclustered index including first name
-- after index: .0033256I was surprised to see that the Estimated Execution Plan shows a slightly higher cost for LEFT(LastName,2) over LIKE. The Plan for LIKE recommended creating a nonclustered index including FirstName. The LEFT Plan did not recommend any new indexes, yet after I created it the estimated performance increased dramatically, thought not nearly as dramatically as for the LIKE query.
One lessons to take from this is that that you can't always relay on Estimated Execution Plan to tell you where new indexes should be. How accurate are the costs in general? Other lessons?
View 5 Replies
View Related
Aug 15, 2015
I have a requirement to Insert Column 1 and Column 2 based on below condition only. Looking for a Store procedure or query
Condition : Allow Insert when column 1 and Column 2 have same values on 2nd row insert. But should not allow insert when Column 2 value is different.
ALLOW INSERT:
Column1 Column2
A0007 12-Aug
A0007 12-Aug
A0007 12-Aug
DONOT ALLOW INSERT: (COLUMN1 ID should not allow different dates)
Column1 Column2
A0007 23-Mar
A0007 02-Feb
FINAL OUTPUT Should be
Column1Column2
A000712-Aug
A000712-Aug
A000712-Aug
B000220-Jun
B000220-Jun
C000330-Sep
Discard Insert when Column1 ID's comes with Different dates.
View 4 Replies
View Related
Dec 2, 2005
I can create 1 view of a database then use that views results to query
but i want do this with a stored procedure
pass some data to it, so it selects some data
query the results it created and output this data.
ie
pass 2 values to the procedure
(
@FirstValue int,
@secondValue int output
)
select from a database with the firstpassed value
Select *
From TableName
Where ID = @FirstValue
Then
using the results from the above select
Select *
From theResultsOfAbove
Where ID = @SecondValue
any ideas would be fabo !
View 6 Replies
View Related
Aug 20, 2013
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
View 9 Replies
View Related
Oct 16, 2007
how can i make this stored procedure:
INSERT INTO tableX (field1, field2, field3) VALUES (value1, value2, value3)
where value1 is field11 in table11
and value2 is field22 in table22
and value3 is field33 in table33
so all values are stored in another tables
View 1 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Dec 16, 2014
I've the table structure below. Example table of my original
create table fms
(
fs_locn char(100),
fs_account_no varchar(200),
fs_cost_center_no varchar(100),
fs_tran_type char(50),
fs_post_amt float,
fs_tran_date datetime
[Code] ....
Expecting Output :
Account 200Prod 201PROD ProdcutionCost
E002-SW100-2100 2500 1000 3500
How to do that?
I tried like
select
k.fs_acoounts,
k.200Prod,
(
my query
)k
its showing error '200prod'
View 1 Replies
View Related
Apr 1, 2014
I have a table with the following columns
Num,ID,Pos,Value
74 ,1,2,beck
74 ,1,2,greg
74 ,1,9,mike
74 ,1,9,laggo
74 ,2,2,beck
74 ,2,2,greg
74 ,2,9,mike
74 ,2,9,laggo
I am trying to get the result as follows.
Num Id Final Value
74 1 2,beck,greg;9,mike,laggo
74 2 2,beck,greg;9,mike,laggo
I tried to use Stuff and XMLpath but it is not giving me distinct results.
View 4 Replies
View Related
Apr 30, 2014
I have one table say A and in which 4 columns are there. Out of 4 , one columns stores the queries like
'select * from table xyz' etc(Only select queries). I am writing a procedure in which I have to fetch this column and execute the query and wants to check whether query i.e. "select * from table xyz" contains any record or not. If yes , I am updating the table B with value as Pass , else Fail.
I used execute @queryfromvariable but it does not gives me count..
View 7 Replies
View Related
Nov 21, 2014
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL
[Code] ....
I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:
[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]
Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline
update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
View 7 Replies
View Related
Jan 17, 2008
Here's my stored procedure:
ALTER PROCEDURE sproc_InsertOrder
(
@CustID tinyint,
@Size varchar(50),
@Crust varchar(50),
@total smallmoney
)
AS
INSERT INTO tblOrders (CustID, SizeID, CrustID, Total)
VALUES(@CustID,
SELECT SizeID FROM tblSizes WHERE @Size = SizeName,
SELECT CrustID FROM tblCrusts WHERE @Crust = Crust,
@total)
SELECT SCOPE_IDENTITY()
RETURN
And my tables:
tblOrders
-------------------------------------------
OrderID | CustID | SizeID | CrustID | Total
-------------------------------------------
tblSizes
--------------------------
SizeID | SizeName | Price
--------------------------
tblCrusts
------------------------
CrustID | Crust | Price
------------------------
My stored procedure is giving me an error, it says "Unable to parse query text", and I'm certain the problem is with the SELECT statements. What am I doing wrong?
View 4 Replies
View Related
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
View 5 Replies
View Related
May 14, 2014
I have a table with this info:
NrCard numberPersonAuto123456789101112
11111111111111111111User1VW Jetta6,46,46,46,45,825,825,825,825,825,825,826,4
22222222222222222222User2Honda CR-V 13,2113,2113,2112,0112,0112,0112,0112,0112,0112,0113,2113,21
How I can get this result:
NrCard numberPersonAutomonthvalue
11111111111111111111User1VW Jetta16,4
11111111111111111111User1VW Jetta26,4
11111111111111111111User1VW Jetta36,4
11111111111111111111User1VW Jetta45,82
11111111111111111111User1VW Jetta55,82
11111111111111111111User1VW Jetta65,82
[code]....
Should I use unpivot or pivot?
View 2 Replies
View Related
Dec 27, 2014
I need to retrieve columns names(instead of select * from) as single row from table in dynamic way.
i m using following query.
its working fine while using from selected database. but its not working when i m running from different database.
DECLARE @columnnames varchar(max)
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames
I need to pass database name dynamically like using by variable.
Is this possible to use variable after from clause. or any other methods?
eg:
DECLARE @columnnames varchar(max)
DECLARE @variable='db_month11_2014'
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from @VARIABLE.INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames
View 9 Replies
View Related
May 20, 2015
Assuming I have a table similar to the following:
Auto_ID Account_ID Account_Name Account_Contact Priority
1 3453463 Tire Co Doug 1
2 4363763 Computers Inc Sam 1
3 7857433 Safety First Heather 1
4 2326743 Car Dept Clark 1
5 2342567 Sales Force Amy 1
6 4363763 Computers Inc Jamie 2
7 2326743 Car Dept Jenn 2
I'm trying to delete all duplicate Account_IDs, but only for the highest priority (in this case it would be the lowest number).
I know the following would delete duplicate Account_IDs:
DELETE FROM staging_account
WHERE auto_id NOT IN
(SELECT MAX(auto_id)
FROM staging_account
GROUP BY account_id)
The problem is this doesn't take into account the priority; in the above example I would want to keep auto_ids 2 and 4 because they have a higher priority (1) than auto_ids 6 and 7 (priority 2).
How can I take priority into account and still remove duplicates in this scenario?
View 3 Replies
View Related
Jan 8, 2007
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
View 2 Replies
View Related
Jul 1, 2014
I have two tables that I need to combine, they are as follows:
AccountCheckInDT
3289442/14/2014 15:11
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35
3289442/19/2014 9:41
AccountCheckOutDT
3289442/14/2014 16:18
3289442/16/2014 0:27
3289442/19/2014 9:28
3289442/19/2014 10:00
This is how I need the end result to look:
AccountCheckInDT CheckOutDT
3289442/14/2014 15:11 2/14/2014 16:18
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58 2/16/2014 0:27
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35 2/19/2014 9:28
3289442/19/2014 9:41 2/19/2014 10:0
View 3 Replies
View Related
Nov 9, 2005
I'm trying to create a list of orders in my db that has been created correctly (some orders are not dealt with correctly...) An order should go from "open -> assigned" to "assigned -> responded" status.
I got the following query:
select org.name, count(order) AS correct, NULL AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
where
o1.status = 'Open -> Assigned'
and o2.status = 'Assigned -> Responded'
and o1.time_stamp < o2.time_stamp
This gives me a list of all organisations with the correct number of orders in the system...
But now I need to add the total number of tickets they got in the system. So I was thinking about a union with a query without the were constraints
UNION 'with the above query
select org.name, NULL AS correct, count(order) AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
..but that gives me a list like this:
name correct total
org1 324 NULL
org1 NULL 423
How can I combine them, or maybe doing it a better way?
View 3 Replies
View Related
Oct 28, 2014
I am trying to move data from one table to the another (staging to real time) in a stored procedure.
There are no indexes or primary keys on the target table and it is still taking ages to execute it (30 minutes approx.). There are no defaults, no constraints as well. There is one identity int column though.
There are some 500000 odd rows in the target table.
I am using the
Insert into..
Select from..
method.
View 2 Replies
View Related
Mar 19, 2015
IF OBJECT_ID('tTable') IS NOT NULL
DROP TABLE tTable
GO
CREATE TABLE tTable
(nRow_IdINTEGER IDENTITY NOT NULL PRIMARY KEY,
nParent_IdINTEGERNULL,
[Code] ....
gives:
nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C
I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows
4 4 Two A
5 4 Two B
6 5 Tow C
View 5 Replies
View Related
Sep 15, 2014
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies
View Related
Jan 13, 2015
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
View 4 Replies
View Related
Sep 15, 2015
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008
View 2 Replies
View Related
Jan 16, 2008
I have a simple sql select statement that looks like this. Select Column1+ ' ' + Column2 As SpecFROM Table both columns are varchar's and the output i get is something like this.Spec-----------------------AaBbCc What I would like to return as my results is this: Spec--------------------AaBbCc I hope this makes sense. I can I accomplish that?Thanks!
View 3 Replies
View Related
Apr 19, 2004
Hello,
I have a table which has the following structure:
ID MessageText
001 Hello
001 There
001 Working
003 See
003 you
003 Next
003 Time
How to build a query or store procedure to return result like this:
ID MessageText
001 Hello There Working
003 See you Next Time
Your help/advice is greatly appreciated.
Thanks, Ficisa
View 14 Replies
View Related
Nov 14, 2006
Table users:
userid, name, added...
Table groups
groupid, groupname...
Table groupadmins:
userid, groupid
The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'
If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users
then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?
View 4 Replies
View Related
Feb 9, 2015
I am trying to insert the queries in a table such as the following, not able to insert it.
create table test (txt Varchar(200))
insert into test values('select *from master.dbo.sysprocesses WHERE status NOT IN('sleeping','background')')
View 4 Replies
View Related
Apr 6, 2008
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
View 3 Replies
View Related