A view that include a table from a database in another sql server

I need to create a view that contain a table from a database that reside in
another sql server.
The view is a simple select statement joining two tables; i.e. table A inner
joining table B that resides in different server. I hope this make sense.
I thank you in advance for any recommendations.
JamesCreate a linked server to the SQL Server containing table B or replicate the
data from table B to another table locally for the join.
HTH
Jerry
"jamesww7" <jamesww7@.discussions.microsoft.com> wrote in message
news:30796887-26D4-44E1-B3CF-AA4EB31C2894@.microsoft.com...
>I need to create a view that contain a table from a database that reside in
> another sql server.
> The view is a simple select statement joining two tables; i.e. table A
> inner
> joining table B that resides in different server. I hope this make sense.
> I thank you in advance for any recommendations.
> --
> James

A statement that returns what is and what is not in the database

Hi!!
I wonder if anyone can help. I have a select statement :e.g
select * from products
where productref in ('123456',
'123457',
'123458',
'123459'
and so on)
The products that are in the database come are returned as results.
So,
The query returns:
123456 - sadjsjfsdfjlksf
123457',- fdfslkdjfdj
123458',- dfsjflkdjlsjf
But I also need to find out what is not returned. There are about
10,000 lines and I need to find out what has not been returned. please
help
select * from products
where productref NOT in ('123456',
'123457',
'123458',
'123459'
and so on)
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
"Himani" <himani77@.gmail.com> wrote in message
news:1171103166.734403.217840@.s48g2000cws.googlegr oups.com...
> Hi!!
> I wonder if anyone can help. I have a select statement :e.g
> select * from products
> where productref in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> The products that are in the database come are returned as results.
> So,
> The query returns:
> 123456 - sadjsjfsdfjlksf
> 123457',- fdfslkdjfdj
> 123458',- dfsjflkdjlsjf
> But I also need to find out what is not returned. There are about
> 10,000 lines and I need to find out what has not been returned. please
> help
>
|||On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> select * from products
> where productref NOT in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171103166.734403.217840@.s48g2000cws.googlegr oups.com...
>
>
>
>
> - Show quoted text -
I did try this but it did not work. the query returned other products.
perhaps I can explain a bit more.
The key thing is that some of the products are not on the database at
all.The List
'123456',
123457',
'123458',
'123459'
is from another system in which all the products are there. Whilst the
select query returns
the top 4 products as they are in the database. I want another query
that returns the ones that are not in the database at all - i.e. it
should return 123459 etc but it doesn't.
|||> I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
One method:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '123456' AS productref
UNION ALL SELECT '123457'
UNION ALL SELECT '123458'
UNION ALL SELECT '123459'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171128176.411862.307420@.a75g2000cwd.googlegr oups.com...
> On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I did try this but it did not work. the query returned other products.
> perhaps I can explain a bit more.
> The key thing is that some of the products are not on the database at
> all.The List
> '123456',
> 123457',
> '123458',
> '123459'
> is from another system in which all the products are there. Whilst the
> select query returns
> the top 4 products as they are in the database. I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
>
|||On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> One method:
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '123456' AS productref
> UNION ALL SELECT '123457'
> UNION ALL SELECT '123458'
> UNION ALL SELECT '123459'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171128176.411862.307420@.a75g2000cwd.googlegr oups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
hi !!
Thanks a lot for sql code. I understand the query but there is a
slight problem. I do not have the access to the other database. I
have been given a list of product refs between the range
114203-124675. Now this was an upload that wasn't done properly. So,
roughly 5241 products are on the system and 5233 are not. The products
that the database contains are easily obtainable by the following
query:
select * from products where productref in (114203,114204,114205 etc)
So this query returns 114203,114204 as they are in the database. But I
need to know is there a way of retrieving 114205 which is not on the
database. Any tips would be much appreciated.
Regards
Himani
|||> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
So you need to identify the products that are in the list but not in the
database, right? I believe the query I originally posted will do that -
just specify the actual list of all products in the list you were given as
the derrived table. For example, I would expect the query below to return
only 114205 if 11403 and 11404 are in the products table.
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171190077.447791.188360@.q2g2000cwa.googlegro ups.com...
> On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
> hi !!
> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
> Regards
> Himani
>
|||On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
>
> So you need to identify the products that are in the list but not in the
> database, right? I believe the query I originally posted will do that -
> just specify the actual list of all products in the list you were given as
> the derrived table. For example, I would expect the query below to return
> only 114205 if 11403 and 11404 are in the products table.
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '114203' AS productref
> UNION ALL SELECT '114204'
> UNION ALL SELECT '114205'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171190077.447791.188360@.q2g2000cwa.googlegro ups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Many Thanks. It did work. Just wondering if you could just explain the
scrip a bit if you had some time or comment it.
Regards
Himani
|||> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
There are a few different techniques to get the desired list but all require
that you have a table that contains the complete list of products that
should be in the database. You could either create and load a table with
those products or use the derived table (inline view). I chose a derived
table because this was one-time query.
To expand derived tables, start with a query that returns the desired
result:
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
Then enclose it in parenthesis and specify an alias:
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
You can then use it anywhere in a query where a normal table can be
specified:
SELECT *
FROM
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
Now that you have the list of what should be in the database, you can
identify the rows that are not in the target table using one of several
techniques:
NOT EXISTS:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
NOT IN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE OtherDatabaseList.productref NOT IN
(
SELECT p.productref
FROM dbo.products p
)
Note that the NOT IN method is dangerous for nullable columns; no rows will
be returned if any NULL values are in the NOT IN list. I always use NOT
EXISTS instead to avoid that issue:
OUTER JOIN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
LEFT OUTER JOIN dbo.products p ON
p.productref = OtherDatabaseList.productref
WHERE p.productref IS NULL
MINUS (SQL 2005):
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
EXCEPT
SELECT p.productref FROM dbo.products p
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171296448.577247.172070@.l53g2000cwa.googlegr oups.com...
> On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
> Regards
> Himani
>

A statement that returns what is and what is not in the database

Hi!!
I wonder if anyone can help. I have a select statement :e.g
select * from products
where productref in ('123456',
'123457',
'123458',
'123459'
and so on)
The products that are in the database come are returned as results.
So,
The query returns:
123456 - sadjsjfsdfjlksf
123457',- fdfslkdjfdj
123458',- dfsjflkdjlsjf
But I also need to find out what is not returned. There are about
10,000 lines and I need to find out what has not been returned. please
helpselect * from products
where productref NOT in ('123456',
'123457',
'123458',
'123459'
and so on)
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
"Himani" <himani77@.gmail.com> wrote in message
news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> Hi!!
> I wonder if anyone can help. I have a select statement :e.g
> select * from products
> where productref in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> The products that are in the database come are returned as results.
> So,
> The query returns:
> 123456 - sadjsjfsdfjlksf
> 123457',- fdfslkdjfdj
> 123458',- dfsjflkdjlsjf
> But I also need to find out what is not returned. There are about
> 10,000 lines and I need to find out what has not been returned. please
> help
>|||On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> select * from products
> where productref NOT in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>
> > Hi!!
> > I wonder if anyone can help. I have a select statement :e.g
> > select * from products
> > where productref in ('123456',
> > '123457',
> > '123458',
> > '123459'
> > and so on)
> > The products that are in the database come are returned as results.
> > So,
> > The query returns:
> > 123456 - sadjsjfsdfjlksf
> > 123457',- fdfslkdjfdj
> > 123458',- dfsjflkdjlsjf
> > But I also need to find out what is not returned. There are about
> > 10,000 lines and I need to find out what has not been returned. please
> > help- Hide quoted text -
> - Show quoted text -
I did try this but it did not work. the query returned other products.
perhaps I can explain a bit more.
The key thing is that some of the products are not on the database at
all.The List
'123456',
123457',
'123458',
'123459'
is from another system in which all the products are there. Whilst the
select query returns
the top 4 products as they are in the database. I want another query
that returns the ones that are not in the database at all - i.e. it
should return 123459 etc but it doesn't.|||> I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
One method:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '123456' AS productref
UNION ALL SELECT '123457'
UNION ALL SELECT '123458'
UNION ALL SELECT '123459'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
> On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> select * from products
>> where productref NOT in ('123456',
>> '123457',
>> '123458',
>> '123459'
>> and so on)
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication
>> book?http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL
>> FTShttp://www.indexserverfaq.com
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>>
>> > Hi!!
>> > I wonder if anyone can help. I have a select statement :e.g
>> > select * from products
>> > where productref in ('123456',
>> > '123457',
>> > '123458',
>> > '123459'
>> > and so on)
>> > The products that are in the database come are returned as results.
>> > So,
>> > The query returns:
>> > 123456 - sadjsjfsdfjlksf
>> > 123457',- fdfslkdjfdj
>> > 123458',- dfsjflkdjlsjf
>> > But I also need to find out what is not returned. There are about
>> > 10,000 lines and I need to find out what has not been returned. please
>> > help- Hide quoted text -
>> - Show quoted text -
> I did try this but it did not work. the query returned other products.
> perhaps I can explain a bit more.
> The key thing is that some of the products are not on the database at
> all.The List
> '123456',
> 123457',
> '123458',
> '123459'
> is from another system in which all the products are there. Whilst the
> select query returns
> the top 4 products as they are in the database. I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
>|||On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > I want another query
> > that returns the ones that are not in the database at all - i.e. it
> > should return 123459 etc but it doesn't.
> One method:
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '123456' AS productref
> UNION ALL SELECT '123457'
> UNION ALL SELECT '123458'
> UNION ALL SELECT '123459'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>
> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> >> select * from products
> >> where productref NOT in ('123456',
> >> '123457',
> >> '123458',
> >> '123459'
> >> and so on)
> >> --
> >> Hilary Cotter
> >> Looking for a SQL Server replication
> >> book?http://www.nwsu.com/0974973602.html
> >> Looking for a FAQ on Indexing Services/SQL
> >> FTShttp://www.indexserverfaq.com
> >> "Himani" <himan...@.gmail.com> wrote in message
> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> >> > Hi!!
> >> > I wonder if anyone can help. I have a select statement :e.g
> >> > select * from products
> >> > where productref in ('123456',
> >> > '123457',
> >> > '123458',
> >> > '123459'
> >> > and so on)
> >> > The products that are in the database come are returned as results.
> >> > So,
> >> > The query returns:
> >> > 123456 - sadjsjfsdfjlksf
> >> > 123457',- fdfslkdjfdj
> >> > 123458',- dfsjflkdjlsjf
> >> > But I also need to find out what is not returned. There are about
> >> > 10,000 lines and I need to find out what has not been returned. please
> >> > help- Hide quoted text -
> >> - Show quoted text -
> > I did try this but it did not work. the query returned other products.
> > perhaps I can explain a bit more.
> > The key thing is that some of the products are not on the database at
> > all.The List
> > '123456',
> > 123457',
> > '123458',
> > '123459'
> > is from another system in which all the products are there. Whilst the
> > select query returns
> > the top 4 products as they are in the database. I want another query
> > that returns the ones that are not in the database at all - i.e. it
> > should return 123459 etc but it doesn't.- Hide quoted text -
> - Show quoted text -
hi !!
Thanks a lot for sql code. I understand the query but there is a
slight problem. I do not have the access to the other database. I
have been given a list of product refs between the range
114203-124675. Now this was an upload that wasn't done properly. So,
roughly 5241 products are on the system and 5233 are not. The products
that the database contains are easily obtainable by the following
query:
select * from products where productref in (114203,114204,114205 etc)
So this query returns 114203,114204 as they are in the database. But I
need to know is there a way of retrieving 114205 which is not on the
database. Any tips would be much appreciated.
Regards
Himani|||> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
So you need to identify the products that are in the list but not in the
database, right? I believe the query I originally posted will do that -
just specify the actual list of all products in the list you were given as
the derrived table. For example, I would expect the query below to return
only 114205 if 11403 and 11404 are in the products table.
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
> On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > I want another query
>> > that returns the ones that are not in the database at all - i.e. it
>> > should return 123459 etc but it doesn't.
>> One method:
>> SELECT
>> OtherDatabaseList.productref
>> FROM
>> (SELECT '123456' AS productref
>> UNION ALL SELECT '123457'
>> UNION ALL SELECT '123458'
>> UNION ALL SELECT '123459'
>> ) OtherDatabaseList
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM dbo.products p
>> WHERE p.productref = OtherDatabaseList.productref
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>>
>> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> >> select * from products
>> >> where productref NOT in ('123456',
>> >> '123457',
>> >> '123458',
>> >> '123459'
>> >> and so on)
>> >> --
>> >> Hilary Cotter
>> >> Looking for a SQL Server replication
>> >> book?http://www.nwsu.com/0974973602.html
>> >> Looking for a FAQ on Indexing Services/SQL
>> >> FTShttp://www.indexserverfaq.com
>> >> "Himani" <himan...@.gmail.com> wrote in message
>> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>> >> > Hi!!
>> >> > I wonder if anyone can help. I have a select statement :e.g
>> >> > select * from products
>> >> > where productref in ('123456',
>> >> > '123457',
>> >> > '123458',
>> >> > '123459'
>> >> > and so on)
>> >> > The products that are in the database come are returned as results.
>> >> > So,
>> >> > The query returns:
>> >> > 123456 - sadjsjfsdfjlksf
>> >> > 123457',- fdfslkdjfdj
>> >> > 123458',- dfsjflkdjlsjf
>> >> > But I also need to find out what is not returned. There are about
>> >> > 10,000 lines and I need to find out what has not been returned.
>> >> > please
>> >> > help- Hide quoted text -
>> >> - Show quoted text -
>> > I did try this but it did not work. the query returned other products.
>> > perhaps I can explain a bit more.
>> > The key thing is that some of the products are not on the database at
>> > all.The List
>> > '123456',
>> > 123457',
>> > '123458',
>> > '123459'
>> > is from another system in which all the products are there. Whilst the
>> > select query returns
>> > the top 4 products as they are in the database. I want another query
>> > that returns the ones that are not in the database at all - i.e. it
>> > should return 123459 etc but it doesn't.- Hide quoted text -
>> - Show quoted text -
> hi !!
> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
> Regards
> Himani
>|||On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > Thanks a lot for sql code. I understand the query but there is a
> > slight problem. I do not have the access to the other database. I
> > have been given a list of product refs between the range
> > 114203-124675. Now this was an upload that wasn't done properly. So,
> > roughly 5241 products are on the system and 5233 are not. The products
> > that the database contains are easily obtainable by the following
> > query:
> > select * from products where productref in (114203,114204,114205 etc)
> > So this query returns 114203,114204 as they are in the database. But I
> > need to know is there a way of retrieving 114205 which is not on the
> > database. Any tips would be much appreciated.
> So you need to identify the products that are in the list but not in the
> database, right? I believe the query I originally posted will do that -
> just specify the actual list of all products in the list you were given as
> the derrived table. For example, I would expect the query below to return
> only 114205 if 11403 and 11404 are in the products table.
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '114203' AS productref
> UNION ALL SELECT '114204'
> UNION ALL SELECT '114205'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
>
> > On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> > wrote:
> >> > I want another query
> >> > that returns the ones that are not in the database at all - i.e. it
> >> > should return 123459 etc but it doesn't.
> >> One method:
> >> SELECT
> >> OtherDatabaseList.productref
> >> FROM
> >> (SELECT '123456' AS productref
> >> UNION ALL SELECT '123457'
> >> UNION ALL SELECT '123458'
> >> UNION ALL SELECT '123459'
> >> ) OtherDatabaseList
> >> WHERE NOT EXISTS
> >> (
> >> SELECT *
> >> FROM dbo.products p
> >> WHERE p.productref = OtherDatabaseList.productref
> >> )
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> "Himani" <himan...@.gmail.com> wrote in message
> >>news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
> >> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> >> >> select * from products
> >> >> where productref NOT in ('123456',
> >> >> '123457',
> >> >> '123458',
> >> >> '123459'
> >> >> and so on)
> >> >> --
> >> >> Hilary Cotter
> >> >> Looking for a SQL Server replication
> >> >> book?http://www.nwsu.com/0974973602.html
> >> >> Looking for a FAQ on Indexing Services/SQL
> >> >> FTShttp://www.indexserverfaq.com
> >> >> "Himani" <himan...@.gmail.com> wrote in message
> >> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> >> >> > Hi!!
> >> >> > I wonder if anyone can help. I have a select statement :e.g
> >> >> > select * from products
> >> >> > where productref in ('123456',
> >> >> > '123457',
> >> >> > '123458',
> >> >> > '123459'
> >> >> > and so on)
> >> >> > The products that are in the database come are returned as results.
> >> >> > So,
> >> >> > The query returns:
> >> >> > 123456 - sadjsjfsdfjlksf
> >> >> > 123457',- fdfslkdjfdj
> >> >> > 123458',- dfsjflkdjlsjf
> >> >> > But I also need to find out what is not returned. There are about
> >> >> > 10,000 lines and I need to find out what has not been returned.
> >> >> > please
> >> >> > help- Hide quoted text -
> >> >> - Show quoted text -
> >> > I did try this but it did not work. the query returned other products.
> >> > perhaps I can explain a bit more.
> >> > The key thing is that some of the products are not on the database at
> >> > all.The List
> >> > '123456',
> >> > 123457',
> >> > '123458',
> >> > '123459'
> >> > is from another system in which all the products are there. Whilst the
> >> > select query returns
> >> > the top 4 products as they are in the database. I want another query
> >> > that returns the ones that are not in the database at all - i.e. it
> >> > should return 123459 etc but it doesn't.- Hide quoted text -
> >> - Show quoted text -
> > hi !!
> > Thanks a lot for sql code. I understand the query but there is a
> > slight problem. I do not have the access to the other database. I
> > have been given a list of product refs between the range
> > 114203-124675. Now this was an upload that wasn't done properly. So,
> > roughly 5241 products are on the system and 5233 are not. The products
> > that the database contains are easily obtainable by the following
> > query:
> > select * from products where productref in (114203,114204,114205 etc)
> > So this query returns 114203,114204 as they are in the database. But I
> > need to know is there a way of retrieving 114205 which is not on the
> > database. Any tips would be much appreciated.
> > Regards
> > Himani- Hide quoted text -
> - Show quoted text -
Many Thanks. It did work. Just wondering if you could just explain the
scrip a bit if you had some time or comment it.
Regards
Himani|||> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
There are a few different techniques to get the desired list but all require
that you have a table that contains the complete list of products that
should be in the database. You could either create and load a table with
those products or use the derived table (inline view). I chose a derived
table because this was one-time query.
To expand derived tables, start with a query that returns the desired
result:
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
Then enclose it in parenthesis and specify an alias:
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
You can then use it anywhere in a query where a normal table can be
specified:
SELECT *
FROM
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
Now that you have the list of what should be in the database, you can
identify the rows that are not in the target table using one of several
techniques:
NOT EXISTS:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
NOT IN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE OtherDatabaseList.productref NOT IN
(
SELECT p.productref
FROM dbo.products p
)
Note that the NOT IN method is dangerous for nullable columns; no rows will
be returned if any NULL values are in the NOT IN list. I always use NOT
EXISTS instead to avoid that issue:
OUTER JOIN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
LEFT OUTER JOIN dbo.products p ON
p.productref = OtherDatabaseList.productref
WHERE p.productref IS NULL
MINUS (SQL 2005):
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
EXCEPT
SELECT p.productref FROM dbo.products p
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171296448.577247.172070@.l53g2000cwa.googlegroups.com...
> On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > Thanks a lot for sql code. I understand the query but there is a
>> > slight problem. I do not have the access to the other database. I
>> > have been given a list of product refs between the range
>> > 114203-124675. Now this was an upload that wasn't done properly. So,
>> > roughly 5241 products are on the system and 5233 are not. The products
>> > that the database contains are easily obtainable by the following
>> > query:
>> > select * from products where productref in (114203,114204,114205 etc)
>> > So this query returns 114203,114204 as they are in the database. But I
>> > need to know is there a way of retrieving 114205 which is not on the
>> > database. Any tips would be much appreciated.
>> So you need to identify the products that are in the list but not in the
>> database, right? I believe the query I originally posted will do that -
>> just specify the actual list of all products in the list you were given
>> as
>> the derrived table. For example, I would expect the query below to
>> return
>> only 114205 if 11403 and 11404 are in the products table.
>> SELECT
>> OtherDatabaseList.productref
>> FROM
>> (SELECT '114203' AS productref
>> UNION ALL SELECT '114204'
>> UNION ALL SELECT '114205'
>> ) OtherDatabaseList
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM dbo.products p
>> WHERE p.productref = OtherDatabaseList.productref
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
>>
>> > On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
>> > wrote:
>> >> > I want another query
>> >> > that returns the ones that are not in the database at all - i.e. it
>> >> > should return 123459 etc but it doesn't.
>> >> One method:
>> >> SELECT
>> >> OtherDatabaseList.productref
>> >> FROM
>> >> (SELECT '123456' AS productref
>> >> UNION ALL SELECT '123457'
>> >> UNION ALL SELECT '123458'
>> >> UNION ALL SELECT '123459'
>> >> ) OtherDatabaseList
>> >> WHERE NOT EXISTS
>> >> (
>> >> SELECT *
>> >> FROM dbo.products p
>> >> WHERE p.productref = OtherDatabaseList.productref
>> >> )
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> "Himani" <himan...@.gmail.com> wrote in message
>> >>news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>> >> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> >> >> select * from products
>> >> >> where productref NOT in ('123456',
>> >> >> '123457',
>> >> >> '123458',
>> >> >> '123459'
>> >> >> and so on)
>> >> >> --
>> >> >> Hilary Cotter
>> >> >> Looking for a SQL Server replication
>> >> >> book?http://www.nwsu.com/0974973602.html
>> >> >> Looking for a FAQ on Indexing Services/SQL
>> >> >> FTShttp://www.indexserverfaq.com
>> >> >> "Himani" <himan...@.gmail.com> wrote in message
>> >> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>> >> >> > Hi!!
>> >> >> > I wonder if anyone can help. I have a select statement :e.g
>> >> >> > select * from products
>> >> >> > where productref in ('123456',
>> >> >> > '123457',
>> >> >> > '123458',
>> >> >> > '123459'
>> >> >> > and so on)
>> >> >> > The products that are in the database come are returned as
>> >> >> > results.
>> >> >> > So,
>> >> >> > The query returns:
>> >> >> > 123456 - sadjsjfsdfjlksf
>> >> >> > 123457',- fdfslkdjfdj
>> >> >> > 123458',- dfsjflkdjlsjf
>> >> >> > But I also need to find out what is not returned. There are about
>> >> >> > 10,000 lines and I need to find out what has not been returned.
>> >> >> > please
>> >> >> > help- Hide quoted text -
>> >> >> - Show quoted text -
>> >> > I did try this but it did not work. the query returned other
>> >> > products.
>> >> > perhaps I can explain a bit more.
>> >> > The key thing is that some of the products are not on the database
>> >> > at
>> >> > all.The List
>> >> > '123456',
>> >> > 123457',
>> >> > '123458',
>> >> > '123459'
>> >> > is from another system in which all the products are there. Whilst
>> >> > the
>> >> > select query returns
>> >> > the top 4 products as they are in the database. I want another query
>> >> > that returns the ones that are not in the database at all - i.e. it
>> >> > should return 123459 etc but it doesn't.- Hide quoted text -
>> >> - Show quoted text -
>> > hi !!
>> > Thanks a lot for sql code. I understand the query but there is a
>> > slight problem. I do not have the access to the other database. I
>> > have been given a list of product refs between the range
>> > 114203-124675. Now this was an upload that wasn't done properly. So,
>> > roughly 5241 products are on the system and 5233 are not. The products
>> > that the database contains are easily obtainable by the following
>> > query:
>> > select * from products where productref in (114203,114204,114205 etc)
>> > So this query returns 114203,114204 as they are in the database. But I
>> > need to know is there a way of retrieving 114205 which is not on the
>> > database. Any tips would be much appreciated.
>> > Regards
>> > Himani- Hide quoted text -
>> - Show quoted text -
> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
> Regards
> Himani
>

A statement that returns what is and what is not in the database

Hi!!
I wonder if anyone can help. I have a select statement :e.g
select * from products
where productref in ('123456',
'123457',
'123458',
'123459'
and so on)
The products that are in the database come are returned as results.
So,
The query returns:
123456 - sadjsjfsdfjlksf
123457',- fdfslkdjfdj
123458',- dfsjflkdjlsjf
But I also need to find out what is not returned. There are about
10,000 lines and I need to find out what has not been returned. please
helpselect * from products
where productref NOT in ('123456',
'123457',
'123458',
'123459'
and so on)
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
"Himani" <himani77@.gmail.com> wrote in message
news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> Hi!!
> I wonder if anyone can help. I have a select statement :e.g
> select * from products
> where productref in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> The products that are in the database come are returned as results.
> So,
> The query returns:
> 123456 - sadjsjfsdfjlksf
> 123457',- fdfslkdjfdj
> 123458',- dfsjflkdjlsjf
> But I also need to find out what is not returned. There are about
> 10,000 lines and I need to find out what has not been returned. please
> help
>|||On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> select * from products
> where productref NOT in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?[url]http://www.nwsu.com/0974973602.html[/u
rl]
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.co
m
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
I did try this but it did not work. the query returned other products.
perhaps I can explain a bit more.
The key thing is that some of the products are not on the database at
all.The List
'123456',
123457',
'123458',
'123459'
is from another system in which all the products are there. Whilst the
select query returns
the top 4 products as they are in the database. I want another query
that returns the ones that are not in the database at all - i.e. it
should return 123459 etc but it doesn't.|||> I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
One method:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '123456' AS productref
UNION ALL SELECT '123457'
UNION ALL SELECT '123458'
UNION ALL SELECT '123459'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
> On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I did try this but it did not work. the query returned other products.
> perhaps I can explain a bit more.
> The key thing is that some of the products are not on the database at
> all.The List
> '123456',
> 123457',
> '123458',
> '123459'
> is from another system in which all the products are there. Whilst the
> select query returns
> the top 4 products as they are in the database. I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
>|||On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> One method:
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '123456' AS productref
> UNION ALL SELECT '123457'
> UNION ALL SELECT '123458'
> UNION ALL SELECT '123459'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
hi !!
Thanks a lot for sql code. I understand the query but there is a
slight problem. I do not have the access to the other database. I
have been given a list of product refs between the range
114203-124675. Now this was an upload that wasn't done properly. So,
roughly 5241 products are on the system and 5233 are not. The products
that the database contains are easily obtainable by the following
query:
select * from products where productref in (114203,114204,114205 etc)
So this query returns 114203,114204 as they are in the database. But I
need to know is there a way of retrieving 114205 which is not on the
database. Any tips would be much appreciated.
Regards
Himani|||> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
So you need to identify the products that are in the list but not in the
database, right? I believe the query I originally posted will do that -
just specify the actual list of all products in the list you were given as
the derrived table. For example, I would expect the query below to return
only 114205 if 11403 and 11404 are in the products table.
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
> On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
> hi !!
> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
> Regards
> Himani
>|||On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
>
> So you need to identify the products that are in the list but not in the
> database, right? I believe the query I originally posted will do that -
> just specify the actual list of all products in the list you were given as
> the derrived table. For example, I would expect the query below to return
> only 114205 if 11403 and 11404 are in the products table.
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '114203' AS productref
> UNION ALL SELECT '114204'
> UNION ALL SELECT '114205'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Many Thanks. It did work. Just wondering if you could just explain the
scrip a bit if you had some time or comment it.
Regards
Himani|||> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
There are a few different techniques to get the desired list but all require
that you have a table that contains the complete list of products that
should be in the database. You could either create and load a table with
those products or use the derived table (inline view). I chose a derived
table because this was one-time query.
To expand derived tables, start with a query that returns the desired
result:
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
Then enclose it in parenthesis and specify an alias:
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
You can then use it anywhere in a query where a normal table can be
specified:
SELECT *
FROM
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
Now that you have the list of what should be in the database, you can
identify the rows that are not in the target table using one of several
techniques:
NOT EXISTS:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
NOT IN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE OtherDatabaseList.productref NOT IN
(
SELECT p.productref
FROM dbo.products p
)
Note that the NOT IN method is dangerous for nullable columns; no rows will
be returned if any NULL values are in the NOT IN list. I always use NOT
EXISTS instead to avoid that issue:
OUTER JOIN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
LEFT OUTER JOIN dbo.products p ON
p.productref = OtherDatabaseList.productref
WHERE p.productref IS NULL
MINUS (SQL 2005):
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
EXCEPT
SELECT p.productref FROM dbo.products p
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171296448.577247.172070@.l53g2000cwa.googlegroups.com...
> On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
> Regards
> Himani
>

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big
enough to bring more rows, where might the problem mbe?
Declare @.ColList varchar(2000)
Declare @.CrLf varchar(10)
Select @.CrLf=Char(13) + Char(10)
Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
MyName From MyTable
Select @.ColListworks fine on my end.
JIM.H. wrote:
> This does not display more than 10 rows from the able, varchar(2000) is big
> enough to bring more rows, where might the problem mbe?
> Declare @.ColList varchar(2000)
> Declare @.CrLf varchar(10)
> Select @.CrLf=Char(13) + Char(10)
> Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
> MyName From MyTable
> Select @.ColList|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:
>This does not display more than 10 rows from the able, varchar(2000) is big
>enough to bring more rows, where might the problem mbe?
>Declare @.ColList varchar(2000)
>Declare @.CrLf varchar(10)
>Select @.CrLf=Char(13) + Char(10)
>Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
>MyName From MyTable
>Select @.ColList
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
--
Hugo Kornelis, SQL Server MVP

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

what is the error message|||

I do not see error, in the query analyzer, I set “Results in text” and run the query, I see first 8 records and (1 row(s) affected) message, it should show at least 50 rows. Is this a query analyzer problem?

|||

how about this

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName as nyfield From MyTable

|||

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

|||

i think QA is displaying it in a very long line

have this a try

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

print @.ColList

|||

JIM.H. wrote:

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

tried to simulate you can only make it until 4000

i think you should make use of cursor

|||

If you are using SQL 2005 please check the following:

Tools -> Options -> Query Results -> Results to Text Maximum number of characters displayed in each column (the default is 256)

Tools -> Options -> Query Results -> Results to Grid Maximum Characters Received Non XML data (the default is 65536)

In SQL 2000 in QA

Tools -> Options -> Results ->Maximum number of characters per column (the default is 256)

You may need to increase these numbers

|||

i tried this one in northwind

use northwind

Declare @.ColList char(8000)
Declare @.CrLf varchar(2)
Select @.CrLf=Char(13) + Char(10)
Select @.coLlist= COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + RTRIM(LTRIM(customerid)) From orders

print @.coLlist
select len(@.collist) as txtlength -<- check this out
select datalength(@.collist) as datalenght <-- and this is

here's the result

4000

8000

|||this worked. Thanks.|||there's a limit of byte per row that can be returned, inserted or updated...it's 8096 if i remember correctly...

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

Did you have any SET ROWCOUNT prior to running this SQL> I ran it on my machine and it worked fine for me.|||

If you are using Query Analyzer to execute the query, please go to Tools menu->Options->switch to Results tab->set the 'Maximum characters per column' to max allowed value 8192, then try again.

If you're using Management Studio and you have set to return result as text, please go to Tools->Options->Query Results->SQL Server->Results to Text->set the 'Maximum number of characters displayed in each column' to 8192

|||Thats right. I had changed mine to 1200 sometime back.

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big
enough to bring more rows, where might the problem mbe?
Declare @.ColList varchar(2000)
Declare @.CrLf varchar(10)
Select @.CrLf=Char(13) + Char(10)
Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
MyName From MyTable
Select @.ColListworks fine on my end.
JIM.H. wrote:
> This does not display more than 10 rows from the able, varchar(2000) is bi
g
> enough to bring more rows, where might the problem mbe?
> Declare @.ColList varchar(2000)
> Declare @.CrLf varchar(10)
> Select @.CrLf=Char(13) + Char(10)
> Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
> MyName From MyTable
> Select @.ColList|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:

>This does not display more than 10 rows from the able, varchar(2000) is big
>enough to bring more rows, where might the problem mbe?
>Declare @.ColList varchar(2000)
>Declare @.CrLf varchar(10)
>Select @.CrLf=Char(13) + Char(10)
>Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
>MyName From MyTable
>Select @.ColList
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
[url]http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html[/u
rl]
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
Hugo Kornelis, SQL Server MVP

a simple question about SQL Update statement

hi, everyone,

When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen?

Can somebody help me? Thanks in advance!

Hmmm, I disagree with your observation. An UPDATE statement will not perform an INSERT. You must have some other code happening behind the scenes which is performing the INSERT.|||What's the VBS code that you used for UPDATE table? BTW, there is no SQL2003 server Should be SQL2005?

A Simple Insert statement in European version of SQL

I have a client that uses my utility program to insert a record into a
table. Once my program receives the values, creates an insert statement
with comma separated values. Ie:
Insert into T (a,b) values (1.578, 2)
I now understand that those numeric values could have ',' in place of
decimal point for European version. So, the above values would look like
1,578 and 2.
How does the insert statement would know ',' is not a separator in this
case ? Ie:
Insert into T (a,b) values (1,578, 2) --> resulting
into syntax error
Should I be using a different value separator character ?
TIA.
MacI am not the one producing the values with ',' in place of decimal points.
It is SQL Server of European version that returns the data I am collecting
with embedded comma. So, when I submit a query of "Select a from T"
( a is defined to be a real type number), it returns 2,476 instead of
2.476. So, my question is how do I take these returned value with
embedded comma and insert them back into say another field in a table ? I
do not have such SQL version in my site to see what is going on and how to
accomplish such inserts.
- Mac
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> Hi Mac,
> Please do not use comma as decimal separator. It will cause problems. Use
> period as decimal point.
> Character expressions being converted to an exact numeric data type must
> consist of digits, a decimal point, and an optional plus (+) or minus (-).
> Leading blanks are ignored. Comma separators (such as the thousands
> separator in 123,456.00) are not allowed in the string.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> | Newsgroups: microsoft.public.sqlserver.server
> | Subject: A Simple Insert statement in European version of SQL
> | Date: Mon, 25 Aug 2003 16:49:15 -0700
> | Organization: Unisys - Roseville, MN
> | Lines: 20
> | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> | NNTP-Posting-Host: 192.59.171.175
> | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> 2003 23:49:15 GMT)
> | X-Complaints-To: news@.rsvl.unisys.com
> | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have a client that uses my utility program to insert a record into a
> | table. Once my program receives the values, creates an insert
statement
> | with comma separated values. Ie:
> | Insert into T (a,b) values (1.578, 2)
> |
> | I now understand that those numeric values could have ',' in place of
> | decimal point for European version. So, the above values would look
> like
> | 1,578 and 2.
> |
> | How does the insert statement would know ',' is not a separator in this
> | case ? Ie:
> | Insert into T (a,b) values (1,578, 2) --> resulting
> | into syntax error
> |
> | Should I be using a different value separator character ?
> |
> | TIA.
> | Mac
> |
> |
> |
>|||Are you using Visual basic ?
VB does use the client-settings to format numbers, dates,...
You'll have to use a format function to convert to a propre string.
e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
"###0.00") & ")"
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:big51p$5vu$1@.si05.rsvl.unisys.com...
> I am not the one producing the values with ',' in place of decimal points.
> It is SQL Server of European version that returns the data I am collecting
> with embedded comma. So, when I submit a query of "Select a from T"
> ( a is defined to be a real type number), it returns 2,476 instead of
> 2.476. So, my question is how do I take these returned value with
> embedded comma and insert them back into say another field in a table ?
I
> do not have such SQL version in my site to see what is going on and how to
> accomplish such inserts.
> - Mac
>
> ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > Hi Mac,
> >
> > Please do not use comma as decimal separator. It will cause problems.
Use
> > period as decimal point.
> >
> > Character expressions being converted to an exact numeric data type must
> > consist of digits, a decimal point, and an optional plus (+) or minus
(-).
> > Leading blanks are ignored. Comma separators (such as the thousands
> > separator in 123,456.00) are not allowed in the string.
> >
> >
> >
> > Bill Cheng
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > --
> > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > | Newsgroups: microsoft.public.sqlserver.server
> > | Subject: A Simple Insert statement in European version of SQL
> > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > | Organization: Unisys - Roseville, MN
> > | Lines: 20
> > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > | NNTP-Posting-Host: 192.59.171.175
> > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > 2003 23:49:15 GMT)
> > | X-Complaints-To: news@.rsvl.unisys.com
> > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > | Path:
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > |
> > | I have a client that uses my utility program to insert a record into a
> > | table. Once my program receives the values, creates an insert
> statement
> > | with comma separated values. Ie:
> > | Insert into T (a,b) values (1.578, 2)
> > |
> > | I now understand that those numeric values could have ',' in place of
> > | decimal point for European version. So, the above values would look
> > like
> > | 1,578 and 2.
> > |
> > | How does the insert statement would know ',' is not a separator in
this
> > | case ? Ie:
> > | Insert into T (a,b) values (1,578, 2) -->
resulting
> > | into syntax error
> > |
> > | Should I be using a different value separator character ?
> > |
> > | TIA.
> > | Mac
> > |
> > |
> > |
> >
>|||As jobi point out: You have to differentiate between input and output. Just because a client tool
formats something that SQL Server outputs with a comma doesn't mean that SQL Server accepts that as
a valid input format.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"jobi" <jobi@.reply2.group> wrote in message news:bihkck$boq$1@.reader08.wxs.nl...
> Are you using Visual basic ?
> VB does use the client-settings to format numbers, dates,...
> You'll have to use a format function to convert to a propre string.
> e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> "###0.00") & ")"
> jobi
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > I am not the one producing the values with ',' in place of decimal points.
> > It is SQL Server of European version that returns the data I am collecting
> > with embedded comma. So, when I submit a query of "Select a from T"
> > ( a is defined to be a real type number), it returns 2,476 instead of
> > 2.476. So, my question is how do I take these returned value with
> > embedded comma and insert them back into say another field in a table ?
> I
> > do not have such SQL version in my site to see what is going on and how to
> > accomplish such inserts.
> >
> > - Mac
> >
> >
> > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > Hi Mac,
> > >
> > > Please do not use comma as decimal separator. It will cause problems.
> Use
> > > period as decimal point.
> > >
> > > Character expressions being converted to an exact numeric data type must
> > > consist of digits, a decimal point, and an optional plus (+) or minus
> (-).
> > > Leading blanks are ignored. Comma separators (such as the thousands
> > > separator in 123,456.00) are not allowed in the string.
> > >
> > >
> > >
> > > Bill Cheng
> > > Microsoft Online Partner Support
> > >
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > --
> > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > | Newsgroups: microsoft.public.sqlserver.server
> > > | Subject: A Simple Insert statement in European version of SQL
> > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > | Organization: Unisys - Roseville, MN
> > > | Lines: 20
> > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > | NNTP-Posting-Host: 192.59.171.175
> > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > > 2003 23:49:15 GMT)
> > > | X-Complaints-To: news@.rsvl.unisys.com
> > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > | X-Priority: 3
> > > | X-MSMail-Priority: Normal
> > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > | Path:
> > >
> >
> cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > >
> >
> m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > |
> > > | I have a client that uses my utility program to insert a record into a
> > > | table. Once my program receives the values, creates an insert
> > statement
> > > | with comma separated values. Ie:
> > > | Insert into T (a,b) values (1.578, 2)
> > > |
> > > | I now understand that those numeric values could have ',' in place of
> > > | decimal point for European version. So, the above values would look
> > > like
> > > | 1,578 and 2.
> > > |
> > > | How does the insert statement would know ',' is not a separator in
> this
> > > | case ? Ie:
> > > | Insert into T (a,b) values (1,578, 2) -->
> resulting
> > > | into syntax error
> > > |
> > > | Should I be using a different value separator character ?
> > > |
> > > | TIA.
> > > | Mac
> > > |
> > > |
> > > |
> > >
> >
> >
>|||Mac,
I've checked my vb-code again, and found this extra.
'Aparently the format still uses the client-setting for decimal point !!!
replace$(string, ",",".")
so you'll have to come up to this :
e.g. strsql = "insert into table1 (col1) values (" &
Replace$(Format(numcol, "###0.00"),",",".") & ")"
I guess you don't need the ###-part, in fact, you only need the format if
you want control of the format, else you can use the cstr-function.
Replace$(CStr(numcol), ",", ".")
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:bijdkf$2fsd$1@.si05.rsvl.unisys.com...
> Jobi,
> Yes. I am using VB. Also when I am collecting the data, I use Format
> function to make sure I only get 2 digits decimal point. The format
> sysntax I use is following:
> Format(numValue, "0.00").
> Is this not correct ? Do I need the ### in front of them ? More like
aVB
> question...
> By using the Format function I thought I am also forcing the decimal point
> to show up as decimal point despite the local setting of the computer.
This
> way then I can turn around and use the result in another insert statement
> without further formatting.
> Thanks for the input.
> Mac
> "jobi" <jobi@.reply2.group> wrote in message
> news:bihkck$boq$1@.reader08.wxs.nl...
> > Are you using Visual basic ?
> >
> > VB does use the client-settings to format numbers, dates,...
> > You'll have to use a format function to convert to a propre string.
> >
> > e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> > "###0.00") & ")"
> >
> > jobi
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > > I am not the one producing the values with ',' in place of decimal
> points.
> > > It is SQL Server of European version that returns the data I am
> collecting
> > > with embedded comma. So, when I submit a query of "Select a from
T"
> > > ( a is defined to be a real type number), it returns 2,476 instead
of
> > > 2.476. So, my question is how do I take these returned value with
> > > embedded comma and insert them back into say another field in a table
?
> > I
> > > do not have such SQL version in my site to see what is going on and
how
> to
> > > accomplish such inserts.
> > >
> > > - Mac
> > >
> > >
> > > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > > Hi Mac,
> > > >
> > > > Please do not use comma as decimal separator. It will cause
problems.
> > Use
> > > > period as decimal point.
> > > >
> > > > Character expressions being converted to an exact numeric data type
> must
> > > > consist of digits, a decimal point, and an optional plus (+) or
minus
> > (-).
> > > > Leading blanks are ignored. Comma separators (such as the thousands
> > > > separator in 123,456.00) are not allowed in the string.
> > > >
> > > >
> > > >
> > > > Bill Cheng
> > > > Microsoft Online Partner Support
> > > >
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > --
> > > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > > | Newsgroups: microsoft.public.sqlserver.server
> > > > | Subject: A Simple Insert statement in European version of SQL
> > > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > > | Organization: Unisys - Roseville, MN
> > > > | Lines: 20
> > > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > > | NNTP-Posting-Host: 192.59.171.175
> > > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25
> Aug
> > > > 2003 23:49:15 GMT)
> > > > | X-Complaints-To: news@.rsvl.unisys.com
> > > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > > | X-Priority: 3
> > > > | X-MSMail-Priority: Normal
> > > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > > | Path:
> > > >
> > >
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > > >
> > >
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > > | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.server:303093
> > > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > > |
> > > > | I have a client that uses my utility program to insert a record
into
> a
> > > > | table. Once my program receives the values, creates an insert
> > > statement
> > > > | with comma separated values. Ie:
> > > > | Insert into T (a,b) values (1.578, 2)
> > > > |
> > > > | I now understand that those numeric values could have ',' in
place
> of
> > > > | decimal point for European version. So, the above values would
> look
> > > > like
> > > > | 1,578 and 2.
> > > > |
> > > > | How does the insert statement would know ',' is not a separator
in
> > this
> > > > | case ? Ie:
> > > > | Insert into T (a,b) values (1,578, 2) -->
> > resulting
> > > > | into syntax error
> > > > |
> > > > | Should I be using a different value separator character ?
> > > > |
> > > > | TIA.
> > > > | Mac
> > > > |
> > > > |
> > > > |
> > > >
> > >
> > >
> >
> >
>

A Search for SQLFiles

Someone at my job who was a IT Professional before me wrote a SQL
statement. That statement or file is working with a program that we
use called Papervision and the purpose of that SQL is to assign data
to a table. I need help on how to locate this SQL file. I know it is
running because it is working with the program papervision but it is
not working correctly. Is there any easy way to locate SQL files off
the server we are using?On Mar 13, 1:14 pm, "The Man" <LeJon7...@.gmail.comwrote:

Quote:

Originally Posted by

Someone at my job who was a IT Professional before me wrote a SQL
statement. That statement or file is working with a program that we
use called Papervision and the purpose of that SQL is to assign data
to a table. I need help on how to locate this SQL file. I know it is
running because it is working with the program papervision but it is
not working correctly. Is there any easy way to locate SQL files off
the server we are using?

Yes. Try the following:

On the server click "Start" and then choose "Search."
In the box that opens, where is says "All or part of the file name"
enter *.sql
Where it says "Look in" select "Local Hard Drives"
Click the button that says "Search"

Then just wait for the results.|||On Mar 13, 11:14 pm, "The Man" <LeJon7...@.gmail.comwrote:

Quote:

Originally Posted by

Someone at my job who was a IT Professional before me wrote a SQL
statement. That statement or file is working with a program that we
use called Papervision and the purpose of that SQL is to assign data
to a table. I need help on how to locate this SQL file. I know it is
running because it is working with the program papervision but it is
not working correctly. Is there any easy way to locate SQL files off
the server we are using?

What is papervision? Are you familiar with this product? Maybe the sql
is located somewhere within this app.|||On Mar 14, 4:56 pm, othell...@.yahoo.com wrote:

Quote:

Originally Posted by

On Mar 13, 11:14 pm, "The Man" <LeJon7...@.gmail.comwrote:
>

Quote:

Originally Posted by

Someone at my job who was a IT Professional before me wrote a SQL
statement. That statement or file is working with a program that we
use called Papervision and the purpose of that SQL is to assign data
to a table. I need help on how to locate this SQL file. I know it is
running because it is working with the program papervision but it is
not working correctly. Is there any easy way to locate SQL files off
the server we are using?

>
What is papervision? Are you familiar with this product? Maybe the sql
is located somewhere within this app.

I got some idea from your other post regarding asking for 'SQL Help'.
It seems that your app scans invoices and puts them into sql server
and when you tried to import new data (to match and merge) it failed
with an ODBC error. Your best bet is the product support help. Also,
the server might have some tables that store your invoices (scanned)
with all the information. You might want to find out from QA under
database-tables and view the data itself and might want to get to the
data itself using Enterprise Manager!

A Range Query Optimization

Hi,
Need help in optimizing a query in SQL Server.
Following is the problem statement.
There are two tables;
1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
this is not the primary key. The table can have billions of records;
in test environment, we are having 3,000,000 records
2nd table (t2) has two columns a from_Range and to_Range, both
char(8). this table has lesser number of records, in thousands.
Clustered index is on the primary key.
However there is no relation whatsoever between the KEY and the
from/to range.
We need to find matching records where Key is found between the from
and to range :
select t1.id, t2.id from t1, t2
where t1.KEY between t2.from_range and t2.to_range
( The ids form part of primary keys in both tables. )
The plan shows a loop, with t1 using clustered index of KEY and t2
using clustered index of the primary key.
This query is taking around 14 seconds on SQL server 2000 in win2kpro
with P4 and 512 MB RAM.
Is there any way this can be reducd to a subsecond performance ? This
query forms the core of most of the processing, and any reduction here
will have recursive effect all over.
Thanks in advance,
roy.
aroy (anindya.roy@.rave-tech.com) writes:
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.
But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
http://groups.google.com/groups?hl=s...396%40edtnps84
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||On 29 Jul 2004 00:43:27 -0700, aroy wrote:
(snip)
See my reply in comp.databases.ms-sqlserver.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).
(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)
Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))
Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)
SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)
"aroy" <anindya.roy@.rave-tech.com> wrote in message
news:be87bcc0.0407282343.1bbda35f@.posting.google.c om...
> Hi,
> Need help in optimizing a query in SQL Server.
> Following is the problem statement.
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
> ( The ids form part of primary keys in both tables. )
> The plan shows a loop, with t1 using clustered index of KEY and t2
> using clustered index of the primary key.
> This query is taking around 14 seconds on SQL server 2000 in win2kpro
> with P4 and 512 MB RAM.
> Is there any way this can be reducd to a subsecond performance ? This
> query forms the core of most of the processing, and any reduction here
> will have recursive effect all over.
> Thanks in advance,
> roy.