How do I actually start the product and create tables and data?
Thanks,
Fred
You can't. It only really runs as part of your code (or someone else's).
If you want to create tables, etc. you're probably going to want to use SQL Server Management Studio, the express version is free.
quote:|||Russell - Thank you for your reply.
> I have a service that writes a lot of records to many
> tables in SQL server. Recently, the service failed in
> writing record to any of the tables. But, the retrieving
> of the records is still OK. By examining the application
> log file, it seems that the SQL server started to
> deteriorate by not allowing insertion in one table at a
> time and eventually all tables are rejected for insertion.
> I ended up re-booting the system and everything went back
> to normal. After the reboot, I checked the SQL log file
> and noticed that about 18,000 records were rolled forward
> during SQL Server start-up.
> Does anybody have any idea as why the insertion would
> fail? And why the checkpoint was not executed even there
> are about 18,000 committed transactions?
>
quote:
>--Original Message--
>Mike,
>Perhaps the database space filled up as one table
quote:
>Tables that still had some space left in them could take
quote:
>then they were filled up as well and would no longer take
quote:
>Etc. Etc.
>If this is the case, either allocate more space or turn
quote:
>database in question.
>With regard to your closing comment, checkpoint is not
quote:
>Without knowing the period covered by the 18,000 records
quote:
>about your server it is not possible to offer details.
quote:|||Mike,
>think that checkpointing was working just fine.
>Russell Fields
>"mike" <mike@.ftsl.com> wrote in message
>news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...
insertion.[QUOTE]
back[QUOTE]
forward[QUOTE]
>
>.
>
Hi
I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.
Code Snippet
SET NOCOUNT ON
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
END
FROM Members M
INNER JOIN _MemberProfileLookupValues ML
ON M.MemberID = ML.MemberID
WHERE M.Active = 1
AND OptionID <> 6
When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID
I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately
ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.
Any ideas / help will be greatly appreciated. Please help.
Kind Regards
Carel Greaves
The following query insert the new members who option id 6 is not exist in the MemberProfileLookupValues table,
Code Snippet
SET NOCOUNT ON
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT
M.MemberID
, '6'
, CASE M.MaritalStatusID WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
END
FROM
Members M
Where
NOT EXISTS
(
Select 1 From _MemberProfileLookupValues ML
Where M.MemberID = ML.MemberID And OptionID = 6
)
And M.Active = 1
|||
Thanks a lot.
That is exactly what i was looking for.
Kind Regards
Carel Greaves
I have had a request, one of our tables is used as a report and I have been
asked that all fieldnames for months have dashes in them,
like Jan-05 instead of Jan05 and so on...
Now what we have is a CURSOR which loops through all values in another table
generating these fieldnames, like 'Jan-05', Feb-05' etc..
Then the table definition is modified so these months display as fieldnames.
That is fine,
Except later it calls another stored procedure which inserts values from
another table into this one.
INSERT INTO TableName
SELECT *
FROM Tablename
Obviously this is failing because the newly amended table now has dashes in
the fieldnames now.
Now as the fieldnames are created dynamically I cannot simply do INSERT
Tablename ([Jan-05], [feb-5]) etc, as one would normally do,
How could I do this bearing in mind its not always going to be 12 months we
use,
Basically I am asking how can I modify this INSERT statement so it takes
into account the fieldnames with dashes in them
Bearing in mind I don't know what the fieldnames are called as they are
created dynamically by a CURSOR in a different routine.
And its not always 12 months.
have you any ideas?
and the dashes are required
JayneDynamically built field names is a bit unusual. But you will most likely
have to dynamically build the insert command and then execute it. Use the
INFORMATION_SCHEMA.COLUMNS view to define a cursor of column names you need.
Ray
"Little PussyCat" <SPAMSPAM@.NOSPAM.com> wrote in message
news:5s5ug2-nc1.ln1@.tiger.sphynx...
> Hello,
> I have had a request, one of our tables is used as a report and I have
> been
> asked that all fieldnames for months have dashes in them,
> like Jan-05 instead of Jan05 and so on...
> Now what we have is a CURSOR which loops through all values in another
> table
> generating these fieldnames, like 'Jan-05', Feb-05' etc..
> Then the table definition is modified so these months display as
> fieldnames.
> That is fine,
> Except later it calls another stored procedure which inserts values from
> another table into this one.
> INSERT INTO TableName
> SELECT *
> FROM Tablename
> Obviously this is failing because the newly amended table now has dashes
> in
> the fieldnames now.
> Now as the fieldnames are created dynamically I cannot simply do INSERT
> Tablename ([Jan-05], [feb-5]) etc, as one would normally do,
> How could I do this bearing in mind its not always going to be 12 months
> we
> use,
> Basically I am asking how can I modify this INSERT statement so it takes
> into account the fieldnames with dashes in them
> Bearing in mind I don't know what the fieldnames are called as they are
> created dynamically by a CURSOR in a different routine.
> And its not always 12 months.
> have you any ideas?
> and the dashes are required
> Jayne|||Little PussyCat (SPAMSPAM@.NOSPAM.com) writes:
> I have had a request, one of our tables is used as a report and I have
> been asked that all fieldnames for months have dashes in them, like
> Jan-05 instead of Jan05 and so on...
> Now what we have is a CURSOR which loops through all values in another
> table generating these fieldnames, like 'Jan-05', Feb-05' etc.. Then the
> table definition is modified so these months display as fieldnames.
This is a design that I would strongly discourage use of. Month data
is best stored as rows in a subtable. Then if someone want to looks
at this with the rows as column, you define a view for him, but let
the underlying tables be.
> That is fine,
> Except later it calls another stored procedure which inserts values from
> another table into this one.
> INSERT INTO TableName
> SELECT *
> FROM Tablename
> Obviously this is failing because the newly amended table now has dashes
> in the fieldnames now.
There are two more cases of violations of good practice here:
1) Always list columns explicitly with INSERT:
2) Don't use SELECT * in production code.
Anyway, if you let this poor table be and define that view instead, the
problem will go away.
This may not be the answer you were looking for, but creating columns
dynamically is definitely not what relational databases are designed for,
and if you go there, it becomes very painful.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> This is a design that I would strongly discourage use of. Month data
> is best stored as rows in a subtable. Then if someone want to looks
> at this with the rows as column, you define a view for him, but let
> the underlying tables be.
I have recently taken over from someone else in a job so for the time being
I am having to work with how the original code is written, plus I am
learning how all the systems hook together.
Yes, in my opinion I would have done it in a View but until I feel 100% sure
of how all the systems work I would rather work this way.
Oh, I solved my problem, the stored procedure was failing at the ALTER TABLE
Column command in that CURSOR,
It was failing because of the dashes in the fieldname, so all I have to do
is surround the fieldnames with square brackets when its creating the
column, like this
Exec ('ALTER TABLE TableName ADD' + '[' + @.ColName + ']' + 'Money NULL')
And yes I know having dashes in fieldnames is a big No No, and normally I
wouldn't do it, later on when I feel more comfortable in my job I will
change this to a View then I can simply create Aliases for the Month Names
Thanks for your help anyway :-)
Regards,
Jayne|||Little PussyCat (SPAMSPAM@.NOSPAM.com) writes:
> I have recently taken over from someone else in a job so for the time
> being I am having to work with how the original code is written, plus I
> am learning how all the systems hook together. Yes, in my opinion I
> would have done it in a View but until I feel 100% sure of how all the
> systems work I would rather work this way.
I think that is a fallacy. You only run the risk to be let out on
lonely winding roads that you have no idea where they lead to or
even is what behind the next curve.
> And yes I know having dashes in fieldnames is a big No No,
I wouldn't say so. After all, SQL is designed to accommodate this
possibility. I don't use it myself, as all those brackets or quotes
makes the code more bulky. I'd say the dashes is the smallest of your
problems.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
You can just execute some SqlCommands to insert data to different tables in the Click event of a button, for example:
protected void Button2_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@."Data Source=labsh96223\iori2000;Integrated Security=SSPI;Database=tempdb"))
{
conn.Open();
string insertSql = @."Insert into myTbl_1 select @.id, @.name";
SqlCommand myCommand = new SqlCommand(insertSql, conn);
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.name", SqlDbType.VarChar, 100);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox1.Text);
myCommand.Parameters["@.name"].Value = TextBox2.Text;
int i = myCommand.ExecuteNonQuery();
myCommand.CommandText = @."Insert into myTbl_2 select @.id, @.Description";
myCommand.Parameters.Clear();
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.Description", SqlDbType.VarChar, 1000);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox3.Text);
myCommand.Parameters["@.name"].Value = TextBox4.Text;
i= myCommand.ExecuteNonQuery();
//execute other commands
}
}
