Insert DEFAULT When Getting Values From A SQLquery
Nov 16, 2006
I have a this SP that inserts values into a table with results from a query, but at the same time I want to insert some default values.
But thats NOT working the way I hoped for, actually sqlserver 2005 dont let me create this SP at all.
"Incorrect syntax near the keyword 'DEFAULT'."
Can someone please tell me how I can achieve this?
create procedure %PROC% ( @Ordre_ID int
)
as
begin
IF NOT EXISTS(SELECT Ordre_ID FROM tbl_Ordre WHERE Ordre_ID = @Ordre_ID AND Ordrestatus IN ('2', '3', '4'))
BEGIN
return 0;
END
IF EXISTS(SELECT Ordre_ID FROM tbl_Faktura WHERE Ordre_ID = @Ordre_ID)
BEGIN
return 0;
END
BEGIN TRANSACTION
INSERT INTO tbl_Faktura(
Ordre_ID
,PostNummer
,KID
,Fakturastatus
,Kontonummer
,Forfallsdato
,Belop
,BekreftetBetaltDato
,Faktura_GUID
,Adresse
,PostBoks
,Fornavn
,Etternavn
)
(SELECT
O.Ordre_ID
,K.PostNummer
,DEFAULT
,DEFAULT
,SI.Kontonummer
,(getdate()+14)
,v_OTS.TOTALBELOP
,DEFAULT
,DEFAULT
,K.Adresse
,K.PostBoks
,K.Fornavn
,K.Etternavn
FROM
tbl_Ordre AS O
INNER JOIN
tbl_Kunde AS K
ON
O.Kunde_ID = K.Kunde_ID
INNER JOIN
v_OrdreTotalSum AS v_OTS
ON
O.Ordre_ID = v_OTS.Ordre_ID
,tbl_StatiskeInnstillinger AS SI
WHERE
O.Ordre_ID = @Ordre_ID
)
UPDATE
tbl_Ordre
SET
Ordrestatus = '6'
WHERE
Ordre_ID = @Ordre_ID
COMMIT
end
go
Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable. Is it possible using DataTable with some null values in it? Thanks
I have my stored procedure set to Territory_code IN (@Territory)
, now , how do i enter in more then one value. When i select the multi value check box, it gives me more spaces. But then doesnt recognize the values when i put in more then one. am i doing something wrong?
A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.
One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).
If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.
What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
I tried to insert large pool of data from table A to table B. Table B is then exported to Excel for viewing purpose. However, i found that some of the rows are not inserted in order, they seemed to be inserted in between other rows that are inserted before them. May I know what is the problem? There is no key assigned to table B. Do we need to disable key in order to have items inserted in sequence order?
in other word, instead of "insert", can we "append" records?
Could you pls let me know that when we use the parameters how the sql server ıs able to keep all the datas on the strıng type?and also how ıt can be provıde that securıty on system(how ıt ıgnore to do sql ınjectıon...).
I need to set the default value of a field in my table to 'Regular' if nothing is inserted to that field. Is it not as easy as putting ('Regular') in Default Value?
We have a table that needs to have 00 as the default values in the columns until the point in time where they are updated with the values that we will use. The updated values will be two numbers 1-7 and 3-9 example 47, 14, 26, 68. This is a nvarchar data type in the columns. When I try to set the default values to double zero 00, after i click save it changes them to a single zero 0 instead of a double zero 00.
Code Snippet SELECT * FROM [scholarship] WHERE ([sectionID] = @schoolID OR @schoolID IS NULL) AND ([schlrPrefix] LIKE '%' + @scholarship + '%' OR [schlrName] LIKE '%' + @scholarship + '%' OR [schlrSufix] LIKE '%' + @scholarship + '%' OR [schlrPrefix] + ' ' + [schlrName] LIKE '%' + @scholarship OR [schlrPrefix] + ' ' + [schlrName] + ' ' + [schlrSufix] LIKE '%' + @scholarship OR @scholarship IS NULL ) AND ([Specification] LIKE '%' + @major + '%' OR @major IS NULL ) AND ([reqr1] LIKE '%' + @requirement + '%' OR [reqr2] LIKE '%' + @requirement + '%' OR [reqr3] LIKE '%' + @requirement + '%' OR [reqr4] LIKE '%' + @requirement + '%' OR [reqr5] LIKE '%' + @requirement + '%' OR @requirement IS NULL )
How do I insert/add a default value in if the query above generates zero result?
Hi All im using sqlserver2005 and i have sqlquery to get some fileds now i need to get out put in excel file when i run sqlquery can anyone explain me how can i do it...this is my query select ta_targetpath from tblattributes where ta_pagelevel = 2 and ta_description like '%Do you want to watch this configuration in action%' and ta_targetpath is not null when i execute this i need to get result set in excel file... thnx in advance Hary
Hello everyone,I am developing forums (Discussion Board) in C#.net 2005 with SqlServer. Right now i am having problem in fetching data from two tables.Here are the tables from which i want to fetch data.Topics Table Threads TableTopicID ThreadIDForumID TopicIDTopicName SubjectTopicDescription Replies UserID LastPostDatenow i want to fetch all the topic talbe data as well as total no of threads,Lastpostdate,UserID per topic.I am able to fetch topic table data and Total no of threads per topic through the following query.SELECT TopicID, ForumID, TopicName, TopicDescription,(SELECT COUNT(ThreadID) FROM Portal_Threads WHERE (TopicID = Portal_Topics.TopicID)) AS Threads FROM Portal_Topicsbut i am not able to fetch anoter two detail with subquery as i am getting error likeonly one expression can be specified in the select listorsubquery returned more than one value.can anyone tell me how can i fetch these two values per topic. should i use stored procedure and create temporary table and after fetching these values i can store it in temporary table and i can fetch values from that temporary table...please provide code snippet if possible as i've never used sqlserver before..Thanks in advance...Regards,Nil
I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read.
So can anyone help as I'm really stuck on this one. The code is as follows :
public void DisplayUserInfo() {
ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString); SqlConn.Open(); Console.WriteLine("Connected to DB"); SqlDataAdapter SqlAD = new SqlDataAdapter(); SqlAD.SelectCommand = new SqlCommand("Select * from UserSystemSpecs where UserName ='+ShowUserIDString.ToString()+'",SqlConn); DataSet ds = new DataSet(); SqlAD.Fill(ds); ds.WriteXml(".\ResultsXML.xml", XmlWriteMode.WriteSchema); }
catch (Exception ex) { throw new Exception ("Error Connecting to DB. " + ex.Message); } //SqlConn.Close();
I have a row of data being inserted in to a db table with default values, say: Name Date1 Date2 and I'm inserting VALUES 'myName','12/04/83',NULL Is there any way for the field Date2 to take the value being inserted into Date1 (ideally via default value)? (Any good database tutorials would be handy! Thanks)
Does anyone know if or how I can insert a default value of the (+) character into a column? when I try to save it I get an error message saying it has a problem with ')'.
I have a DTS package that is connected to a table in an Access97 database and to a duplicate(by schema) table in SQL2000. If there is a default value in the Access97 field properties the DTS package will import that value regardless of any other value in the Access field. I can not see anything in the DTS designer that handles this choice.
Even when I look at the Access connection in preview it shows only the default values.
The columns fro which we do not enter any values.Fro eg in my table i did not insert any values to Balance col,in my table. In SQL 2005,management studio,when i said modify table and changed the default to 0 from NULL and also unchecked the checkbox saying Allow Nulls.
I did the above changes and said save.It gave an error saying
'AccountsBalance' table - Unable to modify table. Cannot insert the value NULL into column 'Balance', table 'AccountBalanceSheet.dbo.Tmp_AccountsBalance'; column does not allow nulls. INSERT fails. The statement has been terminated.
How can i change the default value of a column? I already have a columnnamed DateOfRental but I want to alter it so that it has default valuegetdate()ThanksDavid--http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
Pardon the newbie question...but I'm trying to load a dimension table in a small data mart that has columns in it that are unique to the dimension and not sourced from any source table. Two of those columns are date columns that I want to default to the system date and the other column I want to load with a default value. I can't figure out how to do this within a data flow task. The source columns flow from the input db source into a scd transform but I can't seem to edit the columns in the target table table if they don't actually come from a data source. There doesn't seem to be a data transform object to handle this.
I have a report that I run where the selected MONTH and YEAR defaults to the prior month and year. When I run the report it shows the parameter values show as June 2007. Which is correct since this is July.
If I schedule the report to run on the 1st of every month will it default to the prior month/year automatically (I have the "use default" box checked).
Hi I'am practically new in C#, so I want to ask you guys some question.Let say I have this query:"Select EmployeeID, EmpName from PI_Employee"How can I retrieve the result from EmployeeID column and EmpName column and put it into collections of array, so I can call the array and use it again in another function. Can I possibly do that in C# ? if so, how ? please help me guys, I'm on the edge of nervous wreck in here so I could use a little help, any kinds of help. Thanks. Best Regards.
Hi I'am practically new in C#, so I want to ask you guys some question.Let say I have this query:"Select EmployeeID, EmpName from PI_Employee"How can I retrieve the result from EmployeeID column and EmpName column and put it into collections of array, so I can call the array and use it again in another function. Can I possibly do that in C# ? if so, how ? please help me guys, I'm on the edge of nervous wreck in here so I could use a little help, any kinds of help. Thanks. Best Regards.
Building the database I have come across different databases some that add a default value for every field and some that don't. I feel it is a hassle to add a default value, keep track if it is added. I guess with a default value there would be no "NULL" values in the database but one could also make sure in the C# code that all the fields have a value when inputed and on the way out check for nulls. What is the right way???? Pros and cons.........
Hi, I have something similar to the following: CREATE PROCEDURE dbo.MySproc @columnVal int = nullASBEGIN SELECT * FROM MyTable WHERE MyTable.column = @columnVal END If columnVal is not passed into the stored proc i want it to just select everything from 'MyTable' (without the WHERE clause)....how can I do this in as few lines of code possible?thanks
Hi, all, does anyone know of a way to change default values inside of a table without affecting existing values?
I have a table with default = 0 in usermask column. Some rows already have non 0 values in them. I now need to change the default to 1 but still want the non 0 values be there. I also want the new default to take effect when new rows are entered. What's the best and quickest way to do it?
In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.
I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.
To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.
UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0)) FROM facttable FT LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID (In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).
This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.
However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.
Has anyone come across a situation like this (and found a neater solution).
I am creating a new filed in SQL Server 2005, and I want the field to have the default of '0'
I select the data type as numeric and save my changes, I then visit our front end database to see if the default value is just '0' but it appears as '0.00'
SO i thought about changing the default value to int (im thinking this means integer?) and changing it to '3' but it still displays the decimal point and two 0's
Am I doing something wrong here? I just want it to display '0'