Appending two tables

I have 2 SQL tables that Ineed to combine (append). The column information in
each table is exactly the same, except they are for different years (one is
history and one is current year). I need to be able to create a view that has
both the history data and current year data so I can list transactions
chronologically from the two tables.Create View MyView
AS
Select Field1, Field2, Field3
From Table1
UNION
Select Field1, Field2, Field3
From Table2
"Ranjit Charles" <RanjitCharles@.discussions.microsoft.com> a écrit dans le
message de news: 055547F7-9031-4E92-9868-8A11493C7865@.microsoft.com...
>I have 2 SQL tables that Ineed to combine (append). The column information
>in
> each table is exactly the same, except they are for different years (one
> is
> history and one is current year). I need to be able to create a view that
> has
> both the history data and current year data so I can list transactions
> chronologically from the two tables.

Appending two tables

I have 2 SQL tables that Ineed to combine (append). The column information in
each table is exactly the same, except they are for different years (one is
history and one is current year). I need to be able to create a view that has
both the history data and current year data so I can list transactions
chronologically from the two tables.
Create View MyView
AS
Select Field1, Field2, Field3
From Table1
UNION
Select Field1, Field2, Field3
From Table2
"Ranjit Charles" <RanjitCharles@.discussions.microsoft.com> a crit dans le
message de news: 055547F7-9031-4E92-9868-8A11493C7865@.microsoft.com...
>I have 2 SQL tables that Ineed to combine (append). The column information
>in
> each table is exactly the same, except they are for different years (one
> is
> history and one is current year). I need to be able to create a view that
> has
> both the history data and current year data so I can list transactions
> chronologically from the two tables.

Appending two tables

I have 2 SQL tables that Ineed to combine (append). The column information i
n
each table is exactly the same, except they are for different years (one is
history and one is current year). I need to be able to create a view that ha
s
both the history data and current year data so I can list transactions
chronologically from the two tables.Create View MyView
AS
Select Field1, Field2, Field3
From Table1
UNION
Select Field1, Field2, Field3
From Table2
"Ranjit Charles" <RanjitCharles@.discussions.microsoft.com> a crit dans le
message de news: 055547F7-9031-4E92-9868-8A11493C7865@.microsoft.com...
>I have 2 SQL tables that Ineed to combine (append). The column information
>in
> each table is exactly the same, except they are for different years (one
> is
> history and one is current year). I need to be able to create a view that
> has
> both the history data and current year data so I can list transactions
> chronologically from the two tables.

appending two datetimes which were converted from strings

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.

the time is converted from string.

thanksthe time is converted from string.

One method is to build a string containing both date and time in format is
'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a datetime
value. For example:

DECLARE
@.MyDate datetime,
@.MyTime datetime,
@.MyDateTime datetime

SET @.MyDate = '20061121'
SET @.MyTime = '12:13:14'

--concatenate date and time strings and assign to datetime data type
SET @.MyDateTime =
CONVERT(char(9), @.MyDate, 112) +
CONVERT(char(8), @.MyTime, 114)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.
>
the time is converted from string.
>
thanks
>

|||thanks for the answer.it worked.

i have another problem.

how can i convert a char(4) into a time where the datatype of the
column is datatime?

i think this is my only problem now.thanks
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

the time is converted from string.

>
One method is to build a string containing both date and time in format is
'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a datetime
value. For example:
>
DECLARE
@.MyDate datetime,
@.MyTime datetime,
@.MyDateTime datetime
>
SET @.MyDate = '20061121'
SET @.MyTime = '12:13:14'
>
--concatenate date and time strings and assign to datetime data type
SET @.MyDateTime =
CONVERT(char(9), @.MyDate, 112) +
CONVERT(char(8), @.MyTime, 114)
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.

the time is converted from string.

thanks

|||paul_zaoldyeck wrote:

Quote:

Originally Posted by

how can i convert a char(4) into a time where the datatype of the
column is datatime?

Combine it with an appropriate date (today's date, a date from a
related record, whatever) in a similar fashion to the previous
answer. If you don't care about the date, then pick a fixed value
(e.g. January 1, 1970) and use that in all cases.|||The example below parses a string in hh:mm format into a datetime variable.
Note that a datetime always includes both date and time components so your
app will need to ignore the date part, if not needed. The date defaults to
'19000101'.

DECLARE
@.MyTimeString char(4),
@.MyDateTime datetime

SET @.MyTimeString = '1213' --hh:mm

SET @.MyDateTime = LEFT(@.MyTimeString, 2) + ':' + LEFT(@.MyTimeString, 2) +
':00'

SELECT @.MyDateTime

--
Hope this helps.

Dan Guzman
SQL Server MVP

"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164254851.864579.14760@.b28g2000cwb.googlegro ups.com...

Quote:

Originally Posted by

thanks for the answer.it worked.
>
i have another problem.
>
how can i convert a char(4) into a time where the datatype of the
column is datatime?
>
i think this is my only problem now.thanks
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

the time is converted from string.

>>
>One method is to build a string containing both date and time in format
>is
>'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a
>datetime
>value. For example:
>>
>DECLARE
> @.MyDate datetime,
> @.MyTime datetime,
> @.MyDateTime datetime
>>
>SET @.MyDate = '20061121'
>SET @.MyTime = '12:13:14'
>>
>--concatenate date and time strings and assign to datetime data type
>SET @.MyDateTime =
> CONVERT(char(9), @.MyDate, 112) +
> CONVERT(char(8), @.MyTime, 114)
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
>news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.
>
the time is converted from string.
>
thanks
>

>

appending to the end of current file

Hello all! I have a dts package exporting a text file. I would like for the dts job to append to the end of the file each time it is ran, rather than overwriting it. Is there a simple solution for this?
ThanksDarren Green suggested in earlier posts that :
The text file provider does not support this. The simplest thing to do is to export to a new file, then use the DOS copy command to merge the files.

This can be done with the Execute Process Task, and any dynamic filename stuff can be handled with an ActiveX Script Task to change the connection and the Exec Proc Task at the same time.

copy FileOriginal.txt+FileNew.txt FileResult.txt

HTH|||That is what I am currently doing. I was hoping for something within the dts package. Thanks for you help.

val|||As suggested its not available using TEXT provider with DTS.
May be this is also other way around, is to export to an excel sheet and save that as text file.:)

Appending to a text file via DTS

DTS wizard is not allowing me to append the data to a text file. Every time I run DTS and choose the destination to be this text file (say A.txt), it overwrites the data. I have a table whose data I am dumping to a text file. I truncate the table, then get the data again into it and want to append it to the same text file. But I end up overwriting the text file with the new data.

Kindly let me know where I am going wrong.I am having the same issue as well... I thought I remember seeing an option checkbox somewhere to do that... but I cant seem to find it.
I doubt we are the only ones having to deal with this, so can someone please chime in with an answer or maybe a possible direction for us to follow? Thanks in advance... :)

Appending to a Text field

Hi Guys
I am trying to append to a text (text data type) field in my database.
Simply put, the Customer table has a field called Notes. I want to append
some extra data to this field via TSQL. So for a Customer who has an ID of
1234, how do I append some extra text to his Notes field? The help in Books
on Line has me completely baffled.
Thank youWhich helo are you referring to? Have you looked at WRITETEXT/UPDATETEXT?
ML
http://milambda.blogspot.com/

appending tables

Using t-sql, what's the easiest way to append one table to another where the
structure of both tables is precisely the same? Also, can this be set up to
append only those that meet a certain criteria, such that a 'where' clause
could be used for the append?
Thanks for any help.
Bernie YaegerINSERT INTO TABLE1
SELECT column_list FROM TABLE2
WHERE <condition in TABLE2>
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:0Noeb.22436$Kq4.15794796@.news4.srv.hcvlny.cv.net...
> Using t-sql, what's the easiest way to append one table to another where
the
> structure of both tables is precisely the same? Also, can this be set up
to
> append only those that meet a certain criteria, such that a 'where' clause
> could be used for the append?
> Thanks for any help.
> Bernie Yaeger
>|||Hi Kalen,
Tx so much!
Regards,
Bernie
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e6%23nkJ7hDHA.3208@.TK2MSFTNGP11.phx.gbl...
> INSERT INTO TABLE1
> SELECT column_list FROM TABLE2
> WHERE <condition in TABLE2>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:0Noeb.22436$Kq4.15794796@.news4.srv.hcvlny.cv.net...
> > Using t-sql, what's the easiest way to append one table to another where
> the
> > structure of both tables is precisely the same? Also, can this be set
up
> to
> > append only those that meet a certain criteria, such that a 'where'
clause
> > could be used for the append?
> >
> > Thanks for any help.
> >
> > Bernie Yaeger
> >
> >
>

Appending tables

Hi,
I wanted to know if it is possible to do to append two tables into a
third table.
For example, consider these two tables

Table 1
-------------------
| Part_num | Prt_name | Desc1 | Desc2 |
-------------------
| PRT1 | PartA | abc | xyz |
| PRT2 | PartB | def | aaa |
| PRT3 | PartC | ghi | bbb |
-------------------

Table 2
-------------------
| Cat_num | Cat_name | SDsc1 | SDsc2 |
-------------------
| CAT1 | CatalogA | abc | xyz |
| CAT2 | CatalogB | def | aaa |
| CAT3 | CatalogC | ghi | bbb |
-------------------

Now, I want to append them to get this :

Table 3

--------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
SDsc1 | SDsc2 |
--------------------------------------
| PRT1 | PartA | abc | xyz |
|
| PRT2 | PartB | def | aaa |
|
| PRT3 | PartC | ghi | bbb |
|
| | | | | CAT1
| CatalogA | abc | xyz |
| | | | | CAT2
| CatalogB | def | aaa |
| | | | | CAT3
| CatalogC | ghi | bbb |
--------------------------------------

The blanks in Table 3 are , well ,blank.

Now can it be done or not?

Awaiting your replies,
Regards,
ShwetabhShwetabh wrote:
> Hi,
> I wanted to know if it is possible to do to append two tables into a
> third table.
> For example, consider these two tables
> Table 1
> -------------------
> | Part_num | Prt_name | Desc1 | Desc2 |
> -------------------
> | PRT1 | PartA | abc | xyz |
> | PRT2 | PartB | def | aaa |
> | PRT3 | PartC | ghi | bbb |
> -------------------
> Table 2
> -------------------
> | Cat_num | Cat_name | SDsc1 | SDsc2 |
> -------------------
> | CAT1 | CatalogA | abc | xyz |
> | CAT2 | CatalogB | def | aaa |
> | CAT3 | CatalogC | ghi | bbb |
> -------------------
>
> Now, I want to append them to get this :
>
> Table 3
> --------------------------------------
> | Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
> SDsc1 | SDsc2 |
> --------------------------------------
> | PRT1 | PartA | abc | xyz |
> |
> | PRT2 | PartB | def | aaa |
> |
> | PRT3 | PartC | ghi | bbb |
> |
> | | | | | CAT1
> | CatalogA | abc | xyz |
> | | | | | CAT2
> | CatalogB | def | aaa |
> | | | | | CAT3
> | CatalogC | ghi | bbb |
> --------------------------------------
>
> The blanks in Table 3 are , well ,blank.
> Now can it be done or not?
> Awaiting your replies,
> Regards,
> Shwetabh

My browser isn't displaying your Table 3 very well so I'm not quite
certain which data is going into which column. In general you can merge
tables like this using UNION:

SELECT part_num, prt_name, desc1, ...
FROM Table1
UNION ALL
SELECT NULL, NULL, cat_name, ...
FROM Table2 ;

Each SELECT list in the UNION has to have the same number of columns
and each column has to be made up of compatible datatypes. In your case
it seems like the big question is what will be the key of Table3? It
isn't clear to me whether it has a key at all.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Shwetabh (shwetabhgoel@.gmail.com) writes:
> Table 3
> -----------------------
----------------
>| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
> SDsc1 | SDsc2 |
> -----------------------
----------------
>| PRT1 | PartA | abc | xyz |
> |
>| PRT2 | PartB | def | aaa |
> |
>| PRT3 | PartC | ghi | bbb |
> |
>| | | | | CAT1
> | CatalogA | abc | xyz |
>| | | | | CAT2
> | CatalogB | def | aaa |
>| | | | | CAT3
> | CatalogC | ghi | bbb |
> -----------------------
----------------
>
> The blanks in Table 3 are , well ,blank.
> Now can it be done or not?

Judging from the sample data you posted, what you want is

SELECT a.Part_num, a.Prt_name, a.Desc1, a.Desc2, b.Cat_num,
b.Cat_name, b.SDsc1, b.SDcs2
FROM table1 a
JOIN table2 b ON a.Desc1 = b.SDsc1
AND b.Desc2 = b.SDcs2

But I cannot say that it make much sense to join over a description column.

Maybe you need to consider a little more what you are actually looking
for and what you want to achieve.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
it seems Table 3 got pretty messed up.
So I will give the schema definations of the tables here:

Table 1:

CREATE TABLE TABLE1
(
PART_NUM varchar(10) primary key,
PRT_NAME VARCHAR(10),
DESC1 VARCHAR(20),
DESC2 VARCHAR(20)
)

Table 2:

CREATE TABLE TABLE2
(
PART_NUM varchar(10) primary key,
CAT_NUM VARCHAR(10),
CAT_NAME VARCHAR(10),
SDESC1 VARCHAR(20),
SDESC2 VARCHAR(20)
)

Now the resultant table should have the following schema:

CREATE TABLE TABLE3
(
PART_NUM varchar(10) primary key,
PRT_NAME VARCHAR(10),
DESC1 VARCHAR(20),
DESC2 VARCHAR(20),
CAT_NUM VARCHAR(10),
CAT_NAME VARCHAR(10),
SDESC1 VARCHAR(20),
SDESC2 VARCHAR(20)
)

This schema will be created programmatically.

Now my question is, if it is possible, how can I
insert records from table1 and table2 in table3?
I hope I have now made the things clearer.

Awaiting your reply,
Regards,
Shwetabh|||Shwetabh wrote:
> Hi,
> it seems Table 3 got pretty messed up.
> So I will give the schema definations of the tables here:
> Table 1:
> CREATE TABLE TABLE1
> (
> PART_NUM varchar(10) primary key,
> PRT_NAME VARCHAR(10),
> DESC1 VARCHAR(20),
> DESC2 VARCHAR(20)
> )
> Table 2:
> CREATE TABLE TABLE2
> (
> PART_NUM varchar(10) primary key,
> CAT_NUM VARCHAR(10),
> CAT_NAME VARCHAR(10),
> SDESC1 VARCHAR(20),
> SDESC2 VARCHAR(20)
> )
> Now the resultant table should have the following schema:
> CREATE TABLE TABLE3
> (
> PART_NUM varchar(10) primary key,
> PRT_NAME VARCHAR(10),
> DESC1 VARCHAR(20),
> DESC2 VARCHAR(20),
> CAT_NUM VARCHAR(10),
> CAT_NAME VARCHAR(10),
> SDESC1 VARCHAR(20),
> SDESC2 VARCHAR(20)
> )
> This schema will be created programmatically.
> Now my question is, if it is possible, how can I
> insert records from table1 and table2 in table3?
> I hope I have now made the things clearer.
> Awaiting your reply,
> Regards,
> Shwetabh

It looks like you'll want something like this:

INSERT INTO Table3
(part_num, prt_name, desc1, desc2,
cat_num, cat_name, sdesc1, sdesc2)
SELECT COALESCE(T1.part_num, T2.part_num),
T1.prt_name, T1.desc1, T1.desc2,
T2.cat_num, T2.cat_name, T2.sdesc1, T2.sdesc2
FROM Table1 AS T1
FULL JOIN Table2 AS T2
ON T1.part_num = T2.part_num ;

It still seems at least questionable whether Table3 or even Table2
represent "good" designs but as I only have your column names to go on
there isn't much point in me speculating about that.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Hi,
Well I cant really do anything about the design for table2 because that
is the way the client wants it to be.

As far as table3 goes, I wanted to check the possiblility of appending
the
records of all the tables into a single table and check the performance
and
efficiency. I understand that table3 is a poor database design
but the main motivation for doing this is to check the reaction of the
application
which will using such database.

Also, till now, I have converted DBASE database into SQL database using

OPENROWSET to import the data, I was wondering if the same database
can be entered into a new table which can hold the data from all
tables.

Any ideas?|||Hi,
Well I cant really do anything about the design for table2 because that
is the way the client wants it to be.

As far as table3 goes, I wanted to check the possiblility of appending
the
records of all the tables into a single table and check the performance
and
efficiency. I understand that table3 is a poor database design
but the main motivation for doing this is to check the reaction of the
application
which will using such database.

Also, till now, I have converted DBASE database into SQL database using

OPENROWSET to import the data, I was wondering if the same database
can be entered into a new table which can hold the data from all
tables.

Any ideas?

Regards,
Shwetabh

P.S: If you want I can mail you the code I have written in VB for this
purpose.|||In table one, there are rows or records. Take the "first one."

Now in table two there are a bunch of rows or records.

how do we know which record or records from the second to table to
combine with the first row of the first table?

In other words, if there are 100 rows in the first table, and 10 rows
in the second, how many rows are you expecting in the third table?|||Shwetabh (shwetabhgoel@.gmail.com) writes:
> Well I cant really do anything about the design for table2 because that
> is the way the client wants it to be.
> As far as table3 goes, I wanted to check the possiblility of appending
> the
> records of all the tables into a single table and check the performance
> and
> efficiency. I understand that table3 is a poor database design
> but the main motivation for doing this is to check the reaction of the
> application
> which will using such database.
> Also, till now, I have converted DBASE database into SQL database using
> OPENROWSET to import the data, I was wondering if the same database
> can be entered into a new table which can hold the data from all
> tables.
> Any ideas?

For it to be meaningful to merge table1 and table2 into one table,
there must be some relation between the data. Is there any such relation?

In your sample data PRT1 went with CAT1, but you did not indicate what
rule said that these two should go together.

If you don't know what you want, we will not know either.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> Shwetabh (shwetabhgoel@.gmail.com) writes:
> > Well I cant really do anything about the design for table2 because that
> > is the way the client wants it to be.
> > As far as table3 goes, I wanted to check the possiblility of appending
> > the
> > records of all the tables into a single table and check the performance
> > and
> > efficiency. I understand that table3 is a poor database design
> > but the main motivation for doing this is to check the reaction of the
> > application
> > which will using such database.
> > Also, till now, I have converted DBASE database into SQL database using
> > OPENROWSET to import the data, I was wondering if the same database
> > can be entered into a new table which can hold the data from all
> > tables.
> > Any ideas?
> For it to be meaningful to merge table1 and table2 into one table,
> there must be some relation between the data. Is there any such relation?
> In your sample data PRT1 went with CAT1, but you did not indicate what
> rule said that these two should go together.
> If you don't know what you want, we will not know either.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Hi,

Please disregard the sample data for now. I had missed a field in table
2 there.
Let's look at the schema I have given for table 1,2,3. Here, I am able
to create
table 3 programmatically and it successfully creates all the columns in
table 1
and table 2.

As for the rules, let me make it clearer. The application (EasyLabel
from www.tharo.com) will be using this database. This application is
used to create labels. Each label consists of various components like
barcodes,images etc. The data for this label is mapped to the fields in
the database. The user enters the Part_num (which is unique for all
records) and the software retrieves the fields *which* are mapped from
the database and loads them in the label. In case there are some other
fields which are not mapped to the database, the application does not
care about them. In other words, they are as good as blank.

Now, what I have in mind is to create a table such that all Database is
stored in it. Since
the application will load only those fields into the labels which are
mapped, it wouldnt create a problem.
Now if I have 100 records in table 1 and 10 records in table 2, the
table 3 should have 110 records at the end of operation. This table
alone will be accessed by the application to do its work.
My question is how to do this? I mean how can I take the records from
table 1, put them in table 3, then take the records from table 2 and
put them in table 3 , and so on?

Awaiting replies,
Regards,
Shwetabh|||I am expecting 110 rows.

All i want is that first all records from table 1 are added to table 3.
Then all records from table 2 are added to table 3.
The fields which are not present in table 1 or table 2 are left blank.

One more question, if I have a table in SQL,
can I alter a field to make it primary key?
Or do I have to to it while creating the table itself?|||> Now, what I have in mind is to create a table such that all Database is
> stored in it. Since
> the application will load only those fields into the labels which are
> mapped, it wouldnt create a problem.
> Now if I have 100 records in table 1 and 10 records in table 2, the
> table 3 should have 110 records at the end of operation. This table
> alone will be accessed by the application to do its work.
> My question is how to do this? I mean how can I take the records from
> table 1, put them in table 3, then take the records from table 2 and
> put them in table 3 , and so on?

To make a completely wild guess, this may be what you are looking for:

INSERT tbl3(PART_NUM, PART_NAME, DESC1, DESC2)
SELECT PART_NUM, PART_NAME, DESC1, DESC2
FROM tbl1

INSERT tbl3(PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2)
SELECT PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2
FROM tbl2 a
WHERE NOT EXISTS (SELECT *
FROM tbl3 WHERE a.PART_NUM = b.PART_NUM)

UPDATE tbl3
SET CAT_NUM = b.CAT_NUN,
CAT_NAME = b.CAT_NAME,
SDESC1 = b.SDESC1,
SDESC2 = b.SDESC2
FROM tbl3 a
JOIN tbl2 b ON a.PART_NUM = b.PART_NUM

>One more question, if I have a table in SQL,
>can I alter a field to make it primary key?
>Or do I have to to it while creating the table itself?

You cannot alter the field to make it a PK, but you can alter the table
to define a PK, if it does not have one. And PK can have more than one
column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> > Now, what I have in mind is to create a table such that all Database is
> > stored in it. Since
> > the application will load only those fields into the labels which are
> > mapped, it wouldnt create a problem.
> > Now if I have 100 records in table 1 and 10 records in table 2, the
> > table 3 should have 110 records at the end of operation. This table
> > alone will be accessed by the application to do its work.
> > My question is how to do this? I mean how can I take the records from
> > table 1, put them in table 3, then take the records from table 2 and
> > put them in table 3 , and so on?
> To make a completely wild guess, this may be what you are looking for:
> INSERT tbl3(PART_NUM, PART_NAME, DESC1, DESC2)
> SELECT PART_NUM, PART_NAME, DESC1, DESC2
> FROM tbl1
> INSERT tbl3(PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2)
> SELECT PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2
> FROM tbl2 a
> WHERE NOT EXISTS (SELECT *
> FROM tbl3 WHERE a.PART_NUM = b.PART_NUM)
> UPDATE tbl3
> SET CAT_NUM = b.CAT_NUN,
> CAT_NAME = b.CAT_NAME,
> SDESC1 = b.SDESC1,
> SDESC2 = b.SDESC2
> FROM tbl3 a
> JOIN tbl2 b ON a.PART_NUM = b.PART_NUM

Thanks, but I found out another way to get the job done.

> >One more question, if I have a table in SQL,
> >can I alter a field to make it primary key?
> >Or do I have to to it while creating the table itself?
> You cannot alter the field to make it a PK, but you can alter the table
> to define a PK, if it does not have one. And PK can have more than one
> column.

Agreed, I can use
ALTER TABLE <tablename> ADD PRIMARY KEY (<fieldname>);
to alter the table and define a PK. But it works only if the
<fieldname> is
NOT NULL. Is there any way I can alter the table to make the field NOT
NULL?

>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Shwetabh (shwetabhgoel@.gmail.com) writes:
> Is there any way I can alter the table to make the field NOT
> NULL?

ALTER TABLE ALTER COLUMN

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Appending one datatable to another ?

Is the merge method, what will work in this case ? I have two datatables with the exact same structure. How can I append the rows from table 2 onto the bottom of table 1 ? Is looping through the rows collection the only way ?

You can use UNION ALL to join two tables all records with same structrure( watch for the identity issue). Like

Select col1, col2 From table1

UNION ALL

Select col1, col2 From table2

|||

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();

dt1.Merge(dt2);

This will append dt2 to dt1

Appending data to an existing record

I am a novice when it comes to using SQL. I would like to append text to existing data in a specified column, but I can not figure out how to do it. I was trying to use an update query, but as far as I can tell this only replaces the data within the column. Any help would be appreciatedHi

Use update statement .
eg.

table1 (tablename)
id values
1 text1
2 text2
3 text3

Let the table1 contains two fields id and value. U need to append some text
to the existing values. is it ?
ok...use the following postgresql query .
update table1 set values = values || 'newtext' where id = 1

|| --> string concat operator

This will result in

table1
id values
1 text1newtext
2 text2
3 text3

If you want to update all the rows remove the 'where' condition
Try this and reply

-somaskarthic

Quote:

Originally Posted by srkartes

I am a novice when it comes to using SQL. I would like to append text to existing data in a specified column, but I can not figure out how to do it. I was trying to use an update query, but as far as I can tell this only replaces the data within the column. Any help would be appreciated

|||Hi

Here i mentioned is postgresql query , if u use some other database
please find the appropriate sting concat operator . In postgres || is the concat operator . If you use mysql or sql server , please find the correct concatenation operator.

-somaskarthic

Quote:

Originally Posted by somaskarthic

Hi

Use update statement .
eg.

table1 (tablename)
id values
1 text1
2 text2
3 text3

Let the table1 contains two fields id and value. U need to append some text
to the existing values. is it ?
ok...use the following postgresql query .
update table1 set values = values || 'newtext' where id = 1

|| --> string concat operator

This will result in

table1
id values
1 text1newtext
2 text2
3 text3

If you want to update all the rows remove the 'where' condition
Try this and reply

-somaskarthic

|||

Quote:

Originally Posted by somaskarthic

Hi

Here i mentioned is postgresql query , if u use some other database
please find the appropriate sting concat operator . In postgres || is the concat operator . If you use mysql or sql server , please find the correct concatenation operator.

-somaskarthic

This is the way:

update table1 set values = values + 'newtext' where id = 1|||Thanks guys! Worked perfectly.

Appending data to a table but not duplicates

Hiya everyone,

I have two tables in SQL 2000. I would like to append the contents of
TableA to TableB.

Table A has around 1.1 Million Records.
Table B has around 1 Million Reocords.

Basically TableA has all of the data held in TableB plus 100,000
additional records. I would only like to import or append these new
additional records. I have a unique index already setup on Table B.

Any ideas pretty pretty please?

Paul.

Ps. (Have been messing around with DTS but get a unique violation error
- Which is kinda what I want I guess, but would like SQL to ignore the
error and only copy the new data - if only)<paul@.domainscanners.com> wrote in message
news:1119371481.949023.149970@.g43g2000cwa.googlegr oups.com...
> Hiya everyone,
> I have two tables in SQL 2000. I would like to append the contents of
> TableA to TableB.
> Table A has around 1.1 Million Records.
> Table B has around 1 Million Reocords.
> Basically TableA has all of the data held in TableB plus 100,000
> additional records. I would only like to import or append these new
> additional records. I have a unique index already setup on Table B.
> Any ideas pretty pretty please?
> Paul.
> Ps. (Have been messing around with DTS but get a unique violation error
> - Which is kinda what I want I guess, but would like SQL to ignore the
> error and only copy the new data - if only)

insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol = b.keycol)

Simon|||Simon,
how is your sql different from

insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where keycol in (select keycol from dbo.tableb)
??
TIA
Rob|||rcamarda (rcamarda@.cablespeed.com) writes:
> Simon,
> how is your sql different from
> insert into dbo.TableB
> (col1, col2, col3,...)
> select col1, col2, col3...
> from dbo.TableA a
> where keycol in (select keycol from dbo.tableb)

That's one hell of a difference - you are inserting the duplicates only. :-)

But, OK, put in the NOT, and your query is the same as Simon's. In SQL 6.5
there was a difference in performance, NOT IN usually executed slower. I
think that in SQL 2000, the optimizer rewrites the query internally.

Anyway, there is still an advantage with the style that Simon used.
Consider this query:

insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol1 = b.keycol1
and a.keycol2 = B.keycol2)

That is not easily recast to NOT IN.

So NOT EXISTS is simply an operation you need to master.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||er. sorry. missed the not. I'm interesting in "exists" vs. in (select
... )
so. are you saying that the advantage is when you are looking for
something that does not exist? Otherwise "..exists (select .." is the
same as "in (select ..." ?|||rcamarda (rcamarda@.cablespeed.com) writes:
> er. sorry. missed the not. I'm interesting in "exists" vs. in (select
> ... )
> so. are you saying that the advantage is when you are looking for
> something that does not exist? Otherwise "..exists (select .." is the
> same as "in (select ..." ?

Same thing there, EXISTS is the only that works when your condition
is more than a single column. There is also a gotcha there are NULL
values involved.

It's partly a matter of style, but I use (NOT) EXISTS far more often
then (NOT) IN. (With subqueries, that is. (NOT) IN a list of values
is another matter.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes - for a NOT IN condition, it's almost always better to use NOT
EXISTS. The main issue, as Erland pointed out, is the possibility of a
NULL in the subquery. Consider this simplified example:

IF 1 IN (1, 2, 3, NULL) PRINT 'True'

Obviously the condition is TRUE, but now consider this:

IF 1 NOT IN (2, 3, NULL) PRINT 'True'

Now we don't know if the condition is TRUE or not - the NULL has an
unknown value, so in principle it could be a 1, and therefore the whole
condition evaluates to UNKNOWN. So in the case of a correlated
subquery, any NULLs in the subquery mean that the whole query returns
no rows. Using NOT EXISTS avoids this trap.

Admittedly, you often use primary key columns in the correlation, so
there could never be a NULL in the subquery, but I think it's better to
have a 'safer' habit of using NOT EXISTS. And as Erland also mentioned,
there is some personal taste involved - I find that EXISTS/NOT EXISTS
expresses the intention of the query more clearly, especially when
someone is quickly looking through the code.

Simon|||Thanks Guys!|||Hi Guys,

Thanks very much for your answers to my questions. I ran the query you
supplied and it worked fine although I now have another problem and was
wondering if you would be able to helpo me out again?

Basically I now have a table containing all the data I need but the new
data has left a gap in the Identity column that im using.

Basically the original data's identity column went up to 1,000,000. I
was hoping that the new data that was appended would be inserted as
1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
However the new appended data went in as 1,254,324 then 1,254,325 etc.

Is there a command I can run to resnycronise my identity column? so
that the ID's run smoothly from 0 through to 1,1000,000?

Hope you can help me out again,

Paul.|||(paul@.domainscanners.com) writes:
> Basically the original data's identity column went up to 1,000,000. I
> was hoping that the new data that was appended would be inserted as
> 1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
> However the new appended data went in as 1,254,324 then 1,254,325 etc.
> Is there a command I can run to resnycronise my identity column? so
> that the ID's run smoothly from 0 through to 1,1000,000?

If you want contiguous ids, or at least control over them, don't
use the IDENTITY property. When you attempt to insert a row into
a table with the IDENTITY property, you consume one number, even if
the INSERT fails. This may seem stupid, but it is actually a feature,
because it speeds up concurrency. If the number would be reused in
case of failure, SQL Server would need to lock the number, and no
other process had been able to insert until the INSERT have completed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

appending data from database 1 to database 2 (live into Dev env)

have 2 databases in sql server 2000

Live
Dev

I need to append all the data from LIVE into DEV environment. I have tried using MS Access (linking tables & importing tables and running APPEND query to update the rows from LIVE to DEV but PK & FK is causing problems as some data will have the same ID's...

Could I use DTS--Access wizard in SQL Server.

What is the best option to use?

Thanks all in advanceIf this is a one-time transfer, or if does not need to occur on a scheduled basis, you can just right click on your production database in Enterprise Manager and follow the wizard to export data.|||The best thing, IMHO, is to restore you production to development. It will force you to write a script to do a delta, and it will clean up your dev environment. If you don't have the space, buy it. It is cheap.

appending current Row ID

I am trying to append the current row ID to a string I am trying toinsert via a sproc. I have retrieved the @.@.Identity and I am passing itinto a class with a parameter and calling it using:

Listings.UpdateDB AddNewListing = new Listings.UpdateDB();

AddNewListing.InsertListing (Bathrooms.Text, Bedrooms.Text,Description.Text, Features.ToString(), Address.Text, Price.Text, FN);

I would like to add the current row ID to "FN" like:

Listings.UpdateDB AddNewListing = new Listings.UpdateDB();

AddNewListing.InsertListing(Bathrooms.Text, Bedrooms.Text,Description.Text, Features.ToString(), Address.Text, Price.Text, FN +ID);

Thanks in advance,
Justin.

Why wouldn't your UpdateDB() return the new ID (or set a property)?

Appending Backedup data During Restore Process

Dear All,

Can i append new database backup to the existing data, while restoring the backedup database? If so give me the solution. I had backups for 30 days backup and trying to restore all these bckups..

During the restore process, i had observed that the previsous data is getting deleted and new data is replaced on it (in the database). But i want to append the new data to the existing data.

Note : The backup format that i had taken for all these 30 days is of Full Backup (not differential backup)

Any solution(s) for the above stated...

Regards,Not possible using FULL BACKUP, you can use TLOG Backups to restore them on this server.

appending a temporary table to another temporary table

I need to append one table to another. The fields/columns headings are
identical in both tables. How do I append one to the other using SQL (in
Query Analyzer)?Hi Mitch
Try something like this:
INSERT INTO #temptable1
SELECT * FROM #temptable2
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:A0D31075-F452-4DF5-B78A-AA9482992986@.microsoft.com...
>I need to append one table to another. The fields/columns headings are
> identical in both tables. How do I append one to the other using SQL (in
> Query Analyzer)?
>|||insert into tableA
select * from TableB
http://sqlservercode.blogspot.com/

append xml nodes

I'm searching for a way to insert nodes from an xml document into an other.
My solution to the problem is something like this:
ALTER PROCEDURE [dbo].[insertOrder]
@.Customer xml (dbo.schemaCustomer),
@.Product xml (dbo.schemaProduct)
AS
BEGIN
declare @.app varchar(1024)
declare @.x xml
set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
'</Product>'+ CAST(@.Customer as varchar(1024)) +
'<id_order>0</id_order></Order>'
insert into xOrder (doc) values (@.app)
END
but I think there's a better way to solve the problem.
Is it possible to do this witout any casting operation?
Thank you, Luca.Hello luca,

> I'm searching for a way to insert nodes from an xml document into an
> other. My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
> END
> but I think there's a better way to solve the problem. Is it possible
> to do this witout any casting operation? Thank you, Luca.
Not that I've found. Something like this would be ideal if doc was XML-typed
as X1 is here:
declare @.x1 xml,@.x2 xml
set @.x1 = '<pet id="1" ownerID="1"><name>Jack</name></pet>'
set @.x2 = '<descr>Grey<descr>'
set @.x1.modify('insert sql:variable("@.x2") into /pet[1]')
However, the modify method won't work with XML-type variables. Nor does trea
ting
@.x2 as a varchar(x) really get you any place since when you do try that,
you get:
XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string
?".
.modify() also requires the use of string literal as the query. So, Ick.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Try:
CREATE TABLE xOrder(doc xml)
go
CREATE PROCEDURE [dbo].[insertOrder]
@.Customer xml, --(dbo.schemaCustomer),
@.Product xml --(dbo.schemaProduct)
AS
BEGIN
insert into xOrder (doc) select (
select @.Customer, @.Product as "Product", 0 as "id_order"
FOR XML PATH('Order'), TYPE
)
END
go
exec insertOrder '<a/>', '<b/>'
select * from xOrder
Best regards
Michael
"luca" <luca@.discussions.microsoft.com> wrote in message
news:1F06F5E3-E9C9-4B40-ACFE-2784345D1F40@.microsoft.com...
> I'm searching for a way to insert nodes from an xml document into an
> other.
> My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
>
> END
> but I think there's a better way to solve the problem.
> Is it possible to do this witout any casting operation?
> Thank you, Luca.
>

append xml nodes

I'm searching for a way to insert nodes from an xml document into an other.
My solution to the problem is something like this:
ALTER PROCEDURE [dbo].[insertOrder]
@.Customer xml (dbo.schemaCustomer),
@.Product xml (dbo.schemaProduct)
AS
BEGIN
declare @.app varchar(1024)
declare @.x xml
set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
'</Product>'+ CAST(@.Customer as varchar(1024)) +
'<id_order>0</id_order></Order>'
insert into xOrder (doc) values (@.app)
END
but I think there's a better way to solve the problem.
Is it possible to do this witout any casting operation?
Thank you, Luca.
Hello luca,

> I'm searching for a way to insert nodes from an xml document into an
> other. My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
> END
> but I think there's a better way to solve the problem. Is it possible
> to do this witout any casting operation? Thank you, Luca.
Not that I've found. Something like this would be ideal if doc was XML-typed
as X1 is here:
declare @.x1 xml,@.x2 xml
set @.x1 = '<pet id="1" ownerID="1"><name>Jack</name></pet>'
set @.x2 = '<descr>Grey<descr>'
set @.x1.modify('insert sql:variable("@.x2") into /pet[1]')
However, the modify method won't work with XML-type variables. Nor does treating
@.x2 as a varchar(x) really get you any place since when you do try that,
you get:
XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string
?".
..modify() also requires the use of string literal as the query. So, Ick.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Try:
CREATE TABLE xOrder(doc xml)
go
CREATE PROCEDURE [dbo].[insertOrder]
@.Customer xml, --(dbo.schemaCustomer),
@.Product xml --(dbo.schemaProduct)
AS
BEGIN
insert into xOrder (doc) select (
select @.Customer, @.Product as "Product", 0 as "id_order"
FOR XML PATH('Order'), TYPE
)
END
go
exec insertOrder '<a/>', '<b/>'
select * from xOrder
Best regards
Michael
"luca" <luca@.discussions.microsoft.com> wrote in message
news:1F06F5E3-E9C9-4B40-ACFE-2784345D1F40@.microsoft.com...
> I'm searching for a way to insert nodes from an xml document into an
> other.
> My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
>
> END
> but I think there's a better way to solve the problem.
> Is it possible to do this witout any casting operation?
> Thank you, Luca.
>

Append without UNION

select * from GEt_lu_Lookup_ST1 -- rows 1,817,148
UNION
select * from GEt_lu_Lookup_UNION -- rows 423
I want to get the full 1,817,571 rows without using a Union Statement, can
anyone help?On Tue, 8 Mar 2005 02:35:04 -0800, marcmc wrote:

>select * from GEt_lu_Lookup_ST1 -- rows 1,817,148
>UNION
>select * from GEt_lu_Lookup_UNION -- rows 423
>I want to get the full 1,817,571 rows without using a Union Statement, can
>anyone help?
Hi marcmc,
My first reaction can only be: why' Is there any particular reason why
you want to avoid UNION?
In case you're worried about performance: UNION will attempt to remove
duplicates. If you're sure there are no duplicates (or if you don't want
them removed), use UNION ALL. This should be lots faster, as the step to
remove duplicates is skipped.
In case you have another reason for not wanting to use UNION: here's one
possible way to get the same results without using UNION or UNION ALL:
-- Same as UNION
SELECT COALESCE(a.Column1, b.Column1) AS Column1,
COALESCE(a.Column2, b.Column2) AS Column2,
...
COALESCE(a.ColumnN, b.ColumnN) AS ColumnN
FROM GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN GEt_lu_Lookup_UNION AS b
ON a.Column1 = b.Column1
AND a.Column2 = b.Column2
AND ....
AND a.ColumnN = b.ColumnN
-- Same as UNION ALL
SELECT COALESCE(a.Column1, b.Column1) AS Column1,
COALESCE(a.Column2, b.Column2) AS Column2,
...
COALESCE(a.ColumnN, b.ColumnN) AS ColumnN
FROM GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN GEt_lu_Lookup_UNION AS b
ON 1 = 2
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx Hugo. Beacause I am investigating the use of an indexed view. The new
code gets what I want but then i get...
CREATE UNIQUE CLUSTERED INDEX [GEv_lu_Lookup] ON
[dbo].[GEv_lu_Lookup]([Ge_lookup_id], [Ge_source_id],
[Ge_lookup_code], [Ge_lookup_parent]) ON [PRIMARY]
GO
Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'DbName.dbo.GEv_lu_Lookup'. It contains one or more
disallowed constructs.
Any ideas...I've read the rules I just can't find why the workaround won't
work.|||Maybe its the full outer join in the design. The GEt_lu_Lookup_ST1 and
GEt_lu_Lookup_UNION are dummy temp tables.
CREATE VIEW GEv_lu_Lookup WITH SCHEMABINDING AS
SELECT COALESCE(a.Ge_lookup_id, b.Ge_lookup_id) AS Ge_lookup_id,
COALESCE(a.Ge_source_id, b.Ge_source_id) AS Ge_source_id,
COALESCE(a.Ge_lookup_code, b.Ge_lookup_code) AS Ge_lookup_code,
COALESCE(a.Ge_lookup_desc, b.Ge_lookup_desc) AS Ge_lookup_desc,
COALESCE(a.Ge_lookup_parent, b.Ge_lookup_parent) AS Ge_lookup_parent
FROM dbo.GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN dbo.GEt_lu_Lookup_UNION AS b
ON a.Ge_lookup_id = b.Ge_lookup_id
AND a.Ge_lookup_code = b.Ge_lookup_code
-- 1817512 rows in Time: 1:29|||On Tue, 8 Mar 2005 04:11:02 -0800, marcmc wrote:

>Thx Hugo. Beacause I am investigating the use of an indexed view. The new
>code gets what I want but then i get...
> CREATE UNIQUE CLUSTERED INDEX [GEv_lu_Lookup] ON
>[dbo].[GEv_lu_Lookup]([Ge_lookup_id], [Ge_source_id],
>[Ge_lookup_code], [Ge_lookup_parent]) ON [PRIMARY]
>GO
>Server: Msg 1936, Level 16, State 1, Line 1
>Cannot index the view 'DbName.dbo.GEv_lu_Lookup'. It contains one or more
>disallowed constructs.
>Any ideas...I've read the rules I just can't find why the workaround won't
>work.
Hi marcmc,
Can you post the CREATE VIEW statement used to create the view
GEv_lu_lookup?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Mar 2005 04:23:02 -0800, marcmc wrote:

>Maybe its the full outer join in the design.
Hi marcmc,
Yes, that must be the reason. According to Books Online, outer joins are
not permitted in an indexed view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||any other ideas how I can get all the rows and index my view without the use
of UNION/OUTER JOIN or any other rule breakers?
"Hugo Kornelis" wrote:

> On Tue, 8 Mar 2005 04:23:02 -0800, marcmc wrote:
>
> Hi marcmc,
> Yes, that must be the reason. According to Books Online, outer joins are
> not permitted in an indexed view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Tue, 8 Mar 2005 06:15:05 -0800, marcmc wrote:

>any other ideas how I can get all the rows and index my view without the us
e
>of UNION/OUTER JOIN or any other rule breakers?
Hi marcmc,
Not without knowing more about your actual business problem than I know
now. Please post the following:
1. Table structure of both tables, posted as CREATE TABLE statement. If
there are many columns, you may omit those that play no role of
importance. Do include all constraints and properties; especially
PRIMARY KEY constraint and UNIQUE constraints are important in this
case. (www.aspfaq.com/5006)
2. Some sample data to illustrate your situation. No need to post all
1.8 million rows, of course - just enoguh to give me a feeling for the
structure of your data. (http://vyaskn.tripod.com/code.htm#inserts)
3. Expected output from the posted sample data. Especially handling of
duplicates should be visible in the expected output.
4. A short but concise description of the actual business problem you're
trying to solve. Don't assume I know your business - I probably don't.
5. Also: what you are trying to accomplish with this indexed view. An
indexed view can never be a goal in itself; it can only be part of a way
to achieve some other goal. If you can elaborate on that goal, I can
help you find other, maybe even better ways to achieve the same goal.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Append Views

My Problem is

I have 2 views --> 2 Databases (2 Products) but there are same fields (Same structure)

and I have to created the report by Crystal Reports to compare the Quatity of all product in my Company

So how Can I combine them (2 views with the same recoed but not the same data)

thanks for helping me

Kate

You can try Linked Servers if your databases are on different servers or on different instances.

If they are on the same instance then you can use

select * from DatabaseName.dbo.TableName
OR
select * from DatabaseName..TableName

Eralper