Here's my issue. I've got a series of tables like so:
dbo.CustomerBobJones
dbo.CustomerJaneDoe
dbo.CustomerBrianSmith
Each contains these columns:
DateOfComment datetime
CommentText varchar(200)
Now all other customer information is stored in another table dbo.CustomerList
CustomerID int
CustomerName varchar(50)
Address varchar(50)
and so on.
I need to join from this customer list to the individual tables for those customers.
My thought was to add to dbo.CustomerList the name of the table associated to that account... but I'm not sure once that information is entered how I'll be able to join between them.
I had thought that the id in sysobjects might be the key but I'm not sure how to join using an id instead of a table name.
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
USE [International_Forecast_New] GO /****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this Period Quarter Year Period Start Period End 2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores' storeid doc article storeid doc article ALL 0010 001 101 0010 001 ALL 0010 002 101 0010 002 ALL 0011 001 102 0011 002 ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[Â Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
Is it possible to insert data into a table from a temporary table that is inner join? Can anyone share an example of a stored procedure that can do this? Thanks, xyz789
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
I have one main Table "MainTable" which I want to relate with "subTable1, subTable2, ..." in such a way that:
"ith subTable" have to be related/joind on "ith row" of the "MainTable", "jth subTable" have to be related/joined on "jth row" of the "MainTable" and so on...
What I want Actually?
I want that when ever I delete a Record in the "MainTable", The corresponding "subTable" have to be deleted Itself.
I thought a solution that, I can cerate a Trigger on Delete of the "MainTable" and it would delete the corresponding "subTable". But I dont know how to ceate that.
Secound solution what I thought is, that may be there is some majic power in the Table Joinings. That I might join "MainTable" row with "subTable" ( ofcourse that I dont know either :))
So my question is, that what is the actual solution for my problem?
What ever solution is please give me a sample also with that. Like in a Trigger how can I write some Expression which can delete the "subTable" for the Currunt delete Row.
I was writing a query using both left outer join and inner join. And the query was ....
SELECT Â Â Â Â Â Â Â S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Â Â Â Â Â Â Â Production.Suppliers AS S LEFT OUTER JOIN Â Â Â Â Â Â (Production.Products AS P Â Â Â Â Â Â Â Â INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT Â Â Â Â Â Â Â S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Â Â Â Â Â Â Â Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier   country   productid   productname   unitprice   categorynameSupplier QOVFD   Japan   9   Product AOZBW   97.00   Meat/PoultrySupplier QOVFD   Japan  10   Product YHXGE   31.00   SeafoodSupplier QOVFD   Japan  74   Product BKAZJ   10.00   ProduceSupplier QWUSF   Japan   13   Product POXFU   6.00   SeafoodSupplier QWUSF   Japan   14   Product PWCJB   23.25   ProduceSupplier QWUSF   Japan   15   Product KSZOI   15.50   CondimentsSupplier XYZ   Japan   NULL   NULL   NULL   NULLSupplier XYZ   Japan   NULL   NULL   NULL   NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
Orders, with OrderID as primary key, a code for the client, and a code for the place of delivery/receipant.
Both the client and place of delivery should be linked to the table:
Relations, where each relation has it's own PrimaryID which is a auto-numbered ID. Now I want to substract my orders, with both the clientcode, and the place of delivery code linked to the relations table, so that for both the name and adress is shown.
I can link one of them by:
InnerJoin On Orders.ClientID = Relations.ClientID, but it's not possible to also link to the ReceipantsID. Is there a way to solve this?
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Simple example:    declare @tTable(col1 int)    insert into @tTable(col1) values (1)    select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
Here is the scenario, I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable
-- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Hi, I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table. Overall it is like storing the history of the table updation. How do i write a stored procedure for this? Anybody has done this before? Pls help me.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth How can I get the keepmonth? can I use parameters ?
For discussion sake, I've got two tables: customers and orders. What I wantis a query that will return the most recent order for each customer. Let'ssay the tables are like:Customer: customerId, customerNameOrders: orderId, customerId, orderDateThe simple joins I've done give me back all of the orders for all of thecustomers.Any advice?
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
I am attempting to join 2 table variables on 2 keys, employeeID and employerID
SELECT * FROM @employees INNER JOIN @addresses ON ((@employees.employeeID = @addresses.employeeID) AND (@employees.employerID = @addresses.employerID))
Both tables are declared with proper fields and they are populated with the correct data; I get this error msg upon execution.
Server: Msg 137, Level 15, State 2, Line 28 Must declare the variable '@employees'.
All, I have two tables, A and B. Table A has names, address, phone. Table B has names and image filename. Table A and B can be linked by names. I would like to show all the names in Table A and link to Table B such that it will show a column image filename if there is a image filename. If the name cannot be found in the Table B display a NULL on the column. (Note : there may be two same names in the Table B, so how can i show only the first image filename).
