Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
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/ms130214(en-US,SQL.90).aspx
--|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
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
Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts
Monday, March 12, 2012
query or stored procedure to insert values into pr. key field
Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)
bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
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/ms130214(en-US,SQL.90).aspx
|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.
|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)
bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
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/ms130214(en-US,SQL.90).aspx
|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.
|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Friday, March 9, 2012
Query optimization question...
I'm trying to optimize some queries on an existing system, and I'm
noticing some odd behavior. I'm performing a join between several
tables, the final table being joined by the optimizer has a clustered
index on the field that it is using to join to the rest of the query,
but for some reason SQL Server doesn't seem to actually use this index
(it's doing an index scan instead of an index seek). Is there some
reason why SQL Server would not use a valid Clustered Index? I've
dropped and readded the index, but that doesn't seem to help. I don't
know if it would be relevant, but the tables I'm working on are fairly
fat (2 to 7K bytes/row).
noticing some odd behavior. I'm performing a join between several
tables, the final table being joined by the optimizer has a clustered
index on the field that it is using to join to the rest of the query,
but for some reason SQL Server doesn't seem to actually use this index
(it's doing an index scan instead of an index seek). Is there some
reason why SQL Server would not use a valid Clustered Index? I've
dropped and readded the index, but that doesn't seem to help. I don't
know if it would be relevant, but the tables I'm working on are fairly
fat (2 to 7K bytes/row).
This is happening for several tables. I've been able to get around it
for some of the tables by creating a non-clustered index on all the
fields that are being queried so that the leaf pages don't need to be
loaded, but this isn't a valid solution for all of the tables I'm
struggling with.
Any ideas? (and no, they aren't willing to redesign any of the
tables)."Mathew Relick" <ticars@.yahoo.com> wrote in message
news:dd84d8a7.0311181017.39d1c69@.posting.google.co m...
> I'm trying to optimize some queries on an existing system, and I'm
> noticing some odd behavior. I'm performing a join between several
> tables, the final table being joined by the optimizer has a clustered
> index on the field that it is using to join to the rest of the query,
> but for some reason SQL Server doesn't seem to actually use this index
> (it's doing an index scan instead of an index seek). Is there some
> reason why SQL Server would not use a valid Clustered Index? I've
> dropped and readded the index, but that doesn't seem to help. I don't
This can happen if your table statistics are out of date. If SQL Server
determines that the join will require more than x % of the table to be
retrieved, then doing a clustered index scan may be faster than doing a
clustered index seek (faster because an index scan can read the rows in a
page sequentially, whereas an index seek has to traverse the B-tree
structure. Sequential read is faster because you cut down on seek time, and
also because you may be able to read more than one page in a single I/O
operation, since the data is sequential.) Try updating statistics and see
if it helps:
UPDATE STATISTICS <table_name> WITH FULLSCAN
GO
> know if it would be relevant, but the tables I'm working on are fairly
> fat (2 to 7K bytes/row).
> This is happening for several tables. I've been able to get around it
> for some of the tables by creating a non-clustered index on all the
> fields that are being queried so that the leaf pages don't need to be
> loaded, but this isn't a valid solution for all of the tables I'm
> struggling with.
This is because when you create a new index, new statistics are generated
for that index, so you have the most up-to-date statistics with your new
index. Same if you rebuild your existing indexes.
HTH,
Dave
>
> Any ideas? (and no, they aren't willing to redesign any of the
> tables).|||"Dave Hau" <nospam_dave_nospam_123@.nospam_netscape_nospam.net_ nospam> wrote
in message news:gCvub.33788$yj4.5497@.newssvr27.news.prodigy.c om...
> "Mathew Relick" <ticars@.yahoo.com> wrote in message
> news:dd84d8a7.0311181017.39d1c69@.posting.google.co m...
> > I'm trying to optimize some queries on an existing system, and I'm
> > noticing some odd behavior. I'm performing a join between several
> > tables, the final table being joined by the optimizer has a clustered
> > index on the field that it is using to join to the rest of the query,
> > but for some reason SQL Server doesn't seem to actually use this index
> > (it's doing an index scan instead of an index seek). Is there some
> > reason why SQL Server would not use a valid Clustered Index? I've
> > dropped and readded the index, but that doesn't seem to help. I don't
> This can happen if your table statistics are out of date. If SQL Server
> determines that the join will require more than x % of the table to be
> retrieved, then doing a clustered index scan may be faster than doing a
> clustered index seek (faster because an index scan can read the rows in a
> page sequentially, whereas an index seek has to traverse the B-tree
> structure. Sequential read is faster because you cut down on seek time,
and
> also because you may be able to read more than one page in a single I/O
> operation, since the data is sequential.) Try updating statistics and see
> if it helps:
> UPDATE STATISTICS <table_name> WITH FULLSCAN
> GO
> > know if it would be relevant, but the tables I'm working on are fairly
> > fat (2 to 7K bytes/row).
> > This is happening for several tables. I've been able to get around it
> > for some of the tables by creating a non-clustered index on all the
> > fields that are being queried so that the leaf pages don't need to be
> > loaded, but this isn't a valid solution for all of the tables I'm
> > struggling with.
> This is because when you create a new index, new statistics are generated
> for that index, so you have the most up-to-date statistics with your new
> index. Same if you rebuild your existing indexes.
I'm going to chime in because I think Dave has some good points here.
However, as I understand it, the original poster did rebuild the clustered
index, so there may be more to this problem than meets the eye.
One other thing that can happen is that the optimizer decides it's faster to
do a scan instead of a seek. This is particularly true if the result it
expects to return is a large percentage of the index. (i.e. if you have 100
rows and will return 80).
I'll be honest, I'm not sure exactly how this applies with a clustered
index.
> HTH,
> Dave
> > Any ideas? (and no, they aren't willing to redesign any of the
> > tables).
Monday, February 20, 2012
query needed for path navigation through a web site
hi experts,
i'm working in building new reports from an existing database. the report i'm working in is to save the path of the visitor through a web site(this is neede for the statistics web site), i have the siteId, commid, maintab, subtab.
the site id is dtored in site table, maintab and subtab are stored in article(they are mixed in one columns called title) i have also sessionid stored in session table.
i want a query that show the flow of the visitor through a web site, which tab he clicked first then second tab then third tab and in this tab he clicked subtab and the last tab he clicked on before leaving the web page.
is this possible and if not, what are the other approches that can i make to get the report i want.
also i want to ask if it is possible to create the report where it will show you the visitors for the first time and the returned visitors.
thanksHow do you determine the order of the pages visited? Are you storing timestamps or incrementally increasing IDs?|||hi,
thanks for the answer, actually i'm storing also the timestamp, and i think you have a point there, a timestamp is a good way to know the order, but i think a lot of information will be stored in the database if i decided to store the timestamp, do you have any other approach because at this time we can not afford tos tore a lot of information?
thanks|||A datetime column is not going to take up a lot of disk space. I can't think of any solution that would be cheaper in terms of bytes except an auto-incrementing identity surrogate key, and this is not the intended use of surrogate keys.|||there are plenty of 3rd party apps that do all this - you are reinventing the wheel if you code it yourself.
have you looked at google analytics?
http://www.google.com/analytics/|||ok.
blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
jezemine, can you explain more.
what about the report for showing the returning visitors?
thanks|||I use google analytics to analyze traffic on my website. I find it very good, and it's free. You can do all sorts of stuff, like see where people are referred from, most common navigation paths they take through your site, etc.
Did you look at the link I provided?
also there are a ton of 3rd party apps for parsing web logs, many are free and the ones that aren't almost always have trial versions. all you need to do is look for them:
http://www.google.com/search?q=web+log+analysis|||ok.
blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
jezemine, can you explain more.
what about the report for showing the returning visitors?
thanks
Okay, assuming you have the timestamp that will give you the order in which the pages were visited. Then you just need a way to differentiate between the different people/logins/connections that visit each page. What is your method of accomplishing this?|||thanks for your answers and sorry for being late to answer back.
i have all th information stored about the user, i have sessionid,siteid, i did not think of it yet, but in your point of view which one is the best?
any ideas?!!!
i will take a look also to the link you give me.
thanks|||So can't you just sort your data by sessionid and timestamp to get the order of the pages visited?|||hi,
can you give me an example or a query how to do it?
thanks|||select * from YourData order by SessionID, TimeStamp
What is the problem here?|||it seems too easy but i think you are right.
i will work on the report and i will post back if there is a problem, thaks for your help.
what about the report to store the IP's any idea about that.
thanks again and have a nice week end
i'm working in building new reports from an existing database. the report i'm working in is to save the path of the visitor through a web site(this is neede for the statistics web site), i have the siteId, commid, maintab, subtab.
the site id is dtored in site table, maintab and subtab are stored in article(they are mixed in one columns called title) i have also sessionid stored in session table.
i want a query that show the flow of the visitor through a web site, which tab he clicked first then second tab then third tab and in this tab he clicked subtab and the last tab he clicked on before leaving the web page.
is this possible and if not, what are the other approches that can i make to get the report i want.
also i want to ask if it is possible to create the report where it will show you the visitors for the first time and the returned visitors.
thanksHow do you determine the order of the pages visited? Are you storing timestamps or incrementally increasing IDs?|||hi,
thanks for the answer, actually i'm storing also the timestamp, and i think you have a point there, a timestamp is a good way to know the order, but i think a lot of information will be stored in the database if i decided to store the timestamp, do you have any other approach because at this time we can not afford tos tore a lot of information?
thanks|||A datetime column is not going to take up a lot of disk space. I can't think of any solution that would be cheaper in terms of bytes except an auto-incrementing identity surrogate key, and this is not the intended use of surrogate keys.|||there are plenty of 3rd party apps that do all this - you are reinventing the wheel if you code it yourself.
have you looked at google analytics?
http://www.google.com/analytics/|||ok.
blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
jezemine, can you explain more.
what about the report for showing the returning visitors?
thanks|||I use google analytics to analyze traffic on my website. I find it very good, and it's free. You can do all sorts of stuff, like see where people are referred from, most common navigation paths they take through your site, etc.
Did you look at the link I provided?
also there are a ton of 3rd party apps for parsing web logs, many are free and the ones that aren't almost always have trial versions. all you need to do is look for them:
http://www.google.com/search?q=web+log+analysis|||ok.
blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
jezemine, can you explain more.
what about the report for showing the returning visitors?
thanks
Okay, assuming you have the timestamp that will give you the order in which the pages were visited. Then you just need a way to differentiate between the different people/logins/connections that visit each page. What is your method of accomplishing this?|||thanks for your answers and sorry for being late to answer back.
i have all th information stored about the user, i have sessionid,siteid, i did not think of it yet, but in your point of view which one is the best?
any ideas?!!!
i will take a look also to the link you give me.
thanks|||So can't you just sort your data by sessionid and timestamp to get the order of the pages visited?|||hi,
can you give me an example or a query how to do it?
thanks|||select * from YourData order by SessionID, TimeStamp
What is the problem here?|||it seems too easy but i think you are right.
i will work on the report and i will post back if there is a problem, thaks for your help.
what about the report to store the IP's any idea about that.
thanks again and have a nice week end
Subscribe to:
Posts (Atom)