|
please, I want simple table in SQL to training and query, and thank you.
|
|
|
|
|
CREATE TABLE [dbo].[Customer](<br />
[CustomerID] [nvarchar](20) NOT NULL,<br />
[Description] [nvarchar](90) NULL,<br />
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED <br />
(<br />
[CustomerID] ASC<br />
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />
) ON [PRIMARY]<br />
<br />
CREATE TABLE [dbo].[Users](<br />
[User_Code] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,<br />
[First_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
[Last_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED <br />
(<br />
[User_Code] ASC<br />
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />
) ON [PRIMARY]<br />
<br />
CREATE TABLE [dbo].[QuoteHistory](<br />
[ID] [int] IDENTITY(1,1) NOT NULL,<br />
[Qdate] [smalldatetime] NULL,<br />
[RequestorID] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
[CustomerID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
[ProductCode] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
CONSTRAINT [PK_QuoteHistory] PRIMARY KEY CLUSTERED <br />
(<br />
[ID] ASC<br />
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />
) ON [PRIMARY]
|
|
|
|
|
Huh? What is it you are trying to do? Learn how to make a simple table? If so, there's plenty of good resources ( google, books, etc )...
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
If you need a sample database, you can use for example Northwind database provided by Microsoft. Found here: Sample Databases[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
As we know that "Set NoCount ON;" increases the performance in SQL server queries by avoiding any extramessages being sent to client...
We are using this statement invarialbly in all procedures and I have seen it being part of SQL Server default procedure template...
What I couldnt figure out is if we need to use this with all the statements why is it OFF by default???
What are the cases where we shouldn't be using it???
~AHAGeek
|
|
|
|
|
A _sp that returns a result set and you want the count (often for queries and reporting purposes).
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
In SP if we want count..we can still use @@RowCount with this statement on???
~AHAGeek
|
|
|
|
|
Typically I would use @@ROWCOUNT in a stored procedure where there are several transactions that are going to modify records. You can then be certain that every update, delete, insert occurs as expected; esp. in stored procedures where it's all or nothing. If 1 table doesn't get modified as expected, you can then rollback.
IE: You have a SP (with NOCOUNT = ON) that has several table updates for updating general ledger entries for A/R and then write the changes into a history table. If one update fails (by checking @@ROWCOUNT), the entire transaction gets rolled.
Returning a rowcount (meaning NOCOUNT = OFF) is good for a very simple sps that return a result set and the row count can be displayed for information purposes and ensure the query didn't error off.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
I suggest the default is bc it is useful when coding a proc, it is only when you go to production that you want to turn it off.
It would be nice to be able to turn it off as a database/server option.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, I searched the boards and found how to do this, but I have come up against a problem that I cannot seem to resolve.
This is the code to create the stored procedure: (It was create first but I have been playing with it)
ALTER PROCEDURE [dbo].[sproc_CreateMarriageTable]
-- Add the parameters for the stored procedure here
@Surname varchar(50)
AS
BEGIN
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''),
[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY])
ON [PRIMARY]'
EXEC @SQLStatement
END
This parses and compiles without any errors. Then I tried to execute the procedure:
USE [BMD2005]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sproc_CreateMarriageTable]
@Surname = N'M_VAISEY'
SELECT 'Return Value' = @return_value
GO
It fails with the message:
Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTable, Line 29
The name 'CREATE TABLE [M_VAISEY]
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_FName] DEFAULT (''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_SName] DEFAULT (''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_TName] DEFAULT (''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAISEY_MF] DEFAULT ('M'),
[Year] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_PerMar] DEFAULT (''),
[BirthDate] [smalldatetime] ' is not a valid identifier.
The declaration of the statement variable is plenty long enough (The text is about 1200 characters) and the place where it stops is about 687 characters.
Is there a problem with NULL? Do I need to enclose that in something?
If anyone can see what the problem is I would be most grateful. 
David
UPDATE: I removed a couple of the filds from the end and got this message when I executed
Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTableTest, Line 25
The name 'CREATE TABLE [M_VAUGHAN]
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_FName] DEFAULT (''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_SName] DEFAULT (''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_TName] DEFAULT (''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAUGHAN_MF] DEFAULT ('M'),
[Year] [smallint] NULL CONSTRAINT [DF_M_VAUGHAN_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_M_VAUGHAN_Qtr] DEFAULT ((1)),
[BirthDate] [smalldatetime] NULL
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTI' is not a valid identifier.
It would seem there is some lenght of string problem. Any ideas?
modified on Friday, October 17, 2008 11:07 AM
|
|
|
|
|
DECLARE @SQLStatement varchar(1024)
DECLARE @SQLStatement2 varchar(1024)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''), '
SET @SQLStatement2 = '[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY])
ON [PRIMARY]'
EXEC (@SQLStatement + @SQLStatement2)
|
|
|
|
|
Hi,
It looks like the length is the issue. I guess you might have some tab characters in the code for formatting perhaps, not sure. Anyway, I just fixed a similar problem in a Sybase IQ procedure and just removed all the white space.
So for example...
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname
+ '] ([AutoID] [int] IDENTITY(1,1) NOT NULL,'
+ '[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_'
+ @Surname
+ '_FName] DEFAULT (''''), '
+ '[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname
+ '_SName] DEFAULT (''''), '
+ '[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname
... etc
... etc
... etc
+ ' STATISTICS_NORECOMPUTE = OFF, '
+ ' IGNORE_DUP_KEY = OFF, '
+ ' ALLOW_ROW_LOCKS = ON, '
+ ' ALLOW_PAGE_LOCKS = ON)'
+ ' ON [PRIMARY]) '
+ ' ON [PRIMARY]'
EXEC @SQLStatement
I hope that helps.
Good luck  ,
Kevin
|
|
|
|
|
Took me a second or two to work out what you were saying. *david scratches head* Ah! The light dawned - concatenation
Yes of course the wrapping or rather formatting may be adding spurious characters.
Strange thing is that it worked when I split the line into two. Perhaps I just hit the right spot by luck rather than by judgement! 
I will give it a try.
Thanks
David
|
|
|
|
|
Worked a treat. Thanks again.
|
|
|
|
|
I'm missing the 'Open Table' right click menu item in SSMS 2008. I am running SQL Compact 3.5 sp1. Any ideas on how to get the menu option back.
It's getting extremely annoying having to view, modify, insert/delete by using queries.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
I was hoping someone had a fix for this. So annoying. Only with compact DBs.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
Hi all.., How to find refresh percentage of a database through QUERY?
D.Willington
|
|
|
|
|
What do you mean exactly? Your question makes no sense.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i have an application to refresh a database. I have to select database, backup file path and all.., it is working well.., while the database in refresh i have to display this much percentage has fininshed and so on.
so i want to get the completed percentage of refresh database.
D.Willington
|
|
|
|
|
Well, while your database is restoring you can't run queries so you'll just have to show them a progress bar or something that runs on a timer.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
can't run the query from master database?
D.Willington
|
|
|
|
|
You can run a query from the master database, but the database you are restoring is being restored, so there is nothing to query - it doesn't do it table by table etc.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ok.., Thank u very much
D.Willington
|
|
|
|
|
Hi,
I have a table (tblregfundprice) with the column name "Unit_Price, Fund_Code, and Date".
The table contains the following data.
Fund_Code.....Unit_Price.....Date
AF............0.1254.........10/17/2008
AF............0.5487.........10/16/2008
AF............0.2114.........10/15/2008
AF............0.8744.........10/14/2008
AF............0.1254.........10/13/2008
AF............0.5487.........10/12/2008
AF............0.2114.........10/11/2008
AF............0.8744.........10/10/2008
AF............0.1254.........10/09/2008
SC............0.5487.........10/14/2008
SC............0.2114.........10/13/2008
SC............0.8744.........10/12/2008
GH............0.1254.........10/11/2008
GH............0.5487.........10/10/2008
GH............0.2114.........10/09/2008
GH............0.8744.........10/08/2008
Now if you notice, the most recent date is 10/17/2008 and SC is having the most date of 10/14/2008. How can i do to create below result table for TOP 10 Fund_Code = "SC"?
Unit_Price.....Date
NULL..........10/17/2008
NULL..........10/16/2008
NULL..........10/15/2008
0.5487.......10/14/2008
0.2114.......10/13/2008
0.8744.......10/12/2008
0.1254.......10/11/2008
NULL..........10/10/2008
NULL..........10/09/2008
NULL..........10/08/2008
I need the NULL value to appear as i need it for my system to capture it... I hope there's a way for this.
Pls help....Many thx.
|
|
|
|
|
try this
SELECT *<br />
FROM (SELECT DISTINCT Date FROM TABLE) D<br />
LEFT JOIN (SELECT * FROM TABLE WHERE Fund = 'SC') SC ON SC.Date = D.Date
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
