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
 

View 3 Replies


ADVERTISEMENT

Filling A DataTable From SqlQuery : If SqlQuery Returns Null Values Problem Ocurrs With DataTable

Sep 3, 2007

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

View 2 Replies View Related

Insert A Row With Default Values

Aug 12, 2014

I need to insert a record in a table which has about 90 columns.

My first column is a CustCode that would be supplied by parameter all other columns should be set to 0

Is there a quick way of doing this?

View 5 Replies View Related

How Do I Enter In A Default Values For A Report Parameter That Accepts Multi Values

Apr 11, 2008



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?

The field is a Varchar 20

View 1 Replies View Related

SQL 2012 :: Find Out Values Of Parameters When SP Is Executed With Default Values?

May 30, 2014

I am working with SP. How can we find out values of parameters when the SP is executed with the default values?

View 9 Replies View Related

Default Values Does Not Include All The Values (Cascading Parameters)

Mar 18, 2007

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.

 

View 8 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

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.

View 2 Replies View Related

Insert By Key Or Sequence? What's The Default Insert Feature?

Feb 22, 2007

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?

View 9 Replies View Related

SQLQUERY

Mar 7, 2008

Hi,Here is my SQL Query.SQL = "insert into mnetwork..tblContactCorr(CorrespondentID,Name,Phone,Fax,Email,UserName,rowguid,Status,ChgPassword,lastlogin,MailNotification,USerLevel,LocationID,msrepl_tran_version)"SQL = SQL & " values( '" & drpCorr.SelectedValue & "', '" & txtName.Text & "', '" & txtphone.Text & "', '" & txtFax.Text & "', '" & txtEmail.Text & "', '" & txtUserName.Text & "', '" & mGuid & "', '" & drpstatus.SelectedValue & "', '" & "Null" & "', '" & getdate & "', '" & drpmn.SelectedValue & " ', '" & drpuserlevel.SelectedValue & "', '" & drplocation.SelectedValue & "', '" & "Null" & "')"Dim dbcommand As New SqlCommand(SQL, ObjConn)ObjConn.Open()dbcommand.ExecuteNonQuery()------>here is the problemdbcommand = New SqlCommand("select @@identity", ObjConn)Dim NewUserID = dbcommand.ExecuteScalarAfter executing i am getting error like this:Message "Syntax error converting from a character string to uniqueidentifier." StringI don't know how to convert mguid into uniqueidentifier.here is my batabase fields:ContactID-->this is auto incrementCorrespondentID--intName-varcharPhone-varcharFax-varcharEMail-varcharUserName-varcharrowguid--uniqueiduntifierstatus-tiniintchgpassword-snallintlastlogin--datetimemailnotification--smallintlocationid--intmsrepl_tran_version-uniqueiduntifier -(this is null)let me know whats wrong in this?

View 8 Replies View Related

Parametres - SqlQuery

Jun 22, 2007

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...).

View 1 Replies View Related

Default Values

Mar 2, 2006

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?

View 4 Replies View Related

Default Values

Aug 3, 2001

How do you enter a default value where it is a combination of values ?

I want a default value to be entered into a field every time a create a new record.

The field will contain the "primary key number" + username

Cheers

Gary

View 1 Replies View Related

Default Values

Dec 27, 2005

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.

Miranda

View 4 Replies View Related

How To Add Default Values?

Feb 11, 2008

This is the query that I have.




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?

View 4 Replies View Related

Get Excel File When Run Sqlquery

Dec 23, 2007

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

View 1 Replies View Related

Sqlquery Or Storedprocedure Error

Jun 18, 2008

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 

View 8 Replies View Related

SQLquery Using Parameter Help Needed Please C#???

Aug 14, 2004

Hi All,

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();


}

}


Thanks

Garry

View 2 Replies View Related

MSSQL Default Values

Aug 23, 2006

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)

View 1 Replies View Related

Default Values In SQL Server

Sep 22, 2004

I am designing a new Database using SQL Server 2000 Enterprise Manager. I have a field called Phone, Fax and Zip.

Under Enterprise Manager I am trying to set up default values for these fields

Phone, Fax = 000-000-000
Zip = 00000

However Sql Server truncates it to 0.

How do I default value as shown about in SQL server?

View 5 Replies View Related

Default Values In Tables

Oct 26, 2000

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 ')'.

View 1 Replies View Related

Help With DTS And Access Default Values

Jun 15, 2001

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.

Any thoughts on getting the actual values?

Thanks

View 1 Replies View Related

How To Change The Default Values

Nov 13, 2006

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.

Plz help

Poornima

View 2 Replies View Related

Changing Default Values

Jul 20, 2005

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

View 4 Replies View Related

Setting Default Values

Jun 6, 2006

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.

Thanks.

View 5 Replies View Related

Default Values And Subscriptions

Jul 18, 2007

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).

View 1 Replies View Related

Hide Default Values

May 23, 2007

Is there a way to hide my default values? If so, how?

View 13 Replies View Related

Retrieving SQLQuery Results In Array On C#--How???

Apr 9, 2008

 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. 

View 9 Replies View Related

Retrieve SQLQuery Result In Array On C#--How ????

Apr 9, 2008

 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. 

View 2 Replies View Related

Default Values For Database Fields Yes Or No????

Aug 4, 2006

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.........
 
Newbie

View 1 Replies View Related

Select All Table Values By Default

May 20, 2007

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 

View 3 Replies View Related

Change Default Colume Values

Feb 8, 2001

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?

Thanks for advice.


Richard

View 2 Replies View Related

Updating By Default/specific Values

Apr 5, 2007

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.

E.G.

AffiliateID, TypeID, Revenue
0, 1, £50
22, 1, £55
33, 1, £57
0,2,£60
22, 2, £66
33, 2, £69


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).

View 2 Replies View Related

Creating New Field With Default Values...

Nov 7, 2007

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'

Thanks

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved