Inserting a default Value

I have populated an SQLdata table from an XML datasource usinmg the bulk command. In my SQL table is a new column that is not in the XML table which I would like to set to a default value.

Would anyone know the best way to do this. So far I can's see how to add this value in the Bulk command. I am happy to create a new command that updates all the null values of this field to a default value but can't seem to do this either as a SQLdatasource or a APP Code/ Dataset.

Any suggestions or examples where I can do this.

Many thanks in advance

A DataColumn has a DefaultValue property - seeMSDN for usage:

private void MakeTable()
{
// Create a DataTable.
DataTable table = new DataTable("Product");

// Create a DataColumn and set various properties.
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.Decimal");
column.AllowDBNull = false;
column.Caption = "Price";
column.ColumnName = "Price";
column.DefaultValue = 25;

// Add the column to the table.
table.Columns.Add(column);

// Add 10 rows and set values.
DataRow row;
for(int i = 0; i < 10; i++)
{
row = table.NewRow();
row["Price"] = i + 1;

// Be sure to add the new row to the
// DataRowCollection.
table.Rows.Add(row);
}
}

Inserting a checkbox value into bit field sql server 2000

Edited by SomeNewKid. Please post code between<code> and</code> tags.

---

This is probaly the easiest question you've ever read but here goes.

I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.

Here is my code:

Sub AddSection_Click(Sender As Object, e As EventArgs)
 Dim myCommand As SqlCommand
 Dim insertCmd As String
 ' Build a SQL INSERT statement string for all the input-form
 ' field values.
 insertCmd = "insert into Customers values (@.SectionName, @.SectionLink, @.Title, @.NewWindow, @.LatestNews, @.Partners, @.Support);"
 ' Initialize the SqlCommand with the new SQL string.
 myCommand = New SqlCommand(insertCmd, myConnection)
 ' Create new parameters for the SqlCommand object and
 ' initialize them to the input-form field values.
 myCommand.Parameters.Add(New SqlParameter("@.SectionName", SqlDbType.nVarChar, 50))
 myCommand.Parameters("@.SectionName").Value = Section_name.Value
 myCommand.Parameters.Add(New SqlParameter("@.SectionLink", SqlDbType.nVarChar, 80))
 myCommand.Parameters("@.SectionLink").Value = Section_link.Value
 myCommand.Parameters.Add(New SqlParameter("@.Title", SqlDbType.nVarChar, 50))
 myCommand.Parameters("@.Title").Value = Section_title.Value
 If New_window.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.NewWindow", SqlDbType.bit, 1))
 myCommand.Parameters("@.NewWindow").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.NewWindow", SqlDbType.bit, 1))
 myCommand.Parameters("@.NewWindow").Value = 1
 End If
 If Latest_news.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.LatestNews", SqlDbType.bit, 1))
 myCommand.Parameters("@.LatestNews").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.LatestNews", SqlDbType.bit, 1))
 myCommand.Parameters("@.LatestNews").Value = 1
 End If
 If Partners.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
 myCommand.Parameters("@.Partners").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
 myCommand.Parameters("@.Partners").Value = 1
 End If
 If Support.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.Support", SqlDbType.bit, 1))
 myCommand.Parameters("@.Support").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.Support", SqlDbType.bit, 1))
 myCommand.Parameters("@.Support").Value = 1
 End If
 myCommand.Connection.Open()
 ' Test whether the new row can be added and display the
 ' appropriate message box to the user.
 Try
 myCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & insertCmd
 Catch ex As SqlException
 If ex.Number = 2627 Then
 Message.InnerHtml = "ERROR: A record already exists with " _
 & "the same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please " _
 & "ensure the fields are correctly filled out"
 Message.Style("color") = "red"
 End If
 End Try
 myCommand.Connection.Close()
 BindGrid()
 End Sub

Any response would be appreciatedYou're right; it's easier than you think. Try this:

myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
myCommand.Parameters("@.Partners").Value = Partners.Checked

|||thanks a lot and sorry for the late reply that worked

inserted value on text field gets truncated after 255 chars

Hello,
I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
statement over a table. In the table I have two 'text' fields with the
same properties, with just one difference: one field allows nulls, the
other one does not.
Well, one field actually accepts only the first 255 chars (the nullable
field), while the other field has no problems.
The "Length" property is set to 16 for both fields, as I said all the
properties but one (null/not null) are exactly the same, and also the
context is the same (same database, same table).
Many thanks for your help!
GiovanniHow does your SP look?
It sounds like you truncate it somewhere there. Maybe the parameter is
a varchar or something like that?|||How are you validating that only 255 characters are there? Are you using
SELECT DATALENGTH(col_name) FROM table? Or are you counting the number of
characters in the result set?
"gm1974" <gmascia@.gmail.com> wrote in message
news:1138737544.776002.219580@.f14g2000cwb.googlegroups.com...
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the nullable
> field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
> Many thanks for your help!
> Giovanni
>|||gm1974 wrote:
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the
> nullable field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
>
Are you testing it in QA? If so, you should modify the "maximum characters
per column" setting in the QA options dialog.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Oh, I must be really tired. I definitely forgot to change parameter
type in the SP, it was still set at VarChar(255), so the value was
truncated!
Better to get some sleep, many thanks for your help.|||Thanks for your help, it may be useful in the future.
Giovanni

Insert/Update sql commands not saving to DB

Here is my problem :
I issue an insert statement to the db. While I am getting a return value of 1 (1 row was affected) the values never show up into the db when I open the DB in access. However, I can see the data when it does an SQL select inside the program. So for instance, I do an insert into ORDER values (1, 12, 5.99). (1 = item ID, 12 = quantity, 5.99 = price). I then do a select * from Order, and I get those values back. When I open the DB in access, in between doing the insert and the select, I dont see the values there either. It is like it is making a temporary copy of the DB in memory during the execution only. When I close the program and re-F5, the data is no longer there. Maybe we need some kind of commit transaction? What am I doing wrong? I am using VB.Net 2005/MS Access 2003. Here is the relevant code :
Private m_Connection As OleDbConnection
''' <summary>
''' Defines the path to the database.
''' </summary>
''' <remarks></remarks>
#If CONFIG = "Debug" Then
Public Const DB_PATH As String = "DBs\DB_Test.mdb"
#ElseIf CONFIG = "Release" Then
Public Const DB_PATH As String = "DBs\DB_Production.mdb"
#End If
Sub connect(ByVal p_path As String) Implements IPartyDBase.connect
Dim connect_string As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & p_path
m_Connection = New OleDbConnection(connect_string)
m_Connection.Open()
End Sub
Sub someSub(ByVal stock As StockClass)
Dim tempString
Dim command As OleDbCommand
command = m_Connection.CreateCommand
command.CommandType = CommandType.Text
tempString = "Insert into Stock VALUES (" & stock.ID & ", "
tempString = tempString & stock.Quantity & ", "
tempString = tempString & stock.Price & ")"
Dim tempInt as Integer
command.CommandText = tempString
tempInt = command.ExecuteNonQuery
If Not tempInt = 1 Then
Throw New Exception("Bad addStockToDB into Stock " & tempInt)
End If
End Sub
Sub anotherSub
p_dbase.connect(PartyDBaseAccess.DB_PATH)
p_dbase.someSub()
p_dbase.close()
End Sub
Edit : During execution, looking under bin/debug/DBs, there is a copy of the database that has all the transactions I did during execution... but the actual DB isnt being updated/copied over.
Ok, the problem was that the path was not implicit, and it was overwriting the DB in /bin/debug/DBs... so changing the DB attributes to never copy worked, and opening the file in /bin/debug/DBs instead of the place where it was copying from.

insert/update NULL instead of ''

Hi,
due to special reasons I have to ensure, that in insert and update
statements for varchar-columns (which allow NULL-values) the value ''
automatically becomes replaced by NULL before the records have been
inserted/updated. (This because I have to convert a large application from
another database -which automatically substituted '' by NULL- to SqlServer
2000 SP4).
My first thought was to find a setting in SqlServer server. But I didn't
found one. Is there any?
My second thought was to find a setting in the OLEDB-provider. But I didn't
found one. Is there any?
(I'm using OLEDB, but not ADO)
My third thought has been to define triggers for that case (my very first
ones). I did it as listed below. Is this the correct way? Or is there a
better way? Or perhaps a way with better performance?
create table test (primkey integer not null, testcol1 varchar(10), testcol2
varchar(10))
create trigger test_trigger_u on test instead of update
as
update test set primkey = i.primkey,
testcol1 = nullif(i.testcol1, ''),
testcol2 = nullif(i.testcol2, '')
from test t inner join inserted i
on t.primkey = i.primkey
create trigger test_trigger_i on test instead of insert
as
insert into test (primkey,
testcol1,
testcol)
select primkey,
nullif(testcol1, ''),
nullif(testcol2, '')
from inserted
Regards,
RainerRainer,
Why not just evaluate the incoming value (i.e., stored procedure parameter)
if the value IS NULL replace it with ''. You could optionally use a trigger
as well but the former would probably give better performance and should
probably be used unless you can't control the input method/application.
HTH
Jerry
"Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
news:%23127HJC1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> due to special reasons I have to ensure, that in insert and update
> statements for varchar-columns (which allow NULL-values) the value ''
> automatically becomes replaced by NULL before the records have been
> inserted/updated. (This because I have to convert a large application from
> another database -which automatically substituted '' by NULL- to SqlServer
> 2000 SP4).
> My first thought was to find a setting in SqlServer server. But I didn't
> found one. Is there any?
> My second thought was to find a setting in the OLEDB-provider. But I
> didn't found one. Is there any?
> (I'm using OLEDB, but not ADO)
> My third thought has been to define triggers for that case (my very first
> ones). I did it as listed below. Is this the correct way? Or is there a
> better way? Or perhaps a way with better performance?
> create table test (primkey integer not null, testcol1 varchar(10),
> testcol2 varchar(10))
> create trigger test_trigger_u on test instead of update
> as
> update test set primkey = i.primkey,
> testcol1 = nullif(i.testcol1, ''),
> testcol2 = nullif(i.testcol2, '')
> from test t inner join inserted i
> on t.primkey = i.primkey
> create trigger test_trigger_i on test instead of insert
> as
> insert into test (primkey,
> testcol1,
> testcol)
> select primkey,
> nullif(testcol1, ''),
> nullif(testcol2, '')
> from inserted
> Regards,
> Rainer
>|||Jerry,
our application does not use stored procedures. It uses sql-statements to
select, insert, update and delete data. The application should run against
the previous database (Gupta SQLBase) and against MS SqlServer (depending on
the customer). This goal should be reached with as few source code
modifications as possible. I know, that I can change each affected
insert/update statement in the sourcecode and replace '' by NULL. But I'm
looking for a way to avoid doing this.
Do you think, the triggers are o.k.?
Do you know a better way?
regards,
Rainer
P.S.: By the way, I want to replaye '' by NULL, not NULL by ''
"Jerry Spivey" <jspivey@.vestas-awt.com> schrieb im Newsbeitrag
news:ObsNBRC1FHA.3124@.TK2MSFTNGP12.phx.gbl...
> Rainer,
> Why not just evaluate the incoming value (i.e., stored procedure
> parameter) if the value IS NULL replace it with ''. You could optionally
> use a trigger as well but the former would probably give better
> performance and should probably be used unless you can't control the input
> method/application.
> HTH
> Jerry
> "Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
> news:%23127HJC1FHA.2884@.TK2MSFTNGP09.phx.gbl...
>|||I believe Rainer is actually looking for a way to *insert* null values
instead of the supplied empty strings rather than trying to prevent null
values from being inserted.
NULLIF is the way to go. Yet, I'd suggest handling that in the insert
procedures, rather than in the triggers, and use the triggers if changing th
e
procedures cannot be done (i.e. if there aren't any).
Or did I miss something?
ML|||Functionality wise...a trigger should work fine.
HTH
Jerry
"Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
news:uzfHiaC1FHA.904@.tk2msftngp13.phx.gbl...
> Jerry,
> our application does not use stored procedures. It uses sql-statements to
> select, insert, update and delete data. The application should run against
> the previous database (Gupta SQLBase) and against MS SqlServer (depending
> on the customer). This goal should be reached with as few source code
> modifications as possible. I know, that I can change each affected
> insert/update statement in the sourcecode and replace '' by NULL. But I'm
> looking for a way to avoid doing this.
> Do you think, the triggers are o.k.?
> Do you know a better way?
> regards,
> Rainer
> P.S.: By the way, I want to replaye '' by NULL, not NULL by ''
> "Jerry Spivey" <jspivey@.vestas-awt.com> schrieb im Newsbeitrag
> news:ObsNBRC1FHA.3124@.TK2MSFTNGP12.phx.gbl...
>|||I believe Rainer is actually looking for a way to *insert* null values
instead of the supplied empty strings rather than trying to prevent null
values from being inserted.
NULLIF is the way to go. Yet, I'd suggest handling that in the insert
procedures, rather than in the triggers, and only use triggers if changing
the procedures is not an option (i.e. if - for some insane reason - there
aren't any).
Or am I missing something?
ML|||Ahh...ML...ok.
Something like:
CREATE TABLE #TEST
(ID INT NOT NULL,
VAL VARCHAR(10))
DECLARE @.VAL VARCHAR(10)
SET @.VAL = ''
INSERT #TEST(ID,VAL)
VALUES(1,NULLIF(@.VAL,''))
SELECT * FROM #TEST
--DROP TABLE #TEST
then?
HTH
Jerry
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D577E511-2347-4A2B-9F26-FA578CFEF71B@.microsoft.com...
>I believe Rainer is actually looking for a way to *insert* null values
> instead of the supplied empty strings rather than trying to prevent null
> values from being inserted.
> NULLIF is the way to go. Yet, I'd suggest handling that in the insert
> procedures, rather than in the triggers, and only use triggers if changing
> the procedures is not an option (i.e. if - for some insane reason - there
> aren't any).
> Or am I missing something?
>
> ML|||As the modern German would say: Wonderbra!
ML|||Or as Cosmo would say: Wonderbro! ;-)
Jerry
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1D8DEB5B-C763-4B1F-A18E-2E87411B674A@.microsoft.com...
> As the modern German would say: Wonderbra!
>
> ML|||Hmm... which one? :)
http://en.wikipedia.org/wiki/Cosmo
MLsql

Insert with values and select

Hi

Thnks for the time

I need to insert some values and select a value from another table to insert

insertinto products values(10,'proname','desc',(select modelfrom Products))

How can this be done.

Try:

insert into products(c1, ..., cn)

select 10, 'proname', 'desc', model

from dbo.products

go

AMB

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

you can do it the other way around

Code Snippet

insertinto products

select 10,'proname','desc', Model

from Products

|||

Hi

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Put the result of the "select" statement into a variable and use the variable.

declare @.model varchar(25)

set @.modele = (select model from products where producti = @.productid)

insert into products values(10, 'proname', 'desc', @.model)

go

AMB

sql

Insert With Text Based on Top Row?

I have a table with 3 columns, a varchar, a text, and an int64 (for
PK).
I need to take the text value from highest-numbered int64'd row, and
insert a new row with a new varchar value.
CREATE TABLE [TestTbl] (
[MyVarChar] [varchar] (128) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MyText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestTbl_Id] [int] IDENTITY (1, 2) NOT NULL ,
)
Insert into TestTbl(MyVarChar,MyText) Values ('a','aaaa')
-- This does not work, how should I word it?
insert into TestTbl( MyVarChar,MyText)
values ( 'bb' , (select top 1 MyText from TestTbl order by TestTbl_Id
desc) )
Thanks.> insert into TestTbl( MyVarChar,MyText)
> values ( 'bb' , (select top 1 MyText from TestTbl order by TestTbl_Id
> desc) )
How about :
INSERT TestTbl(MyVarChar, MyText)
SELECT TOP 1 'bb', MyText
FROM TestTbl
ORDER BY TestTbl_ID DESC
Or maybe you could explain why you need to redundantly repeat the same text
from the previous "most recently inserted" row.|||Thanks for the proper syntax.
Column MyVarChar contains a version information string and MyText
contains an XML document. When the version of the front-end
applcaction is changed, we want to copy the data to match the new
version while keeping the older version in "parallel existence". Then
we can compare program operation between different front ends (by
doing a select where MyVarChar='versionstring') and if things look
good we can just use the newer one.
Thanks again for the help.
On Mon, 12 Sep 2005 13:40:21 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>How about :
>INSERT TestTbl(MyVarChar, MyText)
> SELECT TOP 1 'bb', MyText
> FROM TestTbl
> ORDER BY TestTbl_ID DESC
>Or maybe you could explain why you need to redundantly repeat the same text
>from the previous "most recently inserted" row.
>

insert with multiple subqueries

Hi

what i like to do is insert in one table 2 value from 2 different row.

exp:

table1: person

id name

1 bob

2 john

so id like to make an insert that will result in this

table 2: person_knowed

idperson1: 1

idperson2: 2

so the wuery should look something like this:

insert into person_knowed(idperson1, idperson2)
(select id from personwhere name = 'bob',
select id from personwhere name = 'john'));

anybody have an idea of how to acheive this?

insert into person_knowed(idperson1, idperson2)
select A.id, B.id from
(select id from personwhere name = 'bob')A,
(select id from personwhere name = 'john')B

This will work as you are expecting it to work only if the subqueries return 1 row each. If they return more than 1 row, you will end up with a cross-join

Insert with condition

Hi,
what i am trying to do is, reading from an array which has the column name and value and then insert that value to the column which is fetched from that array.And on the other hand i should use select to get the data's which have the same ID.
more detail: what i have >> (VARID,columnName,Value)
what i want>> insert to table (columnName) values (Value) while ID =VARID

any idea how i should do that?
tnx
you can resort to dynamic SQL, e.g.

EXEC('insert into table (' + @.columnName + ') VALUES (' + @.value + ') WHERE ID =' + @.id)

HTH,|||Thanks for your reply. But that was my mistake that i thought Insert is what i need. I should use Update instead.Anyway it is the same story. I am using that in an c# application(web service).
I have this in my code:
objConnect = new SqlConnection(connectionString);
string queryString = "upadte MyTable set "+col+"= "+data+" where DeviceID = "+id+";";
try
{
objConnect.Open();
SqlCommand objCommand = new SqlCommand(queryString, objConnect);
objCommand.ExecuteReader();
}
...

It doesn't work, and because it is inside a web method i get the SoapException ..
I also tried with stored procedure,like this:
ALTER PROCEDURE [dbo].[UpdateData]
@.devID varchar(30),
@.dataCol varchar(30),
@.dataVal varchar(30)
AS
BEGIN
SET NOCOUNT ON;

update DataTransmission
set @.dataCol= @.dataVal
where DeviceID = @.devID
END
and it also doen't work when i do : exec UpdateData 'id','user','me'
Do you know what is going wrong?
|||

I am not sure about the cause of failure in your C# application, but the proc UpdateData will not give you desired result because " set @.dataCol= @.dataVal" will set the variable @.dataCol to have the value of the @.dataVal. The column is not touched at all. You can use dynamic sql inside the proc to achieve what you desire.

Thanks

|||I tried to use dynamic sql in stored procedure like this:
create PROCEDURE [dbo].[UpdateTable1]
@.id varchar(30),
@.col varchar(30),
@.value varchar(30)
AS
BEGIN

update table1
set col1 = case @.col when 'col1' then @.value end
set col2 = case @.col when 'col2' then @.value end
where id = @.id

End

do you think it is right way?
|||that's just fine... but typing will be tedious if you have many columns in your table. |||But sql server has another idea it says there is syntax error (near '=').do you know what is wrong with it? when i check CASE in books, the syntax is right..So i don't know the problem.
|||Remove your second SET statement

create PROCEDURE [dbo].[UpdateTable1]
@.id varchar(30),
@.col varchar(30),
@.value varchar(30)
AS
BEGIN

update table1
set col1 = case @.col when 'col1' then @.value end,
col2 = case @.col when 'col2' then @.value end
where id = @.id

End

HTH,|||Thanks alot. finally works
|||no prob... glad to be of help

Insert values from file

Just in general terms (I will look up the specifics), how would I go
about doing this?
There is value.csv list containing values (000, 001, 002, 003, etc . .
.). There is a table named Attrib. Each value in value.csv needs an
Insert Into Attrib.
Basically I want to perform the following:
Insert into Attrib
values ('A', 'B', 'C', '000')
Insert into Attrib
values ('A', 'B', 'C', '001')
Insert into Attrib
values ('A', 'B', 'C', '002')
With something like 2000 values in value.csv, I don't know the smartest
way to go about this.
Thanks
-tomTom,
Using DTS (Data Transformation Services) would be the easiest way to do
this. DTS is covered in the SQL Books Online and additional information can
be found at:
http://www.sqldts.com/
HTH
Jerry
"tom" <tomfeldsten@.hotmail.com> wrote in message
news:1129133965.223074.121090@.z14g2000cwz.googlegroups.com...
> Just in general terms (I will look up the specifics), how would I go
> about doing this?
> There is value.csv list containing values (000, 001, 002, 003, etc . .
> .). There is a table named Attrib. Each value in value.csv needs an
> Insert Into Attrib.
> Basically I want to perform the following:
> Insert into Attrib
> values ('A', 'B', 'C', '000')
> Insert into Attrib
> values ('A', 'B', 'C', '001')
> Insert into Attrib
> values ('A', 'B', 'C', '002')
> With something like 2000 values in value.csv, I don't know the smartest
> way to go about this.
> Thanks
> -tom
>|||Look at:
http://www.databasejournal.com/feat...cle.php/3331881
There are samples for opening this in a query.
Then you should go with:
INSERT INTO SomeTable
SELECT <columnlist> from <Openrowsetquery>
HTH, Jens Suessmeyer.|||Thanks to the both of you. Good hints for the direction I need to go.
-tom|||And a third one: "sp_addlinkedserver" with a little help from "schema.ini".
Look up both in Books Online.
ML

Insert value not happy, why?

How am I doing so far? I am trying to Insert the user_id if it does not allready exist in the table. But it does not like the value I am giving it in the INSERT statement.

What can I do?

ALTER PROCEDURE dbo.ap_Update_Usr_Type_Data-- define parameters--@.passUserId intAS--Set @.passUserId = UserIdSELECT [user_id]As UserIdFROM CC_host.dbo.usr_tableAs t1Where(emailLIKE N'%@.%'And emailLIKE N'%usa.com')Order By UserIdIfNot Exists (Select [user_id]As UserId2From Usr_Type_DataWHERE UserId2 = UserId)INSERT INTO Usr_Type_Data (user_id, CustomerTypeId)VALUES(t1.UserId, 7)GO

The error I am getting in Query Analyzer (SQL SERVER 2000) is:

The name 'UserId' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Is this going to require a DO Loop inside the stored procedure?

I am not used to this kind of Stored Procedures :)

Is Looping in T-SQL done with BEGIN and END?

|||

You have it all messed up. Assuming you get some parameters, here's how it would look:

ALTER PROCEDURE dbo.ap_Update_Usr_Type_Data-- define parameters@.passUserIdintASBeginIfNOT EXISTS (Select *FROM CC_host.dbo.usr_tableWhere UserID = @.passUserIdAND emailLIKE N'%@.%usa.com')INSERT INTO Usr_Type_Data (user_id, CustomerTypeId)VALUES(@.passUserId, 7)End

|||

Thanks, that is the direction I was trying to head, as indicated in my messages so not sure I am all too messed up. Just have not done it before. :)

I don't want to pass a value from an outside source though, which I think this procedure you have written would require?

Can something be done to pass the value from the first select statement? There are about 1500 records that need to be checked...

DECLARE @.passUserId int

SET @.passUserId = (Select *, user_id As UserId FROM CC_host.dbo.usr_table Where(email LIKEN'%@.%usa.com'))

Begin
If NOT EXISTS (Select * FROM CC_host.dbo.usr_table Where UserID = @.passUserId AND email LIKEN'%@.%usa.com')
INSERT INTO Usr_Type_Data (user_id, CustomerTypeId) VALUES(@.passUserId, 7)
End

Thank you.

Dan

|||

Try this:

Declare @.UserId int

Set @.UserId = 0

SELECT @.UserId =user_idFROM CC_host.dbo.usr_tableWhere(emailLIKE N'%@.%'And emailLIKE N'%usa.com')Order By user_id --(I think you should use TOP 1 to get only one user_id. But, this is OK.

 --You will only insert one in your later query)IfNot Exists (Select [user_id]From Usr_Type_DataWHERE UserId2 = @.UserId)INSERT INTO Usr_Type_Data (user_id, CustomerTypeId)VALUES(@.UserID, 7)GO

|||

Ahh very cool, but it only inserted 1 of the 1500 records it should have updated :) When I executed it.

ALTER PROCEDURE dbo.ap_Update_Usr_Type_Data

AS
DECLARE @.passUserId int
Set @.passUserId = 0

Select @.passUserId = user_id FROM CC_host.dbo.usr_table Where(email LIKEN'%@.%usa.com')

If Not Exists (Select [user_id] From Usr_Type_Data WHERE [user_id] = @.passUserId)
INSERT INTO Usr_Type_Data (user_id, CustomerTypeId) VALUES(@.passUserId, 7)
GO

Probably it was the LAST record in the original query that got inserted. :)

That is why I am wondering If there is not some sort of looping method that could have taken place, or alternatively should the query be put into a derived table?

|||

Perhaps it needs to be done totally different aka:

INSERT INTO Usr_Type_DataSELECT user_id, 7FROM CC_host.dbo.usr_table usrWHERE (usr.emailLIKE N'%@.%usa.com')AND usr.user_idnot in (SELECT user_idfrom Usr_Type_Data)

Insert Value list doest not match column list

HI...

I need to do a simple task but it's difficult to a newbie on ssis..

i have two tables...

first one has an identity column and the second has fk to the first...

to each dataset row i need to do an insert on the first table, get the @.@.Identity and insert it on the second table !!

i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"

here is the script

INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@.@.Identity,
?,
?,
?
)

what's the problem ?

Is that a cut and paste of your query?

There is a missing space between "Reference)" & "Values" in the first insert statement.|||Yes...it's a copy past....

I did what you ask and the problem remains the same|||Then you must not have all of the parameters mapped. Looks like 9 parameters.|||But i'm sure that is a problem....because sql is not mapping automatic !! and i can't do it manual to !! it doesn't work!|||

Alexandre Martins wrote:

But i'm sure that is a problem....because sql is not mapping automatic !! and i can't do it manual to !! it doesn't work!

When you click on the Column Mappings tab, you can't map the columns accordingly?|||

No! it's showing the warning "Insert Value list doest not match column list" and not mapping...

The funny thing is.....this way don't works

INSERT INTO Address(CepID,Street,Number,Location,Complement,Reference)

Values(?,?,?,?,?,?)

INSERT INTO CustomerAddress(AddressID,CustomerID,AddressTypeID,TypeDescription)

VALUES(@.@.Identity,?,?,?)

but this way

INSERT INTO Address(CepID,Street,Number,Location,Complement,Reference)

Values(?,?,?,?,?,?)

INSERT INTO CustomerAddress(AddressID)

VALUES(@.@.Identity)

works perfect.....but i need the other fields....

i changed the table too to test....and with one field works....two or more "Insert Value list doest not match column list" and not mapping"

i don't know what to do....

insert value into 2 columns,1 col. should take data from another table & 2nd random

Hi,

I have table which has 2 columns
username
password

i want to insert username values from by copying data from another table whereas password shd be randomly generated

Thank You

Quote:

Originally Posted by parshupooja

Hi,

I have table which has 2 columns
username
password

i want to insert username values from by copying data from another table whereas password shd be randomly generated


Thank You

Are you looking at doing this directly in MySQL, or do you have a thin/fat client you would like to do this through? I don't believe there is a random text function in MySQL, but many programming languages have ways to accomplish it.|||i am working SQL Server 2005. yes need to do it directly

Quote:

Originally Posted by ilearneditonline

Are you looking at doing this directly in MySQL, or do you have a thin/fat client you would like to do this through? I don't believe there is a random text function in MySQL, but many programming languages have ways to accomplish it.

Insert value in an auto-identity column

I'm a bit new to SQL Server, but here is the problem I am trying to get
around:

1. I have lets say an Employee table where the emp_id is an identity
column that is auto assigned by the db.
2. I wish to insert values into the tables, but sometimes I may wish to
use the value of the emp_id that i supply while inserting (and
sometimes not).

Is there any way in MS SQL to do this? For e.g. is it setup like a
constraint that I can temporarily drop. I do not want to alter the
table, so that option is ruled out.

Any other ways to do this (I know bcp is one of them that allows me to
do this, but looking for ways in the db itself using SQL if possible)

Thanks,
Bharat"Bharat" <go2bharat@.gmail.com> wrote in message
news:1112626803.244086.213160@.f14g2000cwb.googlegr oups.com...
> Xref: news.bluewin.ch comp.databases.ms-sqlserver:126921
> I'm a bit new to SQL Server, but here is the problem I am trying to get
> around:
> 1. I have lets say an Employee table where the emp_id is an identity
> column that is auto assigned by the db.
> 2. I wish to insert values into the tables, but sometimes I may wish to
> use the value of the emp_id that i supply while inserting (and
> sometimes not).
> Is there any way in MS SQL to do this? For e.g. is it setup like a
> constraint that I can temporarily drop. I do not want to alter the
> table, so that option is ruled out.
> Any other ways to do this (I know bcp is one of them that allows me to
> do this, but looking for ways in the db itself using SQL if possible)
> Thanks,
> Bharat

See SET IDENTITY_INSERT in Books Online.

Simon

insert value captured from session into database table

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT Quiz.Title, UserQuiz.DateTimeComplete, UserQuiz.Score FROM UserQuiz INNER JOIN Quiz ON UserQuiz.QuizID = Quiz.QuizID WHERE (UserQuiz.UserName = @.UserName) ORDER BY UserQuiz.DateTimeComplete">
<SelectParameters>
<asp:SessionParameter Name="UserName" SessionField="UserName" />
</SelectParameters>
</asp:SqlDataSource
how to modify this code so that it can insert value of the username captured in the session to the database table record?

you have to add one Insert Command with a parameter. that read from Session["UserName"]

Hope this help.

|||how it should be code men ?

<InsertParameters>
<asp:Parameter ?????? />
</Insert Parameter|||thanks men... i guest i got it now

should be :

<InsertParameters>
<asp:SessionParameter Name="UserName" SessionField="UserName">
</InsertParameters>|||

I think the best way to do it is that do it once through wizard then look at the code generated by wizard. it helped me before.

Hope this help.

sql

insert value 1 into customer table column stand (was "Very Basic Sql")

Hello,

Bit of basic sql here for you:

I have a table called "customer" and there is a field in customer called stand.

Stand is also a seperate table that is joined to customer.

However i would like to add the value of "1" into STAND on the CUSTOMER table and i was wondering if someone could tell me the sql to do this. So basically where there is nothing insert a value of 1 into stand on customer.

Cheersinsert
into customer (stand)
values (1)|||how do i tell it to only insert that into blank values?|||ah, you're probably thinking of UPDATE, not INSERT
update customer
set stand = '1'
where stand = ' '|||Thanks, i will give that a try|||why has someone gone and changed the title of my post?|||Moderator changed unapropriate title ("Very basic sql") into something more meaningful ("insert value 1 into customer table column stand"). As you can see, he indicated the original thread name.

Read more about How to ask questions the smart way (http://catb.org/esr/faqs/smart-questions.html), especially "Use meaningful, specific subject header" chapter.|||Ok thanks for the information

Insert Trigger - How To

I would like to have the value of a field to be set the return value of

System.Web.Security.Membership.GeneratePassword(12,4)

every time a a row is inserted.

Can you guide with this?

Do you have some similar sample code?

Thank you very much

Maybe you can try CLR integration in SQL2005 Create a stored procedure from an assembly?which?generate?the?password,?and?then?call?the?stored?procedure?in?the?insert?trigger?on?the?table.?Take?a?look?at?this?article:Using CLR Integration in SQL Server 2005

But if you just want some random string, why not try?T-SQL new_id() funciton?sql

Insert Trigger

I would like to have the value of a field to be set the return value of

System.Web.Security.Membership.GeneratePassword(12,4)

every time a a row is inserted.

Can you guide with this?

Do you have some similar sample code?

Thank you very much

You can create a dll which contains a method to call System.Web.Security.Membership.GeneratePassword(12,4) and return the generated password. Then add the dll to SQL assemblies and create a CLR reference function for that assembly.?For?more?information?you?can?refer?to:
Using CLR Integration in SQL Server 2005

Insert system time in database

Dear Friends,

I want to insert in my database, in a table field the system time value on that moment.

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

I want to save in my database th system time...

Thanks!!

UPDATE TABLE1 SET MyFieldTime=getdate() WHERE MyFieldID=@.ID|||

it always good practice to use the UTC time instead System's local time. In future if you transfer the data from one server (time zone) to another you need not to applay any changes..

Another benifit on UI you can convert to any Local time from UTC with out any overhead...

use the following query..

Update Table1 Set MyFiedlTime = GetUTCDate() Where MyFieldId = @.ID

|||

You should do this using a DEFAULT on the column and then use DEFAULT keyword in the SET clause of whatever UPDATE statement that modifies the data. Using a separate SP is not really a good idea since you will decouple the actual update and the time when it was done. It is also costly to perform multiple updates on the same row when you can do it once.

You can make below changes:

alter table TABLE1 add default( CURRENT_TIMESTAMP ) for MyFieldTime

After that when you do the actual UPDATE then do:

UPDATE TABLE1

SET col1 = ...

, col2 = ...

, MyFieldTime = DEFAULT

WHERE ...

Insert stored procedure with output parameter

Hello everyone.

I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.

Someone does know how to do this?

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}

|||

Thanks a lot!

While you posted this I solved it out using SELECT @.@.Identity

Is there any diference with the solution you gave me?

|||

Quote from BOL:

SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

Konstantin Kosinsky wrote:

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}