February 19, 2015 at 1:07 pm
Hi all, I am wondering if someone can help me out with what I am trying to accomplish.
I need to create a table from these 2 tables and want all the data. Table 1 has 15000 records and table has around 1000 records. Need some help with syntax.
I have 2 tables.SELECT [UniqueID]
,[Company]
,[CustID]
,[CustomerName]
,[FiscYr]
,[YtdSales]
,[YtdRcpt]
FROM [HistInfo2]
UNION
SELECT [UniqueID]
,[Company]
,[CustID]
,[CustomerName]
,[FiscYr]
,[YtdSales]
,[YtdRcpt]
FROM [HistInfo]
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 19, 2015 at 1:41 pm
You want help with the query syntax, or help turning the query into an insert statement?
Does the destination table already exist (and if so, can we have DDL), or are you looking for help with DDL too?
At the moment, without knowing the tables, the syntax you posted is fine as long as you want to remove identical records. If you want to keep identical records, you'll want UNION ALL instead.
Give us some more information on the problem
February 19, 2015 at 1:46 pm
Already created a view from tables
Create View [dbo].[HistInfo] AS
SELECT [UniqueID]
,[Company]
,[CustID]
,[CustomerName]
,[FiscYr]
,[YtdSales]
,[YtdRcpt]
FROM [HistInfo2]
UNION
SELECT [UniqueID]
,[Company]
,[CustID]
,[CustomerName]
,[FiscYr]
,[YtdSales]
,[YtdRcpt]
FROM [HistInfo]
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 19, 2015 at 1:55 pm
So what are you asking for?
February 20, 2015 at 12:46 am
Two methods.
Method one, Best way (you need to add data types)
/*
ADD DATA TYPES
*/
CREATE TABLE MyNewTable
(
UniqueId
,Company
,CustId
,CustomerName
,FiscYr
,YTDSales
,YTDRcpt
)
GO
INSERT INTO MyNewTable
(
UniqueId
,Company
,CustId
,CustomerName
,FiscYr
,YTDSales
,YTDRcpt
)
SELECT
UniqueId
,Company
,CustId
,CustomerName
,FiscYr
,YTDSales
,YTDRcpt
FROM
[dbo].[HistInfo]
GO
Method two simpler way is to use this.
SELECT
UniqueId
,Company
,CustId
,CustomerName
,FiscYr
,YTDSales
,YTDRcpt
INTO
MyNewTable
FROM
[dbo].[HistInfo]
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 20, 2015 at 12:00 pm
So you've created a view to UNION the tables together. It doesn't matter if you union them in a query or in a view, the DISTINCT operation is still going to be performed because of the UNION. In your OP, you said you wanted all the rows, so a UNION ALL is the correct approach. It'll also be faster because it doesn't have to do the DISTINCT.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply