Union Of 2 Tables With Differnt Number Of Rows

May 24, 2006

I have 2 tables

tblOpenSiteDates
Site: int
OpenDate: smalldate
Comment: VarChar

tblCloseSiteDates
Site: int
CloseDate: smalldate
Comments: VarChar
newLocationID: int

I am trying to get a view which would display a site with the open and close dates. Null is ok for a close date for those opens that are still open.. not every office has an open date and close date.. it is possible to have just a close date and not an open date (ie unsure of open date but I know its closing)

so the output would be

viewOpenCloseSites
Site: int
opendate: smalldate
closedate: smalldate

PLEASE HELP i just can not figure it out

thanks...

View 1 Replies

Count The Number Of Rows In A UNION ALL Statement

Jan 8, 2007

Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark

View 1 Replies View Related

Rows From Two Differnt Databases

Jun 1, 2001

how to pick rows from two tables in two differnt databases

View 1 Replies View Related

Inserting Into Two Differnt Tables

Sep 21, 2007

Hi, I have two different tables that I want to insert into, i have the code (as below) my only problem is i want to insert a qty into tbl_multi and a different qty into tbl_stock, how do i do this. I have been comfusing myself with it all afternoon, maybe someone can point it out for me int i = 0;
int scount = 0;for (i = 1; i <= 5; i++)
{if (((TextBox)FormView1.FindControl("txtP1_" + i.ToString())).Text != "")
{string sConnMAXID = "Data Source=sql380.mysite4now.com;Initial Catalog=nashdfDB1;User ID=******;Password=******";
SqlConnection objConnOID = new SqlConnection(sConnMAXID);int intStockIDMAX = (Convert.ToInt32(Session["intMAXSID"])) + 1;
int intQtyP1 = Convert.ToInt32(((TextBox)FormView1.FindControl("txtP1_" + i.ToString())).Text);int intLocationP1 = Convert.ToInt32(((DropDownList)FormView1.FindControl("ddlP1_" + i.ToString())).SelectedValue);
scount = intQtyP1 + scount;using (objConnOID)
{
objConnOID.Open();string sqlInsert = "INSERT INTO tbl_stock_multi_location(stock_ID, qty, location) " + "VALUES (@stock_ID, @qty, @location);"; <<<<<<<<<<<<<<<< need to try and add the SQL onto the end here
//INSERT INTO tbl_stock(qty)" +// "SELECT qty AS SQTY FROM tbl_stock VALUES (@SQTY);";

SqlCommand objCmd1 = new SqlCommand(sqlInsert, objConnOID);
objCmd1.Parameters.AddWithValue("@stock_ID", intStockIDMAX);
objCmd1.Parameters.AddWithValue("@qty", intQtyP1);objCmd1.Parameters.AddWithValue("@location", intLocationP1);
// objCmd1.Parameters.AddWithValue("@tbl_stock.qty", scount); <<<<<<< this was the param that i was playing about with
// either will work
// objCmd1.ExecuteNonQuery();
objCmd1.ExecuteScalar();
objConnOID.Close();

View 1 Replies View Related

DTS Of Data Between Two Tables, Same Field Names But Differnt Datatypes

Jul 23, 2005

I need to migrate data from one sql database to another. The second DBis a newer version of the "old" database with mostly the same tablesand fieldnames. In order support some reporting queries in the "new"version I needed to change the datatype of a few fields from varchar toint(the data stored was integers already as they were lookup tables).DTS works great except in the cases of about 10 fields which I changedthe datatypes on from varchar to int.DTS seems to drop the data if the fieldname and datatype are not anexact match. Is there any way to use DTS and have it copy data from afield call subsid type varchar to a field call subsid type int?

View 3 Replies View Related

Sorting Tables By Number Of Rows??

Apr 6, 2006

Hello , I have 870 tables in a database and I need to sort tables by row numbers to get the ones having biggest number of rows , is there a query?

F16 L�?GHT�?N�?NNNG

View 3 Replies View Related

How To Manage IDENTITY In Tables With Large Number Of Rows?

May 23, 2002

Table structure: col1 IDENTITY (seed=1 increment=1) + few other columns (col2...col7) + one text column (col 8)
I have around 50,000,000 rows per day inserted in the table T1. At the end of the day 40,000,000 rows are deleted. I have to keep the records for 12 months and then archive it. Database is 24/7 web serving and there is no down time allowed. IDENTITY column will go out of range (overflow) after less than two years, unless the identity seed is reset to the start value (seed=1, increment=1).
At the end of 12th month data is archived in another table and only last month is kept in the table T1. So table T1 enters new year with data from last month of the previous year. There are few other tables that refer to this table by using there own field with values from T1.IDENTITY column (referential integrity is not enforced). Identity column in T1 is needed as a unique id for some search actions. Performance is an issue therefore bigint data type is used for this identity column rather than decimal.

Another problem I have is how to do table update on one column (1 mil rows to be updated out of 2 mil of rows) with the minimum impact on the users who are querying this table heavily. Not need to mention that it is web app 24/7 no down time.

Thank you in advance.

Goran

View 1 Replies View Related

Union All Does Not Union All Rows

Nov 6, 2006

Hi all,

I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.

I don't understand, I've used the Union All transform many times and I've never seen this.

Any idea why this could happen ?

View 18 Replies View Related

How Do I Put Records Number In Union

Dec 21, 2007

Hi i have sql statement like this :

SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_a
UNION
SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_b

the problem is the rec_num repeat for each statement like this :

rec_num empname

1 john
2 maggy
1 lee
2 mary
3 louis

How do i make the rec_num continue for the next statement after union.

View 1 Replies View Related

Using Top To Get Set Number Of Results From A Union

Jan 30, 2008

I would like to get the top 5000 records from a union. The oringinal query is

SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0

I thought that the I could do:
SELECT TOP 5000 * FROM
(

SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
}

This gives me a syntax error that I cannot figure out.

Any ideas?

View 3 Replies View Related

Query With Row Number,Union And Dynamic Orderby.

Dec 19, 2007

Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.

Existing procedure:

ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)

select @Cond=''

if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)

if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow

exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int

select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl

set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;

SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')

RETURN

can anybody help me pls. ITs very urgent.

Thanks in advance.

View 1 Replies View Related

Error Using Union All With Row Number Transformation From SQLIS(.com)

May 14, 2008

Has anyone else experienced similar problems? I've been trying to use "Row number" from sqlis.com site with "Union All" but no lucks.

[DTS.Pipeline] Error: component "Union All" (1840) failed the pre-execute phase and returned error code 0x80070057.

thanks

View 6 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Columns Differnt Values

Apr 1, 2008

How can we write a script that will provide me colmumns and all differnt set of values for those columns...

there are 300 plus columns all together.

View 11 Replies View Related

Union Join To Return Multiple Rows Into Columns.

Feb 19, 2008

I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.

101 dues Mem 100
101 Vol charity 200
101 chapt CHi 300

I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.

101 mem 100 charity 200 chi 300

Error:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'PRODUCT_CODE'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.

SELECTp.ID,
p.PRODUCT_CODE as Chapt,
p.product_code as Dues,
p.product_code as Vol
from (
SELECT ID,
product_code as Chapt,
Null as dues,
Null as Vol
from subscriptions
where prod_type = 'chapt'
AND BALANCE > 0

union all

SELECT ID,
Null as chapt,
product_code as Dues,
Null as vol
from subscriptions
where prod_type = 'dues'
AND BALANCE > 0

union all

SELECT ID,
Null as chapt,
Null as dues,
product_code as Vol
from subscriptions
where prod_type = 'vol'
AND BALANCE > 0

) AS p
GROUP BY p.id

View 9 Replies View Related

UNION OF 2 TABLES - CAN'T GET SUM

Sep 7, 2005

I am trying to get a consolidated sum of all the columns of the two tables that I am Using the UNION on. I can not get the sum function to work or the group by.

SELECT T2.State,t2.Taxcode,
Taxable =
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else 0
End,
'NonTaxable' =
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else T1.DistribSum
End,
T1.VatSum as 'Total Tax', (T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM inv1 t1 inner JOIN oinv t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
UNION ALL
SELECT t2.state,t2.taxcode,
Taxable =
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then -(T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else 0
End,
'NonTaxable' =
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else T1.DistribSum
End,
-T1.VatSum as 'Total Tax', -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM RIN1 t1 inner JOIN ORIN t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
order by T2.STATE

View 2 Replies View Related

Union And Join With Three Tables

Sep 17, 2007

Hi,
I have three tables named as BroadCastetails,PaymetMaster,MemberMaster.
I have two query as mentioned below--
SELECT MemberMaster.FirstName, MemberMaster.LastName, BroadCastDetails.BroadCastName FROM BroadCastDetails INNER JOIN MemberMaster ON 5 = MemberMaster.MemberID AND BroadcastCreationDateTime between '01/01/2007' AND '12/12/2007'
SELECT BroadCastDetails.ScheduledStartDateTime, BroadCastDetails.TotalCalls FROM BroadCastDetails UNION SELECT PaymentTransaction.TransactionDate, PaymentTransaction.CallsToCredit FROM PaymentTransaction

I want the result of the above two query in a GridView.How can I do that ? Its urgent...

View 2 Replies View Related

Join Or Union Two Tables?

Nov 18, 2003

Hi,

I have an Orders Table and a Freeshipping Table.

ORDERS
======

CustomerId OrderDate Quantity
========== ========= ========
1000 01/01/2003 5
1000 01/04/2003 9
1000 01/08/2003 14
2000 01/01/2003 4
1000 06/03/2003 9
4000 05/02/2003 4

FREESHIPPING
=============

CustomerID FreeDate
========== ========
1000 01/01/2003
1000 01/03/2003

How can I write a query that will return the following result to show the order details for customerid 1000
for a date range between 01/01/2003 and 01/08/2003

OrderDate Quantity FreeShipping
========= ======== ============
01/01/2003 5 Y
01/03/2003 0 Y
01/04/2003 9 N
01/08/2003 14 N

Note that even if an order was not placed and the date was a freeshipping date, it is still displayed in the resultset

Thanks in advance,
-ron

View 2 Replies View Related

SQL 2000 2 Tables Using UNION

Mar 27, 2006

I have 2 Tables one called Reps and the other called STORES
I need to use a UNION statement but my Server is saying that the "UNION" is ERROR The Query Designer does not support the UNION SQL construct.

What command would I use to put these 2 tables together?
HELP!

View 5 Replies View Related

Union 2 Temp Tables

Nov 8, 2007

I have 2 temporary tables from a previous operation, Tab1 and Tab2, with the same dimensions. How do I create a third table Tab3 with the same dimensions containing the the combined rows of the 2 previous tables? TIA!

Tab1
Col1 Col2 Col3
A1 B1 C1
A2 B2 C2

Tab2
Col1 Col2 Col3
X1 Y1 Z1
X2 Y2 Z2
X3 Y3 Z3

After the required sql operation I should have

Tab3
Col1 Col2 Col3
A1 B1 C1
A2 B2 C2
X1 Y1 Z1
X2 Y2 Z2
X3 Y3 Z3

View 7 Replies View Related

WHERE Command On UNION Tables

Feb 19, 2008

Hi there, I have some identical tables that I want to query for a search Is there anyway I can execute the unions first then a where command on all the tables at once I have tried using go but it doesn't seem to work, so I put the where statemtents at the end of each union for now. Here's my code:

strSQL = "SELECT * FROM england WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM ni WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM wales WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM scotland WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%'"

View 15 Replies View Related

Order By In Union - Data In 2 Tables

Feb 10, 2015

This query works perfectly and orders by just as I need

Code:
Select
'1st' As [Type],
#Uno.ID
#Uno.Address,
#Uno.shippingInfo
FROM #Uno

[Code] ....

However, when I use it in a Union All so I can pull data from 2 diff tables, the order by statement no longer works. How can I order by data in 2 tables?

Code:
Select
'1st' As [Type],
#Uno.ID
#Uno.Address,
#Uno.shippingInfo
FROM #Uno

[Code] ....

View 1 Replies View Related

CTE - Union Hierarchy Tables By More Roots ID

Oct 4, 2013

I try to build some query on hierarchy data and after two days thinking about it i have null result. What I need is union more trees tables by root id to one table.

-- tree
-- this query is functional: OK
WITH tree (sid, parend_id, level) as
(
SELECT sid, parend_id, 0 as level

[Code] ....

But this query work with one root id (100); what can i do when i have more roots id ? -> generate each tree table separated by roots id and then all tables join to one (union).

View 1 Replies View Related

UNION 2 Tables Horizontally, To One Table

Aug 5, 2007

Is it possible to combine the following tables:

Table1:
id
1
2
3

Table2:
licenses
3
2
1

To one table, like this:

id licenses
1 3
2 2
3 1

-There is always the same number of rows in both tables.

Thanks a lot!

View 9 Replies View Related

Using Union All When Inerting Into Temp Tables

Oct 4, 2007

Hello,
I'm using SQL2005, SP2

I have multiple temp tables with the same column structure that I would combine into into a single temp table using Unions.
Is this possible?

Example:
Select * From #temp1
Union All
Select * From #temp2
Union All
Select * From #temp3

Into #Temp4

I thought I would ask while I continue to research this in case someone came back with the solution before I was able to track it down elsewhere.

Thanks!

View 1 Replies View Related

Standard Way To Union Tables In Different Databases?

Oct 17, 2007

What is the standard way to union tables with exactly the same schema that are in different databases?

For example:

Code Block

USE db1 SELECT * FROM table1
UNION
USE db2 SELECT * FROM table2

this will return the following error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'use'.

View 3 Replies View Related

Union Two Tables With Relational Ordering / Grouping?

Jul 16, 2012

I have two tables with data that I need to get and display in a combobox. What I want to do is have the parent table listed in the combobox with all of its children indented. Sorted by parent then by child.

This SQL seems to be more complex than I have done previously

I can get all of the records from both tables easily:

Code:
SELECT strName, ID FROM tblParents as pp
INNER JOIN tblChildren as cc
ON cc.pID = pp.uiGUID
UNION (SELECT strName FROM tblChildren as c
INNER JOIN tblParents as p
ON c.pID = p.ID)

However, this simply returns a list that is not ordered in any fashion. I'd like to have all of the parent's children shown under the parent name (there is only 1 parent per child and multiple children per parent)

View 2 Replies View Related

Query Multiple Tables - Union/Order By

Oct 25, 2007

Hi!

I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?

Thanks so much

select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what from shows
union
select date3, date2, date1, who, what from shares
union
select date3, date2, date1, who, what from soiree
)
a order by date3, date2, date1

View 4 Replies View Related

Union Select Of Two Tables And Join Of Another Table Afterwards

Apr 2, 2008

I have got the following union statement:

SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr,
FROM plan2008
GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr

UNION

SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
FROM fsp_auftrag
GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr

My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.

My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.

Is it possible to join the tables after the union took place?

Thanks in advance!

View 8 Replies View Related

How To Create View Of Union On Variant Tables ?

Apr 1, 2008

HI, Guys:

I've some AT_DATE tables (eg: AT_20080401, AT_20080402, ...) in SQLServer DB, and these AT_XX table have same columns. but table count could be variant, so I have to query sysobjects to get all of these tables. like this:
select name from sysobject where name like 'AT_%'

Now I try to create a view AT which is the union of all these AT_XX tables, such as:

Code Snippet

Create View AT as
select * from AT_20080401
union
select * from AT_20080402
union ...

but since I'm not sure how many tables there, it would be impossible to write SQL as above.
though I could get this union result via stored-procedure, view couldn't be created on the resultset of a procedure.
Create View AT as
select * from AT_createView() <-- AT_createView must be a function, not procedure

I've checked msdn, there is Multi-statement table-valued function, but this function type seems to create one temporary table, I don't want to involve much of insert operation because there could be more than 1million records totally in these AT_XX tables.

So is there any way to achived my goal?
any reference would be appreciated, thanks !

View 8 Replies View Related

Trouble With Temp Tables And UNION Keyword

Feb 18, 2008

I'm trying to summarize costs assigned to active jobs for a manufacturing business. I need to aggregate work in process (WIP) cost that resides in labor-transaction and part-transaction tables based on transaction types, and transaction dates. Some transactions increase the WIP cost of the job while others decrease WIP. The business needs to see how much $$ is tied up in each job as of a particular date -- the calculation is:
ToDate (cost of materials and labor assigned to job)
- ToInv (cost of materials returned to inventory)
- ToSales (cost of materials sold).

I developed this query incrementally and, so far, the #ToDate, #ToInv, and #ToSales temp tables seem to be populating with the correct data. My thought was to combine these three tables with a UNION and then extract the grand totals and here's where I started getting the following error:
------------------------------------------
Incorrect syntax near the keyword 'UNION'.
------------------------------------------
The problem is with the UNIONs going into #myTotal.

I would appreciate any help with this. Also, please let me know if you can suggest a better design for this. Thanks!

Below is a simplified version of my query:

--#ToDate
CREATE TABLE #ToDate (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToDate (JobNum, Cost)

--M&S To Date
SELECT pt.jobnum,
SUM(pt.extcost) AS Cost
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN ( <valid trans types> )
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

UNION -- This one works ok.

--L&B To Date
SELECT jh.JobNum,
sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost
FROM LaborDtl l
JOIN JobHead jh ON l.JobNum = jh.JobNum
WHERE jh.JobReleased = 1
AND l.PayrollDate < '2007-9-30'
GROUP BY jh.JobNum

--#ToInv
CREATE TABLE #ToInv (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToInv (JobNum, Cost)

SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

--#ToSales
CREATE TABLE #ToSales (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToSales (JobNum, Cost)

SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

--#myTotal
CREATE TABLE #myTotal (JobNum varchar(14), Cost decimal (16,2), Source varchar(9))
INSERT INTO #myTotal (JobNum, Cost, Source)

SELECT d.JobNum, SUM(d.Cost) AS Cost FROM #ToDate d GROUP BY d.JobNum ORDER BY d.JobNum

UNION -- Problem**********************

SELECT i.JobNum, SUM(-1*i.Cost) AS Cost FROM #ToInv i GROUP BY i.JobNum ORDER BY i.JobNum

UNION -- Problem**********************

SELECT s.JobNum, SUM(-1*s.Cost) AS Cost FROM #ToSales s GROUP BY s.JobNum ORDER BY s.JobNum

--Select grand total for each job
SELECT JobNum, SUM(Cost) FROM #myTotal ORDER BY JobNum

--Drop temp tables
DROP TABLE #ToDate
DROP TABLE #ToInv
DROP TABLE #ToSales
DROP TABLE #myTotal

View 3 Replies View Related

Maximum Number Of Tables - Is 4^15 Big A Number?

Nov 26, 2006

Hello people,I might sound a little bit crazy, but is there any possibility that youcan incorporate 4^15 (1,073,741,824) tables into a SQL Database?I mean, is it possible at all? There might be a question of whereanyone would want so many tables, but i'm a bioinformatics guy and I'mtrying to deal with genomic sequences and was coming up with a newalgorithm, where the only limit is the number of tables I can put intoa Database.So, can you please advise if its possible to put in so many tables intoa SQL database? Or is the Bekerley DB better?

View 3 Replies View Related

T-SQL (SS2K8) :: Union Of Two Tables Returned Back To Main Query

Aug 18, 2014

Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.

I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.

Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2

Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB

View 3 Replies View Related