Ik wil uit een tabel het gehele getal halen, de waarde staat als
05612123 maar wanneer ik dit
opvraag in bv. de Query Analyzer wordt de waarde gegeven als 5612123.
De 0 welke ervoor staat wordt
dus niet afgebeeld. De tabel in de kolom heeft als Data Type char, de
Length staat op 10 en Allow Nulls staat aangevinkt. Wie kan mij helpen?
Dank.
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
Hi,
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
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'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
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 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
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,