|
I have the following tables.
Table SS
SID SNAME
== =====
1 AAA
2 BBB
3 CCC
Table PP
PID PNAME
== =====
1 Phase1
2 Phase2
3 Phase3
Third table (COMP) data may look like the following.
ID SID PID
== === ===
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
ID here in third table is autoincrement. I need to insert a new record in SS table and it can have mapping to many Phases in PP table.
So I want to write a
INSERT INTO COMP (col1,col2)
SELECT (col1,col2)
FROM table...
So ultimately new records needs to be inserted into the last table with a SID map and different PID.
How can I accomplish the same?
Thanks in advance.
CodingRocks
ASP.NET,C#.NET Programmer
BANGALORE
"Winners don't do different things. They do things differently. ...
|
|
|
|
|
Assuming you have a list of PIDs you want inserted you require 3 statemants,
<pre lang="SQL">Declare @NewID int
Insert SS (fields).Values
Set @NewID = scope_identity()
insert Comp(SID,PID)
Select @NewID, PID from listofpids</pre>
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
EDIT: I Guess this makes my problem clearer
i have 2 tables
Mother with Columns (Name,NameKey)
and Children with Columns(child,childkey,Mother.NameKey)
i want to connect 2 tables
and insert many children in 1 mother
is this code possible? can't try it right now
insert into children(mother.namekey,child)<br />
values<br />
(Select namekey from mother where name='TELMA'),BEN,RON,DONNA)<br />
i know my codes above have errors even w/o trying it.
i just want to know how to make that happen and get this output
MOTHER- CHILDREN
TELMA..............BEN
............................RON
...........................Donna
modified 24-May-13 22:59pm.
|
|
|
|
|
Have you tried (have assumed that this is SQL Server)
insert into family (children) values
select 'ben'
union
select 'ron'
union
select 'donna'
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Al Arcilla wrote: i have a table family
columns (MOTHER,CHILDREN)
How did you create it? Can you post the CREATE TABLE statement?
Al Arcilla wrote: how can i have an output like that?
Is the problem in getting the "output" based on the data, or getting the data in the table?
Al Arcilla wrote: this is the code i try i know this will not work
What does "not work" mean? Did it execute succesfull or throw an exception? If there was an error, which one? Can you post it?
I'm going to guess that your "mother" column isn't a primary key, nor an identity. In that case, it'll need a value when you do the insert;
INSERT INTO [family] (MOTHER,CHILDREN)
VALUES ("Mother", "Ben");
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
Hi
how can Implementation
disributed database (in one computer(at sql 2008)/two server)؟?
Thanks
m.shamshirgaran@stu.semnaniau.ac.ir
|
|
|
|
|
2 things, do NOT put your email in a forum message, edit it out ASAP. If someone needs to get to you directly they can use the email option at the bottom of the posting.
Define what you mean by <code>distributed</code> you can install 2 instances of SQL server on the same machine this could simulate a distributed environment but you need to be more explicit.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have this SQL Server 2012 trivial stored procedure:
CREATE PROCEDURE usp_SelectCompany
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM Company
END
and in a SSMS window, I execute this commands below to discover my SP is much slower than directly executing SELECT * from Company, even after the SP is run once to compile it.
SELECT * INTO #Company FROM Company WHERE 1=0
INSERT #Company EXEC usp_SelectCompany
PRINT 'Start Time: ' + cast(sysdatetime() as varchar)
INSERT #Company EXEC usp_SelectCompany
PRINT 'SP Time: ' + cast(sysdatetime() as varchar)
INSERT #Company SELECT * FROM Company
PRINT 'Select Time: ' + cast(sysdatetime() as varchar)
Here is the output:
(0 row(s) affected)
(127733 row(s) affected)
Start Time: 2013-05-22 11:56:57.7208741
(127733 row(s) affected)
SP Time: 2013-05-22 11:57:03.2067327
(127733 row(s) affected)
Select Time: 2013-05-22 11:57:04.0015524
I note that the SP executes in 5.4858586 seconds, and the direct "select" statement only takes 0.7948197 seconds, or about 7 times slower.
Can anyone explain this behavior?
|
|
|
|
|
Saxet wrote: Can anyone explain this behavior?
I can't, but a wild guess would be the optimizer (select and insert vs. insert and exec some proc).
What's the timing when you move the insert-statement inside the sproc?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
It's better (1.0295934 seconds), but still slower than the select in the SSMS window.
Start Time: 2013-05-22 12:51:03.6580584
End Time: 2013-05-22 12:51:04.6876518
|
|
|
|
|
Saxet wrote: It's better (1.0295934 seconds), but still slower than the select in the SSMS window.
The server has to do some things that can be skipped when being fed the statement directly, like looking up the access-rights to the sproc. So yes, compared to a simple query, it'd be a (small) bit slower.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm not entirely surprised, but the difference is probably not accurate. What you might try is simply using ExecuteReader from code on each and see how long it takes to do -- without reading the data.
I also wonder whether it's a local database or remote.
|
|
|
|
|
ExecuteReader performs about the same with either a select statement or a stored procedure. It appears to me that there is some penalty (lack of optimization?) when inserting the output from a stored procedure to a table that isn't present when inserting the output of a select statement.
SSMS produces a different "Actual Execution Plan" for the two, but I'm not skilled enough to understand what the differences mean. The execution plan breaks up the stored procedure into two queries, where the select statement is just one.
|
|
|
|
|
Right. About what I'd expect.
|
|
|
|
|
SQL Server can cache execution plans and results.
|
|
|
|
|
Hi,
I am using VS 2012 Free Edition (et al)
I am contemplating using this for the next version of 25 year old successfull software. We need to update various tables in a single transaction. This transaction needs to proceed in a step in which either All succeed, or None succeed. (resulting in a roll-back if any step failed) The database must deal with a high likelyhood that several terminals will at the same time try to write to the same entry in the same table. I handled this in MFC, with my own DB by giving each contentious item a serial-number, The first check for a modify-save would be if the serial number was still the same. if not, the item would be refused, and the transaction rolled back.
How does SQL handle this.
N.B.
The word 'Serial Number' seems to link to some add somewhere. Nothing to do with me! Just, Don't click it on my behalf.
Thankfully that link has disappeared since. TAKE NOTE IF IT HAPPENS TO YOU. The term 'Serial Number' turned into a link to some mobile phone sellers. Its gone now.
Bram van Kampen
|
|
|
|
|
Maybe I don't fully understand the problem, but SQL Server will handle "transactions" the same way. You declare the start of a transaction "Begin Transaction", do your stuff, then either "Commit" or "Rollback" the activity.
SQL server can automatically generate "serial" numbers for you. Look into the column data-type known as "Identity Column".
Did that answer your question?
|
|
|
|
|
Thanks,
I've since learned about the existence of 'Begin-End Transaction' Can you give me a link to a page explaining what it does and how it works?
Regards,#
Bram van Kampen
|
|
|
|
|
|
I have created a stored procedure which lists all customers that have 7 days left on their membership.
----------
create proc spGetMemReminder
as
select users.fullname, membership.expiryDate from membership
inner join users on membership.uid=users.uid
where convert(varchar(10), expiryDate,105) = convert(varchar(10), (getdate() +7), 105)
-------
I would like to insert this list into another table automatically. How do I achieve this? any suggestions appreciated. Thanks
|
|
|
|
|
|
Just use an insert:
CREATE PROCEDURE spGetMemReminder
AS
INSERT INTO YourTableNameHere (FullName, ExpiryDate)
SELECT users.fullname, membership.expiryDate from membership
inner join users on membership.uid=users.uid
where expiryDate = CAST(DATEADD(day, 7, getdate()) AS DATE
if expiryDate is a date column it is better to do date compare than to convert both sides.
|
|
|
|
|
|
Use an insert statement, that is not why I am posting a reply.
There are many ways to compare dates, comparing string (varchar) is probably the worst possible method. Do some research into the datetime object, you could have used datediff or dateadd neither of which require a convert.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
