How To Select From Tables To A File?
Jul 20, 1999I need to select data from within a stored procedure into a file, then FTP the file. Do I need to select it into a temporary table and bcp it or is there a better way?
View 1 Replies
ADVERTISEMENT
Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?
Dec 4, 2007I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
Saving Tables That Are Generated By Queries As HTML File Or Sub-tables
Oct 17, 2006I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.
The table has columns like Commodity, Unit, Quantity, Value, Month, Country
A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"
The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.
It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.
I wanted to know if there is an alternate way to pull the data from server ?
I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??
OR
Can I write a script that creates a html files for each table for all input combinations save them ?
OR
Is there exists any other solution ?
Generate A Separate Txt File For Each Account In A Table, Need To Join Tables To Get Details, And Specify Output File Name?
May 16, 2008Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Oct 15, 2007Hello,
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
How To Select From Two Tables....help, Please....
Dec 11, 2006Hello. What's the correct way of declaring a condition that selects two tables,with the following condition? Here's my code, it does not work.
SelectCommand="SELECT * FROM [table_1, table_2] WHERE table_1_data IS NULL, table_2_data IS NULL"
table_1_data is from table_1.
table_2_data is from table_2.
Thanks.
Select From Different Tables
Jun 19, 2008Hi I will be thankful if any one help me with the queryI have 5 tables InventorySalesInvoiceMasterInventorySalesMasterInventorySalesInvoiceSalesDeliveryNodeIds with fields salesInvoiceId and salesDeliveryNoteIdInventorySalesReturnMasterInventorySalesInvoiceSalesReturnIds with fields salesInvoiceId and salesReturnIdI want to retrive datas from InventorySalesInvoiceMaster and the below query works fine but if salesReturnId is not present for a salesInvoice the qurey is not returning any value the query is select ISIM.salesInvoiceId,ICM.customerName,ISIM.salesInvoiceDate from InventorySalesInvoiceMaster ISIM,InventoryCustomerMaster ICM,InventorySalesMaster ISM,InventorySalesInvoiceSalesDeliveryNodeIds ISISDNID,InventorySalesInvoiceSalesReturnIds ISISRNID,InventorySalesReturnMaster ISRM where ISIM.customerId=ICM.customerId and ISM.salesId=ISISDNID.salesDeliveryNoteId and ISRM.salesReturnId=ISISRNID.salesReturnId and ISIM.salesInvoiceId=ISISRNID.salesInvoiceId and ISIM.salesInvoiceId=ISISDNID.salesInvoiceId and ISIM.salesinvoiceId=32Thanks in AdvanceAnu Palavila
View 3 Replies View RelatedSelect From 3 Tables
Jul 17, 2006Hi, I've got a problem... Let's say I have 3 tables each with only one column and the following information:
TABLE_A
A
B
C
TABLE_B
X
TABLE_C
G
H
I want to make a select of the three tables to have the following result:
COLUMN_A, COLUMN_B, COLUMN_C
A, X, G
B, , H
C, ,
If I do the following select:
select isnull(a.column_a, '') as column_a,
isnull(b.column_b, '') as column_b,
isnull(c.column_c, '') as column_c
from
table_a a, table_b b, table_c c
I get all the possible combinations as a result.. any ideas?
Select From 3 Tables
Sep 30, 2013I'm using this statement to get info from 2 tables:
select holiday_notes.*, lookup.lookup_desc as type_desc from holiday_notes left join lookup on holiday_notes.[type]=lookup.lookup_id
where holiday_id=@holiday_id and delete_date is null order by create_date desc
It uses a specific id (holiday_id) to get the notes for that id.
What I need is all records from holiday_notes table, then the type (from holiday_id table) and also the holiday_name from Holiday_Ref table (which uses the holiday_id from holiday_notes table).I guess it's another join on the holiday_ref table? right join?
SELECT From Two Tables Using LIKE
Dec 16, 2007I'm using MS SQL 2005 server with two tables:
table 1
-------
code code_description sub_category notes
SH Shirt (Made in USA) x 20
HA Hat (Factory) x 36
SH Shirt (Made in China) x 24
table 2
-------
code code_description retail
---- ---------------- ------
TR Trousers 15.00
SH Shirt 10.95
I wish to be able to SELECT from both tables where code is like %S% and give the result:
SH Shirt
SH Shirt (Made in USA) x 20
SH Shirt (Made in China) x 24
How can I do this please?
Select From Tables
May 13, 2006hi,
i have created tables and i want to use a store procedure to get information from almost all of the tables...
lets say i have 4 tables - students, teachers,subjects and presence in classes.
students includes - name+phone+addresas+id
teachers includes - name+phone+degree+address+id and so on..
subjects includes- name+code+hours and so on
presence includes- id_student+id_teacher+code_subject+date and so on
now i want to create a table with all the students name , tachers name , subjects name what are in the presence table.
how can i get all of that information ? is it possible to write in one select statement?
what is right way to do it in a store procedure?
thnaks in advanced
Select Multiple Tables
Jul 9, 2007Hi there,I want to select records from 3 tables. In SQL Server 2005, I'm using of "For XML" like this:Select *, (Select * From dbo.PageModules Where (PageId = 1) For Xml Auto) As Modules, (Select * From dbo.PageRoles Where (PageId = 1) For Xml Auto) As Roles From dbo.PagesThat works fine in SQL 2005 but not in SQL 2000, Because SQL 2000 does not support nested "FOR XML".Is there any way for selecting records from multiple tables by a query?Thanks in advance
View 4 Replies View RelatedUpdate And Select From Two Tables.
Mar 10, 2008Is there some sort of sql command where a tuple in a table has one of its cells updated depending on the value of a cell from another table. Please I would appreciate some help.
Thanks
SELECT Statement For 2 Tables
May 8, 2004Hi
How do I make a SELECT statement to use in WebMatrix to access data that selects from 2 tables:
based on LastnameID it selects from table Contacts
and based on first name and Lastname it selects from student tables
regards
daniel
Select All Tables With Rowcount !!
Jul 27, 2001Folks !!
Can someone suggest a select statement for Display of all the tables in the Db with their Row Count ?
thanks
Girish
Better To Join Tables In DB Then In SELECT?
Feb 20, 2006In my new job I have to administer an existing SQL-database with approx. 50 tables. In this database are no joins :confused: defined between the tables. We use a Visual Basic 6 application to create a GUI and within this VB6 app. there are several SELECT statements to retrieve the required data. In these SELECT statements are all the INNER and OUTER JOINS between the tables defined.
My question: is this a correct way to work with or is it better to create all the JOINs between the tables on the database itself? Or should I create different views and define the JOINs in there? My main concern is the speed to retrieve data and second the required time to administer this database.
SELECT Statement Between Two Tables
Jan 28, 2005I have two tables
Absent
==========
absent_id
employee
Employee
==========
emp_id
employee
department
In plain english I want to select * from absent where the employee's department (in the employee table) equals 'whatever'
Can anyone help please.
Select Tables Where Name Begins With 'a'
Oct 4, 2005how would i go about selecting data from a table...using the first letter of the staff_name field...
eg: i have a list at the top of my page..links, A - Z...for a staff directory page...and i want it so when you click on a link (A - Z), it display's only the people whose name begins with the letter clicked..
ive tried working my head arround it, using the left function to get the first letter...but i dont know how to structure my queries or my loop...
would anyone have any suggestions about this??
Thanks, Justin
Select From Multiple Tables
Feb 24, 2012I have three tables.
Table USERS Contains columns User_id and UserName
Table DOMAIN Contains columns Domain_id and DomainName
Table USER_DOMAIN Contains columns User_id, Domain_id, count, day, month, year
I am looking to run a report that pulls its information from USER_DOMAIN but instead of displaying User_id, Domain_id, it returns the UserName and DomainName associated.
The query to pull the info i need is very simple, where i am having problems is linking the user_id to the UserName and the Domain_id to the DomainName.
Select Into Temporary Tables
Jan 28, 2004on sql-server-performance.com i read :
Do not create temporary tables from within a stored procedure that is invoked by the INSERT INTO EXECUTE statement. If you do, locks on the syscolumns, sysobjects, and sysindexes tables in the TEMPDB database will be created, blocking others from using the TEMPDB database, which can significantly affect performance. [6.5, 7.0, 2000] Added 9-1-2000
I have a question does this negative effect also include simple SQL commands apart from stored procedures.
For example if from vb i execute a "Select into" temporary table. Will this have the same negative impact as with executing this from a stored procedure ?
Thank you very much
Insert To Two Tables With Select
Oct 30, 2013I have those 3 tables (sales,source, sales, sales_details),
Sales_source (table)
id, name
1 Office
2 Post
3 Web
Sales (table)
id, saleid, customer, source, date
01, 230, bill din, 1, 2013-10-22
Sales_details (table)
id, saleid, object, delivery, date
01, 230, card, no, 2013-10-23
02, 230, box, yes, 2013-10-24
03, 230, car, no, 2013-10-31
And I want to create if is possible one insert command
And make the user complete the details of Sales_details (that they have inner join for the sales_source table so he can see only the sales_source_name and behind the sales_source_id to be stored)
And after to be able to add in as many [sale_details] he need for the select sale…
Is this possible to be done with one command ???
I think is not possible as i have made a small research on the web, and i will need to make one insert for the sales (table) independent and after to programming i guess a way to open the sale_details and add the rest….
Select 2 Related Tables
May 25, 2006hi this is probably a dumb question with an easy answer but does anyone know how to do this...
2 tables, one is a supplier list (id, supplier_name) and the other is an order list (id, order_name, supplier_id... etc)
basically i need to join the 2 tables together (id=supplier_id) but i need to display the results without the supplier_id and the supplier table id, only the supplier name?
do i have select only the specific fields I want? or can I exclude the supplier_id and the id from the supplier table?
How Can I Run Select Statement For Two Tables That
Jul 2, 2007Hi all,
How can I run select statement for two tables that are on two different databases?
Let's say I have a SQL server 2005 on Windows 2003 server.
On this SQL server 2000 I have two databases.
Let’s say DB1 and DB2. On db1 I have Tbale1 and on DB2 I have Table2.
I like to run a select statment:
Select Table1.*, Table2.* from Table1, Table2
Where table1.id = Table2.id
And Table1.user_name like '%jim%'
Both tables have same architecture.
Thanks for any help.
Abrahim
SELECT On Multiple Tables Help
Jul 20, 2007Hi all! I just registred (very nice site) and have problem with getting some data from multiple tables, I would like to get result in one result set and best would be in one sql query.
I have DB for miniMessenger proggy, what i try to do is retrieve list of contacts.
Table containing user account information.
CREATE TABLE `account` (
`id_account` mediumint(8) unsigned NOT NULL auto_increment,
`userdata_id` mediumint(8) unsigned NOT NULL default '0',
`login` varchar(15) NOT NULL default '',
`pwd` varchar(15) NOT NULL default '',
`messenger_id` mediumint(8) unsigned NOT NULL default '0',
`logged` tinyint(1) NOT NULL default '0',
`ost_login` varchar(11) default NULL,
PRIMARY KEY (`id_account`),
UNIQUE KEY `messenger_UN` (`messenger_id`),
UNIQUE KEY `userdata_UN` (`userdata_id`)
)
INSERT INTO `account` VALUES (1, 1, 'User', 'fatimah', 4118394, 0, NULL);
INSERT INTO `account` VALUES (2, 2, 'Admin', 'haslo', 3333333, 0, NULL);
Contact list, first field is contact number (like 4356789 - MESSENGER id) next to this number is its contact number, auth - if contact was authorised, ban selfexplained :) I just take every row with number 4356789 and get contact numbers next to it.
CREATE TABLE `contacts` (
`contact_id` mediumint(8) unsigned NOT NULL default '0',
`contacts` mediumint(8) unsigned NOT NULL default '0',
`auth` tinyint(1) unsigned NOT NULL default '0',
`ban` tinyint(1) unsigned NOT NULL default '0',
KEY `Contacts ID` (`contact_id`)
)
INSERT INTO `contacts` VALUES (4118394, 3333333, 1, 0);
INSERT INTO `contacts` VALUES (4118394, 1234567, 0, 1);
Its table for messenger data, ID, status of contact (offline,online,ect), description, chat archiwum,
CREATE TABLE `messenger` (
`id_messenger` mediumint(8) unsigned NOT NULL default '0',
`status_id` tinyint(3) unsigned NOT NULL default '0',
`description` varchar(255) NOT NULL default '',
`archiwum` mediumtext NOT NULL,
PRIMARY KEY (`id_messenger`)
)
INSERT INTO `messenger` VALUES (1234567, 0, '', '');
INSERT INTO `messenger` VALUES (3333333, 1, '', '');
INSERT INTO `messenger` VALUES (4118394, 2, '', '');
Status is enumeration of status states(off,on,brb ect).
CREATE TABLE `status` (
`id_status` tinyint(3) unsigned NOT NULL default '0',
`stat` varchar(15) default NULL,
PRIMARY KEY (`id_status`)
)
INSERT INTO `status` VALUES (0, 'offline');
INSERT INTO `status` VALUES (1, 'Online');
INSERT INTO `status` VALUES (2, 'brb');
What i want to get is contact list + additional info of specific user by its messenger id. Like:
id_messenger,contacts,auth,ban,stat
which is userID, contact ID, authorisation, ban, status
My query looks like this:
SELECT id_messenger,contacts,auth,ban,status_id
FROM account,messenger,contacts
WHERE account.login = 'User'
AND messenger.id_messenger = account.messenger_id
AND contacts.contact_id = messenger.id_messenger
And it shows in stat only status of user of which i retrieve contact list. Please help me, im tired of working on this, im sure it is trivial :(
thx in advance!
Select All From Two Many-to-many Related Tables
Oct 11, 2007Hello, everyone!
Please give your ideas about how to create a query from two tables, which have many-to-many relationship. For example, we have table Book and table Author and consider, that one author had wroten several books and one book had being wroten by several authors. So we have many-to-many relationship of these two tables.
Usually this sutuation resolves by maens of creating the third table AuthorBook, which consist of key fields from the Author table and key fields from the Book table. So that the Author table is the parent table to AuthorBook, and the Book table is the parent table to AuthorBook. So we have two relations one-to-many. Now the question is how to get all related values from both tables, using JOIN operation and the conjunctive table?
I would greatly appreciate any answers
Using Select * From Multiple Tables
Nov 8, 2007Can someone explain to me why this would be considered "bad"? One thing that pops in my mind is that I really don't need all the columns from all these tables, only specific columns. Would this cause a performance issue when used in a stored proc for a transactional app?
SELECT *
FROM CASE_XREF CX, CASE_RENEWAL_XREF CRX, RENEWAL_BATCH RB, PROPOSAL P
WHERERB.MKT_SEG = @MKT_SEG
AND RB.CORP_ENT_CD = 'oh'
AND RB.RENEWAL_DT = '01/01/2008'
AND CRX.TRIGGER_TYPE_CD = 'P'
AND RB.BATCH_ID = CRX.BATCH_ID
AND CRX.CASE_ID = CX.CASE_ID
AND CRX.REN_PROSPECT_ID = P.PROSPECT_ID
AND CRX.REN_PROP_NUM = P.PROP_NUM
AND P.PROP_STATUS <> 'C'
AND CX.ACCT_NBR = 123152
Select From Multiple Tables
Dec 18, 2007I have four tables. now i need to select the rows from all the four tables.
TABLE1: -Job
jobno
mtid
prid
mtpath
prpath
TABLE2: - livestaff this id will be store in Job
staffid
staffname
teamid
active
TABLE3: -masterstaff
mstaffid
staffname
teamid
active
TABLE4: -staffrel
masterstaffid
livestaffid
Now i need to select * from job and staffname from masterstaff and teamid from livestaff.
Please help
____________
Praba
Select Data From Two Tables
Jan 17, 2008I have numerous tables in my db and would like to do this: (not sql below just simple explanation'
select contacts from wce_contact where idstatus is like 'london%' and select the same contacts where notes are not blank in the table wce_history
Hope it makes sense?
I just don't know how to query two tables at once...
SQL Select Records NOT In Both Tables
Aug 7, 2005HiI'm using Access 2002. I have 2 tables tblGroupContact,tblGroupPermission, both have 2 fields identical structure:ContactID GroupID (Both are Composite keys and both hold integers)tblGroupContact holds everybody and the groups they are members of.tblGroupPermission holds only those people who have permission to makechanges to another part of the DB.The SQL at the end of post works, but opens a dialogue box looking fora parameter value 'query1.ContactID'Clicking enter or cancel (without entering anything) works OK.What have I done wrong to cause this parameter request.Thanks ColinKSELECT tblGroupContact.ContactID, tblGroupContact.GroupIDFROM tblGroupContact LEFT JOIN tblGroupPermission ON(tblGroupContact.ContactID = tblGroupPermission.ContactID) AND(tblGroupContact.GroupID = tblGroupPermission.GroupID)WHERE (((tblGroupPermission.ContactI*D) Is Null) AND((tblGroupPermission.GroupID) Is Null));
View 3 Replies View RelatedQuery - Select Two Tables Where A.id=b.id
Dec 28, 2007Hi,my query is:SELECT a.symbol_art,b.node_keyFROM artykuly a, art_podz bWHERE a.symbol_art=b.symbol_art------------- -------------------table: artykuly table art_poda.symbol_art b.symbol_artAA-0001 AA-0001 = record okAA-0002 NULL = >>>>>>>>I want to view diferencerecords symbol_art in two tablesHow select all a.symbol_art where in table art_podz b.symbol_art is no exists?tnx.Tom
View 2 Replies View RelatedSelect From Multiple Tables
Aug 8, 2007Basically I have 5 tables. These are...
1/ RCPCrossRef
2/ RCPPositionData
3/ RGCrossRef
4/ RGData
5/ RComments
------------------------------------------------------
RCPCrossRef and RCPPositionData are related by these keys:
RCPPositionData.UniquePositionID = RCPCrossRef.CPPositionID
RGCrossRef and RGData are related by these keys:
ON RGData.PositionID = RGCrossRef.GPositionID
-----------------------------------------------------------
RCPCrossRef and RGCrossRef are related by these keys:
ON RCPCrossRef.GMatchID = RGCrossRef.GMatchID
But RCPCrossRef may also contain a NULL value for this key meaning no relationship exists for that row.
----------------------------------------------------------------
Finally RComments is related to RCPCrossRef and RGCrossRef by these keys...
ON RComments.GPositionID = RGCrossRef.GPositionID
ON RComments.CPPositionID = RCPCrossRef.CPPositionID
But again, one of these columns in the RComments table could contain a NULL value meaning no relationship exists for that row of data.
---------------------------------------------------------------
So my aim is to display ALL DATA for each of these tables.
Tried the below but doesn't return any rows...
Code SnippetSELECT gd.Quantity, c.Comments,
gc.GPositionID, cc.CPPositionID, cd.PositionDate
FROM ReconComments AS c
INNER JOIN
RGCrossRef AS gc
INNER JOIN
RGData AS gd
ON gc.GPositionID = gd.PositionID
ON c.GPositionID = gc.GPositionID
INNER JOIN
RCPData AS cd
INNER JOIN
RCPCrossRef AS cc
ON cd.UniquePositionID = cc.CPPositionID
ON c.CPPositionID = cc.CPPositionID
WHERE gc.ForcedMatch = 'yes' AND cc.ForcedMatch = 'yes'
Thanks.
Select And Then Inserting Results Into Two Different Tables
Dec 2, 2003Hello!
I've got the following procedure:
ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS
select
A_ProspectPipeline.ID,
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())
then '1'
else '13'
end
end)as [CY],
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())+1
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())+1
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())+1
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())+1
then '1'
else '13'
end
end)as [NY]
from a_ProspectPipeline where A_ProspectPipeline.ID = @ID
What i need to do is insert the two returned values [NY] + [CY] into two different tables.
Can anyone help me with this?
Select Statement Help:How Do I Query Two Tables
Mar 20, 2006How do I Query two tables and minus the result to be displayed in a gridview. I will appreciate all the help that I can get in this regard. Find below my two select statement
1st Select StatementDim SelectString As String = "SELECT DISTINCT [Course_Code], [Course_Description], [Credit_Hr], [Course_Type], [Course_Method] FROM [MSISCourses] WHERE (([Course_Type] = Core) OR ([Course_Type] = Information Integration Project) "If radBtnView.Checked = True ThenSelectString = SelectString & " OR ([Course_Type] = 'Knowledge')"End IfIf chkGView.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Data Management')"End IfIf chkGView2.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'General')"End IfIf chkGView1.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Electronic Commerce')"End IfIf chkGView3.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Network Administration and Security')"End IfIf chkGView4.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Healthcare Information Systems')"End IfSqlDataSource3.SelectCommand = SelectString
2nd Select Statement"SELECT DISTINCT [Co_Code], [Co_Description], [Cr_Hr], [Co_Type], [Co_Method] FROM [StudentCourses] WHERE ([Co_Code] = StdIDLabel)"
my gridview<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="Course_Code" DataSourceID="SqlDataSource3" GridLines="Horizontal"><Columns><asp:BoundField DataField="Course_Code" HeaderText="Course_Code" ReadOnly="True" SortExpression="Course_Code" /> <asp:BoundField DataField="Course_Description" HeaderText="Course_Description" SortExpression="Course_Description" /> <asp:BoundField DataField="Credit_Hr" HeaderText="Credit_Hr" SortExpression="Credit_Hr" /> <asp:BoundField DataField="Course_Type" HeaderText="Course_Type" SortExpression="Course_Type" /> <asp:BoundField DataField="Course_Method" HeaderText="Course_Method" SortExpression="Course_Method" /> </Columns></asp:GridView>