Merge Large Tables

What is the best way to merge two really large tables (7,000,000 rows each,
and very wide)?
The table definitions are the same for both tables.
Right now I am using a insert into statement with a selection from one table
at a time, but it takes way too long. I don't need it to be logged. A all
or nothing result is fine for me. I don't think DTS is an option because I
need to run this from a C# app. I think my only other option is using the
BCP api to select the data and load it into the new table, but this just
seems like the wrong way to go.
Any other ways to go with this?
Shawn
Select *
into [NewTable]
From
Select * [Table1]
Union All
Select * [Table2]
"Shawn Meyer" <me@.me.com> wrote in message
news:O4GhS6QQFHA.580@.TK2MSFTNGP15.phx.gbl...
> What is the best way to merge two really large tables (7,000,000 rows
> each,
> and very wide)?
> The table definitions are the same for both tables.
> Right now I am using a insert into statement with a selection from one
> table
> at a time, but it takes way too long. I don't need it to be logged. A all
> or nothing result is fine for me. I don't think DTS is an option because
> I
> need to run this from a C# app. I think my only other option is using the
> BCP api to select the data and load it into the new table, but this just
> seems like the wrong way to go.
> Any other ways to go with this?
> Shawn
>

Merge Large Tables

What is the best way to merge two really large tables (7,000,000 rows each,
and very wide)?
The table definitions are the same for both tables.
Right now I am using a insert into statement with a selection from one table
at a time, but it takes way too long. I don't need it to be logged. A all
or nothing result is fine for me. I don't think DTS is an option because I
need to run this from a C# app. I think my only other option is using the
BCP api to select the data and load it into the new table, but this just
seems like the wrong way to go.
Any other ways to go with this?
ShawnSelect *
into [NewTable]
From
Select * [Table1]
Union All
Select * [Table2]
"Shawn Meyer" <me@.me.com> wrote in message
news:O4GhS6QQFHA.580@.TK2MSFTNGP15.phx.gbl...
> What is the best way to merge two really large tables (7,000,000 rows
> each,
> and very wide)?
> The table definitions are the same for both tables.
> Right now I am using a insert into statement with a selection from one
> table
> at a time, but it takes way too long. I don't need it to be logged. A all
> or nothing result is fine for me. I don't think DTS is an option because
> I
> need to run this from a C# app. I think my only other option is using the
> BCP api to select the data and load it into the new table, but this just
> seems like the wrong way to go.
> Any other ways to go with this?
> Shawn
>

Merge Join transformation hangs

I have serached through this forum, and I could not find the solution.

My SSIS data flow is a simple one. Extracting two tables from Oracle using OLE DB Source , join them together using merge join and loading into a table in SQL server by SQL Server Destination.

I haven't gone through this simple procedure because Merge join always hangs there. Down to further investigation, I found one input (randomly one of two inputs) is always stuck. Sometimes the input is empty, sometimes is about half way.

Is there workround to see what is happening there and to fix this problem?

TIA

As far as I can see, Mergejoin is not the culprit, it is by design for MergeJoin to wait until it gets buffers from both inputs before it outputs. Looks to me your input(s) did not get through at your Oracle server. I suggest you to use an external tool (rowsetviewer?) to perform the same queries you put on the two OLEDBSrc inputs, to see whether it works there.

thanks

wenyang

|||

Also make sure the 2 inputs of the Merge Join are actually sorted.

Rafael Salas

|||

Hi, Wenyang & Rafael,

Thanks very much for your input.

The 2 inputs of the Merge Join are sorted by "order by" and the Issorted = true. I found the killer is the "data viewer" of the path, which shows the input data. Once I removed the "data viewer", the problem's gone. I cannot explain why.

BTW, what's the external tool (rowsetviewer)? Where can I get it?

Merge Join Problem

Hello,
I've got a problem with the merge join operator.

I'm trying to Join two tables using a left outer join.

Here are my tables :

TABLE AGENT
AGENT_CODE; REGION_ID; COUNTRY_ID
AA;01;01;
BB;01;01;
CC;01;02;
CC;02;02;
DD;01;01;

600 records in this table.

TABLE DELEGUE
AGENT_CODE; FIRST_NAME; LAST_NAME
AA; Maradona;Diego;
BB; Maradona;Diego;
DD; Zidane;Zinedine;

145 records in this table.

This is what i expect in my left outer join:
AGENT_CODE; REGION_ID; COUNTRY_ID;FIRST_NAME; LAST_NAME
AA;01;01;Maradona;Diego;
BB;01;01;Maradona;Diego;
CC;01;02;NULL;NULL
CC;02;02;NULL;NULL;
DD;01;01;Zidane;Zinedine

600 records in the destination table, with only 145 Not null first_name and last_name

The issorted property has been set to true in the 2 sources tables, the numkeyscolumns property is set to 1, and the join keys are both checked.
When i execute the sql query using the editor in sql server, the result is good, i've got what i've expected.
But when i run the dataflow, the destination has got 600 records, but the columns FIRST_NAME and LAST_NAME are not null only 8 times, the 592 other lines are NULL.
Could someone explain to me why i lost 145-8=137 FIRST_NAME and 145-8=137 LAST NAME ?
Thank you in advance.

by left join as the table on left has 600 records, therefore it must result 600 rows, i think so

can you also write your query also here

you can also check by sub query that how many rows exactly match in your child table

|||I changed the table names in first post so that anyone could understand (i'm french), the real table names are :
CODE_AGENT (600 records) and DELEGUE (145).
i want to join these two tables using CODE_AGENT.AGENT_ID and DELEGUE.CODE_AGENT

this is the query i made :
SELECT DELEGUE.NOM_DELEGUE, DELEGUE.PRENOM_DELEGUE, CODE_AGENT.NOM_AGENT, CODE_AGENT.CODE_SECTEUR,
CODE_AGENT.CODE_EQUIPE, CODE_AGENT.CODE_USINE, CODE_AGENT.AGENT_ID, CODE_AGENT.REGION_ID
FROM CODE_AGENT LEFT OUTER JOIN
DELEGUE ON CODE_AGENT.AGENT_ID = DELEGUE.CODE_AGENT

The result is exactly what i want, but when i use SSIS, i lost many records of DELEGUE.NOM_DELEGUE, DELEGUE.PRENOM_DELEGUE.|||

You said that the IsSorted property is set to true and the sortkeyposition property is set to 1 but you didn't say that the data was sorted. Can you verify that the data is actually sorted. Setting these properties don't do anything except tell the dataflow that the data is sorted. If it actually is not sorted then the results of your MergeJoin are undefined.

Thanks,

Matt

|||Thank you Matt !
That was the problem, i didn't understand that i had to sort the table for each merge join.
The result is correct!
I added a "Sort" data flow transformation and the result is ok!

Merge Join Output Bug?

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

I see your point Chris.

I think it is intential -

. The reason why B+C not work is because column B has a non-zero sortKeyPosition which indicates the output (to which B belongs) should be sorted (in other words, the output's "isSorted" property is true), but the output can not find a column with SortKeyPosition 1

. As for why C column only works is because the output is then not sorted.

If you think the error message is not very helpful, please log a customer issue through our connect website http://connect.microsoft.com/SQLServer and your request will be addressed soon as appropriate.

Thanks

wenyang

Merge Join Output Bug?

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

I see your point Chris.

I think it is intential -

. The reason why B+C not work is because column B has a non-zero sortKeyPosition which indicates the output (to which B belongs) should be sorted (in other words, the output's "isSorted" property is true), but the output can not find a column with SortKeyPosition 1

. As for why C column only works is because the output is then not sorted.

If you think the error message is not very helpful, please log a customer issue through our connect website http://connect.microsoft.com/SQLServer and your request will be addressed soon as appropriate.

Thanks

wenyang

Merge join more than 2 tables

I need to take certain items of data from four different tables and out them into one table.

Unfortunately my source data's version of SQL does not support the LEFT JOIN keyword which has left me with a bit of a problem.

I saw the merge join in SSIS and used it to get data from two of the tables and stick them in the destination and it all worked fine.

That got me thinking, is it possible to create a second merge join transformation within the same data flow task for the remaining two tables and then join the output of both the merge joins to give me the data I need from all four tables in one output?

I cannot answer your direct question about the merge join, but wanted to make one comment. It is possible to recast a Left Join query as a Union and this might be a viable approach for your problem. The general form is to make a Union of the simple Join and a Not IN query.

merge history ip address

Hi again,

Is there a way I can find out the ip address of the subscriber machine
in a replication? I can't seem to find it in any of the tables under
the distribution database.

Thanks,
Haroldsp_helpmergesubscription should give you the server name, but you'll
probably need to use an external script (or perhaps xp_cmdshell) to
look up the IP address. There might be a better way, though, so you
might want to post in microsoft.public.sqlserver.replication

Simon

merge database content

hi,

i have a sqlserver CE and a sqlserver database.
The tables are exactly the same on both databases.
the sqlserver CE database Content will be synchronized with the
sqlserver database with insert orders..
is there a way to merge the two databases?
this would be great cause different content will be inserted in both
databases. After a synchronisation both databases should have the same
content..

i hope you understood my problem. my english is not very well..

christianIt sounds like you're looking for merge replication - see Books Online
for more details. Since replication is quite a specialized area, you
might want to post in microsoft.public.sqlserver.replication if you
need more detailed information.

Simon

Merge data in SQL Server databases

I have two databases with identical tables structures. I need to merge the data in both databases without implementing merge replication. Can someone tell me what script to run that will compare the data in those tables? First I know the script has to search for data that doesn't exist in one table, the copy that over from the table that has extra rows. Thanks

AmmieNot sure how to help you unless you want to write a stored proc to do it.

Merge data from two tables into one table - no updates/only insert and no insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

sql

Merge data from two tables into one table - no updates/only insert and no insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

Merge data from two tables into one table - no updates/only insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

Merge Conflict Data

hi all,
i observed some conflicts in conflict viewer.i am able to see the data in
conflict tables.my question is i included one userid column in each table to
identify the data from where it is inserted or updated.now in conflict table
i am getting only publisher side userid i am not getting the subscriber side
userid.
your help is appreciated.
thanks
reddy
What process is filling in the user id that does the modification? If you
are using a default and have your publisher configured to have the higher
priority, the publisher's id will "win".
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:2A7B22B8-3FC6-4C41-8371-F2C3A9CF25BE@.microsoft.com...
> hi all,
> i observed some conflicts in conflict viewer.i am able to see the data in
> conflict tables.my question is i included one userid column in each table
to
> identify the data from where it is inserted or updated.now in conflict
table
> i am getting only publisher side userid i am not getting the subscriber
side
> userid.
> your help is appreciated.
> thanks
> reddy

Merge Application Conflict Log

Is it possible to write conflict sql in some persistent table.
I know that they a writen in merge replication tables and can be viewed via
conflict viewer
but they a lost after the snapshot run.
I need to keep them forever.
Probably i can write custom resolver who will do thta - but is there any
'out of the box' solution?
Grigoris - as far as I know there isn't any out-of-the-box solution but
perhaps the easiest solution would be to regularly poll the conflict table
and to write the values into an audit table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Use perfmon to watch the conflicts per second counter of SQL
Server:Replication Merge and run a job which reads the conflict tables when
conflicts occur.
You can also use sp_helpmergearticleconflicts and run it on the publisher
and subscriber. It will return a list of articles with conflicts and you can
then manually or programmatically retrieve them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Grigoris Tsolakidis" <gcholakidis@.spam_remove.hotmail.com> wrote in message
news:%237acphUZHHA.588@.TK2MSFTNGP06.phx.gbl...
> Is it possible to write conflict sql in some persistent table.
> I know that they a writen in merge replication tables and can be viewed
> via conflict viewer
> but they a lost after the snapshot run.
> I need to keep them forever.
> Probably i can write custom resolver who will do thta - but is there any
> 'out of the box' solution?
>

Merge and Join

Hi I am a newbie into the world of Microsoft SQL Server Integration Services 2005. I am trying to merge 2 tables using a specified join like equi-join and also having a complex set of critriea. And I know if I use the merge/merge join transformation it can't be done so how to go about doing that other than using the "Execute SQL Task" which doesn't help me in my next "Data Flow task" as an input. As i need the data derive from the complex sql as an input to other transformations.Hi Ken Hu

could you be a bit more specific about what you are trying to achieve ? (eg: by giving us some pseudo-code ?).

This way more people will be able to give you bits of answers.

cheers

Thibaut

Merge 2 Tables

Hi All
How to Merge 2 Tables ?
Any Ideas Please
THanks
do they have the same schema?
If so I'd try something like this
insert into table2
select * from table1
If you have primary key collisions try this
insert into table2
select * from table1 where table1.pk not in (select pk from table2)
"TZ" <TZ@.discussions.microsoft.com> wrote in message
news:A97B6000-C7F3-4708-9A4A-0D3D8BED6D28@.microsoft.com...
> Hi All
> How to Merge 2 Tables ?
> Any Ideas Please
> --
> THanks

merge 2 tables

Hi,
I need to merge 2 tables for the purpose of reporting on them.
1 table has actuals data that includes date, account type, branch location &
totals.
The other has budget figures which also includes business type, sub
catagory, date & budget amount.
Can someone explain how i could merge the 2 together.
I have attempted a union but get the error message operator must have equal
number of expressions.
here is the 2 queries
SELECT CONVERT(char(10), date, 103) AS Date, entity, entity_type,
sub_catagory, amount, comments, branch
FROM csd_budgets.dbo.tbl_budgets
WHERE (branch = 53) AND (sub_catagory = 2) AND (entity_type = 7) AND
(entity = 2) AND (date BETWEEN CONVERT(DATETIME, '2005-11-13 00:00:00', 102)
AND
GETDATE())
ORDER BY date
Thankyou in advance
Todd
SELECT CONVERT(char(10), date, 103) AS Date, Account_Code, Branch_Code,
Total_Balance, No_of_Accounts
FROM tbl_account_balances
WHERE (Branch_Code <= 56) AND (Branch_Code = 53) AND (NOT (Account_Code
IN (0001, 0004, 0006, 0085, 0808))) AND (CONVERT(char(10), date, 103)
= CONVERT(char(10), GETDATE(), 103))
and 2nd query isNot really clear on what you're trying to do here - if you use UNION
you basically stack a set of rows on top of another - column number
therefore must be the same...thus your error.
It sounds like you want to join the tables.
http://www.w3schools.com/sql/sql_join.asp|||In order to UNION 2 or more queries, the fields must match. UNION ALL will
give you the result you're looking for...I think...or just JOIN the tables o
n
a foreign key.
Just my twist on it,
Adam Turner
"Tango" wrote:

> Hi,
> I need to merge 2 tables for the purpose of reporting on them.
> 1 table has actuals data that includes date, account type, branch location
&
> totals.
> The other has budget figures which also includes business type, sub
> catagory, date & budget amount.
> Can someone explain how i could merge the 2 together.
> I have attempted a union but get the error message operator must have equa
l
> number of expressions.
> here is the 2 queries
> SELECT CONVERT(char(10), date, 103) AS Date, entity, entity_type,
> sub_catagory, amount, comments, branch
> FROM csd_budgets.dbo.tbl_budgets
> WHERE (branch = 53) AND (sub_catagory = 2) AND (entity_type = 7) AND
> (entity = 2) AND (date BETWEEN CONVERT(DATETIME, '2005-11-13 00:00:00', 10
2)
> AND
> GETDATE())
> ORDER BY date
> Thankyou in advance
> Todd
> SELECT CONVERT(char(10), date, 103) AS Date, Account_Code, Branch_Code
,
> Total_Balance, No_of_Accounts
> FROM tbl_account_balances
> WHERE (Branch_Code <= 56) AND (Branch_Code = 53) AND (NOT (Account_Cod
e
> IN (0001, 0004, 0006, 0085, 0808))) AND (CONVERT(char(10), date, 103)
> = CONVERT(char(10), GETDATE(), 103))
>
> and 2nd query is
>
>|||Tango-
I dint get whats ut exact requirement..but while using Union
Yor are getting error bcoz columns ur selecting while merging are not equal.
.
To override this ,
- Maintain the same columns in both select statements and if u dont want get
any columns from any of the table ,place 'null' instead of that column place
.
-COLUMN datatypes should be the same in both table while using Union/Union a
ll
Hoping this will help you
Kumar
"Tango" wrote:

> Hi,
> I need to merge 2 tables for the purpose of reporting on them.
> 1 table has actuals data that includes date, account type, branch location
&
> totals.
> The other has budget figures which also includes business type, sub
> catagory, date & budget amount.
> Can someone explain how i could merge the 2 together.
> I have attempted a union but get the error message operator must have equa
l
> number of expressions.
> here is the 2 queries
> SELECT CONVERT(char(10), date, 103) AS Date, entity, entity_type,
> sub_catagory, amount, comments, branch
> FROM csd_budgets.dbo.tbl_budgets
> WHERE (branch = 53) AND (sub_catagory = 2) AND (entity_type = 7) AND
> (entity = 2) AND (date BETWEEN CONVERT(DATETIME, '2005-11-13 00:00:00', 10
2)
> AND
> GETDATE())
> ORDER BY date
> Thankyou in advance
> Todd
> SELECT CONVERT(char(10), date, 103) AS Date, Account_Code, Branch_Code
,
> Total_Balance, No_of_Accounts
> FROM tbl_account_balances
> WHERE (Branch_Code <= 56) AND (Branch_Code = 53) AND (NOT (Account_Cod
e
> IN (0001, 0004, 0006, 0085, 0808))) AND (CONVERT(char(10), date, 103)
> = CONVERT(char(10), GETDATE(), 103))
>
> and 2nd query is
>
>

Mental Block

Can some please help with this SQL problem:
I have tables in select query and i want to show all
dbo.TblTerritory.Description even is there is no data in the other columns,
ie there are no records of type 'lost' in ProjectStatus
SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int,
CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats INNER JOIN
dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
dbo.tblCalendar_jpM RIGHT OUTER JOIN
dbo.TblTerritory ON
dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
(dbo.jp_tblproject_stats.ProjectStatus = 'lost')
GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
pleae help
Regards
John"John" <topguy75@.hotmail.com> wrote in message
news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other
columns,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname,
CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON
dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int,
CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
John,
-- The query realigned and aliased for readability:
SELECT TOP 100 PERCENT
T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats AS PS1
INNER JOIN
dbo.tblCalendar_jp AS C1
-- Note right here there is no column name
-- On the right-hand side of the = operator,
-- just the table alias.
ON PS1.Mnth = C1
RIGHT OUTER JOIN
dbo.TblTerritory AS T1
ON PS1.Territory_Code = T1.Code
WHERE (T1.Manager = 'clive wallom')
AND (PS1.ProjectStatus = 'lost')
GROUP BY T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
ORDER BY T1.Description
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
The above originally appeared as:
FROM dbo.jp_tblproject_stats
INNER JOIN
dbo.tblCalendar_jp
ON dbo.jp_tblproject_stats.Mnth
= dbo.tblCalendar_jpM
-- .<some column name goes here>
-- Probably should be .Mnth
Sincerely,
Chris O.|||John:
When there is a requirement of having all row returned for a particular
column no matter if it has records in other tables you are joining to, one
should be very careful in putting in the where clause.
When your where clause is not properly designed, it will filter out the rows
having null value in that column. You can modify your where clause to let it
include null values or you can put these condition while you join two tables
.
(the way Chris suggested you)
Putting this in ON conditions while you are joining two table at times might
become quite complex.
An easier way would be to modify the where clause to let it include the null
s
say for example:
instead of ( dbo.TblTerritory.Manager = 'clive wallom' )
you cna put ( dbo.TblTerritory.Manager = 'clive wallom'
or dbo.TblTerritory.Manager is null)
Either of the approach will give you expected result and will perform equall
y.
Abhishek
"John" wrote:

> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other columns
,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int
,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
>|||Thanks for your assistance, suprising what a bit of organising acheives. it
was '.m'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:vaKdnYt0T5kyIu7eRVn-sg@.comcast.com...
> "John" <topguy75@.hotmail.com> wrote in message
> news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> columns,
> CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth =
> dbo.jp_tblproject_stats.Counter,
> CONVERT(varchar,
> CONVERT(varchar,
> John,
> -- The query realigned and aliased for readability:
> SELECT TOP 100 PERCENT
> T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats AS PS1
> INNER JOIN
> dbo.tblCalendar_jp AS C1
> -- Note right here there is no column name
> -- On the right-hand side of the = operator,
> -- just the table alias.
> ON PS1.Mnth = C1
> RIGHT OUTER JOIN
> dbo.TblTerritory AS T1
> ON PS1.Territory_Code = T1.Code
> WHERE (T1.Manager = 'clive wallom')
> AND (PS1.ProjectStatus = 'lost')
> GROUP BY T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> ORDER BY T1.Description
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> The above originally appeared as:
> FROM dbo.jp_tblproject_stats
> INNER JOIN
> dbo.tblCalendar_jp
> ON dbo.jp_tblproject_stats.Mnth
> = dbo.tblCalendar_jpM
> -- .<some column name goes here>
> -- Probably should be .Mnth
> Sincerely,
> Chris O.
>

Memory usage

Hello,

I need to know which of the following two methods do need less RAM.

There are 2 big tables, each about 9 M rows, and 6 small dimension tables with each about 10 to 100 Rows. The dimension tables are joined by their id's with one of the big table.

The Structure of a dimension Table looks like

CarID (tinyint), Description (varchar(20))
1 BMW
2 Porsche

I want to join the 2 Big Tables in a materialized view. Later i will run queries like
select * into #temp from dbo.vw_materialized_view where Car = 'BMW'

So, back to my question, will such a query take less memory (ram) when i joined all 8 tables before I created the mat. view or will it take less when I only join the 2 big tables in a mat.view and later join the mat.view with the 6 dimension tables?

Hope you got that ;-)

Thank youmemory usage will be managed by sql server

If you create an index on a view, then that data will be stored just like a base table, so you incur more overhead and disk storage, or if it's small enough, in memory

But all of that is managed by sql server

And if you don't index the view and the joins afre simple enough, then it'll use the indexes on the table

What was the question again?