Hi, I'm fairly new to strored procedures. what I need to do is create a new row with an INSERT specifying value A as a param ( so far no problem ) and value B as a value from table2. Is this possible?
Thanks for your help
Raif
Hi, i need to create a stored procedure that will take data from one table and insert it into one tabe.
I have 3 tables
tblTest is the table that i need to take the informations from.
intD1 to intD4 is a score
I don't know if iexplain my sefl correctly tks in advance!
tblTest
1 , 2 , 'A', 'B', 0 , 1 ,2 , 4
I need this
tblResultat
1, 2, 'A','B'
tblResultatXReponse
1,1,0,0
2,1,1,1
3,1,2,2
4,1,4,3
tblTest
intTestId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2
intD1
intD2
intD3
intD4
tblResultat
intResultatsId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2
tblResultatXReponse
intResultatXReponseId autonumber
intResultatsId
intReponseDesc
intOrdre
Hi i am trying to create an insert statement that will insert rows into a table based on the information in the table already.
the table looks like this
Groupid field1 field2
-1 100 200
-1 100 300
-1 300 500
-1 300 600
-1 400 100
the insert looks like this
INSERT Into table1(groupid,field1,field2)
select -1,@passedvalue,field2
from table1
where field1 = @passedvalue1
assume @passedvalue = 700, @passwedvalue1 = 100
Now this is fine however i cannot have a duplicate key (key is comibantion of all 3 fields) thus the first time this runs it works however if it runs again it fails - how can i change the where clause to ignore rows that already exist?
eg if @passedvalue = 300 and passedvalue1 = 500
given a variable @requestID and @session ID, I need to move requests from
a holding table to the request table using the generated request ID.
In a perfect world...
INSERT INTO Requests(ReqID, field1, field2)
VALUES (SELECT @requestID AS RID, field1, field2
FROM holding WHERE session = '1234')
So that all 5 or so rows from the holding table having a session ID of 1234
get transfered to the request table using the variable value @requestid as
the value satisfying ReqID.
Any ideas?
hi all, lets say i have this insert command being executed from C# to a SQL Db. //store transaction log
SqlCommand cmdStoreTrans = new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@ImportID,@ProfileID,@RowID);",conn);
cmdStoreTrans.Parameters.Add("@ImportID",importId);
cmdStoreTrans.Parameters.Add("@ProfileID",profileId);
cmdStoreTrans.Parameters.Add("@RowID",i);
try
{
conn.Open();
cmdStoreTrans.ExecuteNonQuery();
conn.Close();
}
catch(SqlException ex)
{
throw(ex);
}I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @@Identity any help appreciatedTIA, mcm
Insert into Stored Proc
--------------------------------------------------------------------------------
I can't seem to get this stored proc to run properly. It won't let me insert records into it. Any suggestions? Can someone tell me if something doesn't look right? I've been over this a million times!
CREATE PROCEDURE TEST_PROC
/*
My Name
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE @MAVG2003 AS MONEY
SET @MAVG2003 = (SELECT AVG(PRICE) AS AVERAGE
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')
CREATE TABLE #FORECAST(
CUSTOMER_ID INT,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
TOTAL_2003 MONEY,
FORECAST_2004 MONEY,
FORECAST_2005 MONEY)
INSERT INTO #FORECAST
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,
0,
0
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003'
GROUP BY C.CUSTOMER_ID
UPDATE #FORECAST
SET FORECAST_2004 = TOTAL_2003 * (TOTAL_2003 / @MAVG2003)
UPDATE #FORECAST
SET FORECAST_2005 = FORECAST_2004 + (FORECAST_2004 * .0105)
SELECT CUSTOMER_ID, TOTAL_2003, FORECAST_2004, FORECAST_2005
FROM #FORECAST
ORDER BY TOTAL_2003 DESC
DROP TABLE #FORECAST
RETURN 0
END
GO
Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.
Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".
The common field in both SELECT and INSERT is phonenumber.
quote:PROCEDURE [dbo].[usp_select_and_insert]
@name varchar(20),
AS
SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name
INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');
GO
Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. :)
Hello,
Please consider the following:
CREATE procedure [dbo].[jason_test]
with execute as 'bulk_insert_test_jcb'
as
exec('bulk insert SCORPIO_STAGE_BULK_DATAPDCC from ''\shodbs29CDRDataonmech_stat_apd_clark_credit.dat'' with (formatfile = ''\dixdbs01ScorpioBulkDATAPDCC.fmt'')')
This is a stored proc with execute as a SQL user. It runs one bulk insert. The user bulk_insert_test_jcb does have BulkAdmin rights and if the user is logged in directly to the server, this works fine. If a SQL user is logged in and runs it (a user other than bulk_insert_test_jcb), this also works
However, if I run this as a windows user logged into the server
alter database stage_scorpio_bulk_jcb set trustworthy off
exec jason_test
--Msg 4834, Level 16, State 4, Procedure jason_test, Line 4
--You do not have permission to use the bulk load statement.
I expect this because the server-level permissions (bulk) are stripped off unless the database is trustworthy, so...
alter database stage_scorpio_bulk_jcb set trustworthy on
exec jason_test
--Msg 4861, Level 16, State 1, Procedure jason_test, Line 4
--Cannot bulk load because the file "\shodbs29CDRDataonmech_stat_apd_clark_credit.dat" could not be opened. Operating system error code 5(Access is denied.).
Why does this happen? I thought that, since I'm executing as a SQL user, SQL Server would authenticate over to the server with the datafiles as the service account, but I see the following in the log at SHODBS29
--User Logoff:
-- User Name: ANONYMOUS LOGON
-- Domain: NT AUTHORITY
-- Logon ID: (0x0,0x4C99BD2F)
-- Logon Type: 3
--
--
--For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Any ideas? It seems as if it is still trying to used windows authentication even though the stored proc is supposed to execute as a SQL user.
Someone in another forum said that ownership chaining not being allowed for bulk operations was the problem, but I don't think so, since if I put an "execute as user='bulk_insert_test_jcb'" into the exec string, it still fails with the same issue.
Thanks!
Jason
Hi,
How would I check if an insert via stored proc succeeded? Here's the proc I'm using:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[showtube_addNewSUser]
-- Add the parameters for the stored procedure here
@UserId uniqueidentifier,
@FirstName nvarchar(32),
@LastName nvarchar(32),
@DescShort nvarchar(256),
@DescLong ntext,
@ClanID uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Insert into dbo.Users (UserId, FirstName, LastName, DescShort, DescLong, ClanID)
values (@UserId, @FirstName, @LastName, @DescShort, @DescLong, @ClanID);
END
I tried adding a Return @@RowCount before the END statement, but it always seems to return -1. Could someone tell me what I'm doing wrong? Thanks.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedI have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedI have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
How can I get the script of a table.i.e 'CREATE TABLE' script.
1. One way is to get this script from SQL Analyzer.
but I m having tables created in the database and I want the My Stored Procedure should build the script and return it to me ...
How could I achieve that ?
Thankx in advance... :)
I link to a sql server for the first time.
I change my .provider and .connectionstring to ADO
And know in one of my forms i get a debug Error; Invalid table Name "tablename"
can someone help me with this.
Thanks eddied
Hi All,
When an end user view a report using report manager or a custom build web site, would query of the report scan the tables in original data base? In this case it is a OLTP data base.
Thanks
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedHi,
I cannot figure out how to print the design of a tabel.
What I need is to have printed on a paper the list of the columns, data type, length, allow nulls and description for a table.
thx
Ik wil uit een tabel het gehele getal halen, de waarde staat als05612123 maar wanneer ik ditopvraag in bv. de Query Analyzer wordt de waarde gegeven als 5612123.De 0 welke ervoor staat wordtdus niet afgebeeld. De tabel in de kolom heeft als Data Type char, deLength staat op 10 en Allow Nulls staat aangevinkt. Wie kan mij helpen?Dank.
View 3 Replies View RelatedHello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hi
Running on SQL Server2005
I have an tabel Match, TeamInGroup, Team, Group and Series.
Want following resutlt:
Tabell
Lag S V O F TOTAL P
Grupp A
BK Örnen 2 2 0 0 30-10 4
Wåxnäs BC 3 2 0 1 35-25 4
BK Kaskad 2 1 0 1 20-20 2
BK Bågen 3 1 0 2 29-31 2
BK Glam 3 1 0 2 24-36 2
IFK Norrköping BF 3 1 0 2 22-38 2
Grupp B
Uppsala BC 90 3 2 1 0 35-25 5
Ludvika BK 2 2 0 0 25-14 4
Sundbybergs IK F 3 1 1 1 34-26 3
LBK Hudik 3 1 0 2 23-36 2
Domnarvets BS 2 0 1 1 19-21 1
Örta IF 3 0 1 2 23-37 1
Grupp C
Stureby BK 3 2 0 1 39-21 4
Tureberg IF 2 2 0 0 28-12 4
BK Stallis 3 2 0 1 28-31 4
BK Amiki 3 1 0 2 28-31 2
Djurgårdens IF 2 1 0 1 15-25 2
BK Brio 3 0 0 3 21-39 0
Tabel Match:
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Match] Script Date: 09/19/2007 17:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Match](
[MatchId] [int] NOT NULL,
[Matchstart] [datetime] NULL,
[LagIdHemma] [int] NOT NULL,
[LagIdBorta] [int] NOT NULL,
[Resultathemma] [int] NULL,
[ResultatBorta] [int] NULL,
CONSTRAINT [PK_Match_1] PRIMARY KEY CLUSTERED
(
[MatchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_Team] FOREIGN KEY([LagIdHemma])
REFERENCES [dbo].[Team] ([TeamId])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_Team]
GO
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_TeamInGroup] FOREIGN KEY([LagIdHemma])
REFERENCES [dbo].[TeamInGroup] ([TeamId])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_TeamInGroup]
Tabel Team:
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Team] Script Date: 09/19/2007 17:28:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Team](
[TeamId] [int] NOT NULL,
[Name] [varchar](20) NOT NULL,
[GroupId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Tabel TeamInGroup:
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[TeamInGroup] Script Date: 09/19/2007 17:27:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TeamInGroup](
[TeamId] [int] NOT NULL,
[GroupID] [int] NOT NULL,
CONSTRAINT [PK_TeamInGroup_1] PRIMARY KEY CLUSTERED
(
[TeamId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TeamInGroup] WITH CHECK ADD CONSTRAINT [FK_TeamInGroup_Grupp] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Grupp] ([GroupId])
GO
ALTER TABLE [dbo].[TeamInGroup] CHECK CONSTRAINT [FK_TeamInGroup_Grupp]
Tabel Group:
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Grupp] Script Date: 09/19/2007 17:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Grupp](
[GroupId] [int] NOT NULL,
[SeriesId] [int] NOT NULL,
[Namn] [varchar](50) NULL,
CONSTRAINT [PK_Grupp_1] PRIMARY KEY CLUSTERED
(
[GroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Grupp] WITH CHECK ADD CONSTRAINT [FK_Grupp_Serier] FOREIGN KEY([SeriesId])
REFERENCES [dbo].[Serier] ([SeriesId])
GO
ALTER TABLE [dbo].[Grupp] CHECK CONSTRAINT [FK_Grupp_Serier]
Tabel Series
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Serier] Script Date: 09/19/2007 17:30:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Serier](
[SeriesId] [int] NOT NULL,
CONSTRAINT [PK_Serier] PRIMARY KEY CLUSTERED
(
[SeriesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This is what i dot so far:
SELECT tg.GroupID, t.Name, COUNT(m.ResultatHemma) AS S,
CASE
WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS V,
CASE
WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS O,
CASE
WHEN m.ResultatHemma - m.ResultatBorta < 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS F,
SUM(m.ResultatHemma) AS Hemma, SUM(m.ResultatBorta) AS Borta,
SUM(m.ResultatHemma - m.ResultatBorta) AS Diff,
CASE
WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN 1
ELSE
CASE
WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN 2
ELSE 0
END END AS P
FROM Match AS m INNER JOIN
Team AS t ON m.LagIdHemma = t.TeamId INNER JOIN
TeamInGroup AS tg ON m.LagIdHemma = tg.TeamId
WHERE (m.MatchId = m.MatchId) AND (tg.GroupID = 2 OR tg.GroupID = 1 OR tg.GroupID = 3)
GROUP BY t.Name, tg.GroupID, m.Resultathemma, m.Resultatborta
ORDER BY P DESC, Diff DESC, S DESC
I can't figure out howto group by t.Name and team in Group A,B, and C
Here are some data From Match:
MatchId Matchstart LagIdHemma LagIdBorta Resultathemma ResultatBorta
----------- ----------------------- ----------- ----------- ------------- -------------
700201001 2007-09-08 10:00:00.000 33768 159120 16 4
700201002 2007-09-08 15:00:00.000 33744 159120 16 6
700201003 2007-09-08 11:00:00.000 33894 33407 16 4
700201004 2007-09-08 14:00:00.000 33874 33407 11 9
700201005 2007-09-08 11:00:00.000 33737 32186 10 10
700201006 2007-09-08 16:20:00.000 30896 32186 15 5
700201007 2007-09-08 11:00:00.000 33286 42031 11 9
700201008 2007-09-08 15:00:00.000 33290 42031 14 5
700201009 2007-09-08 11:00:00.000 33628 33722 13 7
700201010 2007-09-08 15:00:00.000 33684 33722 15 5
700201011 2007-09-08 11:00:00.000 43635 33705 9 11
700201012 2007-09-08 15:00:00.000 31346 33705 16 4
700201013 2007-09-15 11:00:00.000 159120 33768 8 12
700201014 2007-09-15 16:00:00.000 33407 33768 11 9
700201015 2007-09-15 11:00:00.000 33874 33744 9 11
700201016 2007-09-15 13:00:00.000 33894 33744 14 6
700201017 2007-09-15 10:00:00.000 32186 33737 12 8
700201018 2007-09-15 16:00:00.000 42031 33737 13 7
700201019 2007-09-15 11:00:00.000 33290 30896 9 11
700201020 2007-09-15 15:40:00.000 33286 30896 10 10
700201021 2007-09-15 11:00:00.000 33722 33628 9 10
700201022 2007-09-15 16:00:00.000 33705 33628 12 8
700201023 2007-09-15 11:00:00.000 31346 33684 8 12
700201024 2007-09-15 15:00:00.000 43635 33684 16 4
Team:
TeamId Name GroupId
----------- -------------------- -----------
30896 Sundbybergs IK F 2
31346 Turebergs IF 3
32186 Örta IF 2
33286 Domnarvets BS 2
33290 Ludvika BK 2
33407 BK Glam 1
33628 BK Amiki 3
33684 Stureby BK 3
33705 BK Brio 3
33722 BK Stallis 3
33737 Uppsala BC90 2
33744 BK Bågen 1
33768 Wåxnäs BC 1
33874 BK Kaskad 1
33894 BK Örnen 1
42031 LBK Hudik 2
43635 Djurgårdens IF 3
159120 IFK Norrköping 1
(18 row(s) affected)
TeamInGroup:
TeamId GroupID
----------- -----------
30896 2
31346 3
32186 2
33286 2
33290 2
33407 1
33628 3
33684 3
33705 3
33722 3
33737 2
33744 1
33768 1
33874 1
33894 1
42031 2
43635 3
159120 1
(18 row(s) affected)
Group:
GroupId SeriesId Namn
----------- ----------- --------------------------------------------------
1 48967 Grupp A
2 48967 Grupp B
3 48967 Grupp C
(3 row(s) affected)
And Serier:
SeriesId
-----------
48967
(1 row(s) affected)
Best regards
Gerten
I'm making a system to data acquisition (production) and need more (3) tabels with a heavy load of data.
3 tabels will contain approximately 20-50 milion rows. Every day I need to add 80000 rows to these tabels. Will I get problems with system-performance with such a configuration? Ofcourse the system will contain index in the database and 2 fast 1 Ghz intel CPU. I isn't possible for me to seperate or archive theese tabel, because of researc-functionality.
Michael
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.
I created a test in MS Access and it loooks like this:
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
FROM Country
This works great in Access but not in SQL Server. In SQL Server 2 = @PatientTripID
ANY SUGGESTIONS ON HOW TO HANDLE THIS?
Hi
I'm running transactional repl with updateable subscribtions - queued updating. Running SQL 2005 sp2, win 2003 sp1
I have 18 odd subscribers at the moment, my publisher and disttribution is on the same machine with push subscriptions.
The questions I have
nr 1.
While trying to initialize new subscribers I get loads of deadlocks even after I stop dist cleanup agent. This *I think* cause some other unexpected problems.
nr2.
The queue reader would fail saing it cannot find the "insert" proc on the publisher, although it exists.
I have changed anything on the publication so I'm not sure how this happens or why.
nr3.
I replicate a varbinary(max) column and on the odd occasion get the "Length of LOB data" errors which I then set with sp_configure. The catch here is that the length never exceeds a "len()" of 4000, thus the reported LOB and my calculation doesn't tie up.
Help is appreciated.
Thanks
I have flat file (comma delimited) with 200 columns, and i want to import this to sql table using SSIS package, I create a Flat file source and sql server destination items from tool box. in destination item, i can not see the preview, for some reason, it is not reading the rows from source, Do I have to manually each and every column from source to destination under mapping tabl in destination item.
SQL 2000 DTS never required such thing.
any help?
Thanks,
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr
From Results, PledgeInLastYear
Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear
Insert Into Results(PledgeLastYr)
Values ('Y')
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter.
-----------------------------
declare @stringSQL varchar(200)
select @stringSQL=
'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0'
print @stringSQL_GetUserName
exec (@stringSQL_GetUserName)
select * from temp1
------------------------------
above dynamic query works fine.
Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server
MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang...
Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
thanks
David
When you create a Stored procedure, is it automatically stored under the default Filegoup?
How can I see under which Filegroup my Stored Procedures and Triggers are stored?