an insert

Hello,

I need to realize an insert something like the following:

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTO MyTable2 (MyId,MyName)

SELECT @.MyId,MyName

FROM MyTable1

Here I am getting MyID from a stored procedure and I need to insert this to MyTable2, however I need to get a new MyID for each row in MyTable1. How can I do that?

I think you will have to do a loop here and do the EXEC for each loop.|||

You could try wrapping the stored procedure in a user defined function, then referencing it in your insert. So if the function was fn_NewKey, it'd be something like

Insert into MyTable2(MyID, MyName)

select fn_NewKey() as fred, MyName

from MyTable1

Incidentall, why don't you just use an identity column for MyID? It would be a lot easier.

|||I think you will have to do a loop here and do the EXEC for each loop.|||aaaaaaaaaaaa

an insert

Hello,

I need to realize an insert something like the following:

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTOMyTable2 (MyId,MyName)

SELECT@.MyId,MyName

FROMMyTable1

Here I am getting MyID from a stored procedure and I need to insert this to MyTable2, however I need to get a new MyID for each row in MyTable1. How can I do that?

you could write a view which uses a cursor to step through all the names and inserts the names and the result of that proc into a table, and then insert that.

What does the proc do ? Where does the ID come from that you need to use the proc ?

|||Please don't go the route of using cursors or writing procedural code. SQL is a set-based language and you will get the best performance if you utilize it. What does the SP GetMyID do? Why can't you use IDENTITY column on the table to generate automatic sequential numbers? This is a much more efficient mechanism. You can get the multiple ids generated by SQL Server using OUTPUT clause in SQL Server 2005 or via a trigger that dumps the rows from inserted table in SQL Server 2000 or requery base-table using the alternate key values from the rows that you inserted.|||

I would concur that a cursor is a last resort, but I was assuming there was a reason that the proc needs to be called on a line by line basis ( hence my questions about the nature of hte proc also ).

I was also assuming he needs to do a single insert, not that this code is going to be run regularly. b/c if the insert needs to happen on an ongoing basis, it should happen as each record is created.

|||

GetMyID Return an Id which is unique. This is SQL 2000, can you give me an example of trigger. I am just trying to get a new id for each row in my table.

|||

It generates an ID ? Ideally, I would have an identity column in the first table, and just insert the ID from the first table into the second ( so the string is only in your database once, and you can change it there to see it change throughout the database ).

A trigger is a proc that is fired when you perform a specific action such as an insert into a specific table. The help has lots of info on how they work.

|||

Hi cgraus,

That is true it is an id, however it is not identity, the way that the system is designed it creates a specific id. Is there any trigger example you can give me to accomplish to call the stored procedure for each row inserted into MyTable2 meaning accomplishes the following for each row.

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTO MyTable2 (MyId,MyName)

SELECT @.MyId,MyName

FROM MyTable1

|||

I guess you could do a trigger that runs when you insert a name in to MyTable2 ( I assume these names are contrived ), which calls the proc to generate the ID.

If an ID exists, why don't you store it in the table and use it throughout the system, regardless of where it comes from ?

http://www.codeproject.com/database/SquaredRomis.asp

Basically, the syntax is

CREATE TRIGGER invUpdate ON [Table2]
FOR INSERT

AS

...

Inside an insert trigger, the 'inserted' table will give you access to the items that were inserted. I am not sure if it's called once per line. If not, then a bulk insert leaves you with the same problem, but just within the trigger.

Amount of Normalization

Hello all,

My question concerns the amount of normalization i require for my specific needs. I realize this is a difficult question without knowing alot more about my database. I am hoping with some information I could get advice from those more experienced than I.

- My database consists of 9 tables with the maximim number of columns being 11 which is the contacts table.

- the largest data type is nvarchar 125.

- number of rows in the largest table will eventually grow to hundreds of thousands.

- users access the database online

This is large to me but I expect that to some of you this not.

My application would be easier to setup if the contacts table were to include address info.

So my question is, for a database of this size could I create a contacts table similar to the customer table in the Microsoft Northwind sample data base with the address included, or should I model something more like the contact table in the Microsoft Adventureworks db with the address and State/province split to separate tables.

Any help you could provide with this scetchy info would be greatly appreciated.

Hi,

for me the amount of normalization should not be decided on the number of possible stored rows, as the amount could raise unexpectly. It should be decided on design considerations as databases of Microsoft are. The business case for the northwind database might be that a customer can only have one address, otherwise he will get another customer number (This could be a business decision). The thoughts of the Adventureworks database and the fictional company might be that a customer can have many subsidaries and therefore many addresses.

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

|||

Blair,

See the responses to your very similar post here.

The only real reason to separate Addresses from Contacts would be if there were multiple Contacts per Address.

|||

Hello and thank you for your response,

Thanks to your answers from a previous post I now have a better understanding on normalization and denormalization.

What I was unable to determine from the answers to my previous post was the performance impact differing amounts of normalization would have on varying databases. In providing some information as to the size of my db I was hoping for some insight as to whether or not the amount of normalization would impact the performance.

Thanks again for your responses they were helpful.

|||

Good question Blair.

Your database is so small in relative terms that I wouldn't be concerned with any performance issues related to trying to move the contacts table into third normal form.

The only significant issue for you is the potential syncronization issues related to multiple contacts having the same address information, i.e., several folks working for the same company, and the company merges now having a new address. You would have to have a process in place to find all occurances of the old address and determine if it is appropriate to change it for each contact. Did ALL of them move to the new address, or just some of them... Questions, questions, question...

ONLY if you expect a significant proportion of the contacts to share an address would I consider separating the address information from the contact information.

Alternatives to builtin graphs

Is there any possible way to bundle my reports with references to other
controls to build a visual representation of data? I realize that this is
all informational, but I've got a problem with the stock graphs, they look
HORRIBLE.
Anyway, thanks in advance
WestonNot with this version. I gather the next version will have more options
along this line (release date in November).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Weston Weems" <wweems@.NOSPAMno-span-gmail.com> wrote in message
news:eR18CVagFHA.2444@.tk2msftngp13.phx.gbl...
> Is there any possible way to bundle my reports with references to other
> controls to build a visual representation of data? I realize that this is
> all informational, but I've got a problem with the stock graphs, they look
> HORRIBLE.
> Anyway, thanks in advance
> Weston
>