CREATE TABLE [dbo].[bob1](
[cola] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[bob1] WITH CHECK ADD CONSTRAINT [CK_bob1] CHECK (([cola] > 999 and [cola] < 2001))
ALTER TABLE [dbo].[bob1] CHECK CONSTRAINT [CK_bob1]
This will only allow values between 1000 and 2000, otherwise you get an error
That is the CREATE SCRIPT generated by Management Studio. I'm sorry, I didn't realise you wanted me to do ALL your work. Have you heard of Google, or perhaps Books Online, or even the HELP in Management Studio?
I have three table A, B and C. Table A contain list of books borrow everyday. Table B contain the list of Books available in library. Table C suppose to have list of book not borrowed per day. I need a sample code to do this. I am using MS Access as my Backend database.
My Code is as follows:
"Insert into TableC (BookID,[Name], Author) (Select * From Table B where BookID) Not in (Select * from Table C where [Date] between ' " & TextBox1.Text & "' and ' " & TextBox2.Text & "'"
I want to be able to get those books available in the School Library (Table A) and not yet borrowed (All the books borrowed entered into Table B) and copy the information to Table C every day. My query is given me "Syntax error in Insert Statement". I don't know how to go about this automatically.
NB: Table A,B and C have the following field in common 'BookID' 'Name' 'Author'
It's a bit difficult to say what's the solution without seeing the table definitions. However, if I'm reading correctly, your problem is in syntax (I take it this gives an error). Perhaps something like following:
Insert into TableC (BookID,[Name], Author)
(Select BookID,[Name], Author
where BookID Not in (Select bookID
where [Date] between '" & TextBox1.Text & "' and '" & TextBox2.Text & "'" ))
Also instead of using text concatenation for parameters, it's better to use bind variables (for example to prevent sql injection).
How can I execute a function and then a stored procedure? The function gets the latest recordID and the the insert statement should execute and insert the recordID and value. My code looks like this:
CREATE PROCEDURE dbo.InsertCountry
DECLARE @LastID = BIGINT
SET @LastID = (SELECT MAX(Country_ID) FROM Countries
IF @LastID IS NULL SET @LastID = '1'
ELSE SET @LastID = (@LastID) + 1
INSERT INTO Countries
The above code does not compile but when I take the first Begin and End block and put that in a function it works. I try to execute the function by using EXEC GetLastID but that does not work.
Here is my delema. A company row can have multiple representations in the security table, based on the priusa or prican values in the company table matching the iid values int he security table, or the values in company table being null, in which case i just want to match the cue of the first iid listed for the key in the security table.
The If section works if I change out the copmany.key references for a literal value, so as far as i can tell its having an issue with the assignment of the value to security.iid = (IF...).
SELECT company.key, company.name, security.cue
FROM company LEFT OUTER JOIN
security ON company.key= security.key AND security.iid = (IF (select priusa FROM company WHERE key = company.key) is NOT NULL
select priusa FROM company WHERE key= company.key
IF (select prican FROM company WHERE key= company.key) is NOT NULL
select prican FROM company WHERE key= company.key
select TOP 1 iid FROM security WHERE key= company.key
ORDER BY security.iid
WHERE (company.name like @name)
The reason is that you cannot have correlation in inline view when used in from section.
Perhaps something like this would be a simpler approach:
WHERE company.key = security.key
AND security.iid = ISNULL(company.priusa,
(select MAX(iid) FROM security WHERE key= company.key))))
WHERE (company.name like @name)