Inserting New Records In A SQL CE Database

Jul 10, 2007

hello everyone



I have a question about new record insertion. I have a SQL Database that I have cofigured as a publisher for a mobile database. I want to be able using the PDA to insert new Customers and synchronize the CE database with the SQL DB. But I encounter a problem with the column field rowguid that SQL inserts as a unique indentifier during the publication. It cannot accept null values and I can't insert a value using the keyboard since the value is generated by SQL.

So my impression is that you cannot use PDA to insert new records, only to update old ones. Is that true or I miss something??



If someone knows the answer please let me know.



Thank you in advanced.

View 8 Replies


ADVERTISEMENT

How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.

Sep 13, 2006

Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables.  I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key.  Is there a way to automatically create new records in the foreign tables that will have the new id?  Would this be a job for a trigger, stored procedure?  I admit I haven't studied up on those yet--I am learning things as I need them. Thanks. 

View 4 Replies View Related

Inserting 100 Records

Jan 17, 2007

How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false

View 4 Replies View Related

Inserting Records

Feb 2, 2006

Hi,
I'm using VWD 2005 Express with SQL Server 2005 Express. I've created a page "create.aspx" which will be used to insert records into a table within the database.
I've used the DetailsView control together with a SqlDataSource control on the create.aspx page.
When I run the create.aspx page it retrieves the first record in the table of the database becuase the SqlDataSource is obviously performing a"Select * from Table" command.
I don't want this to occur. I want an empty page with no records. I know I can set a WHERE clause to a value that will never return any results but I don't beleieve this is an elegant way of doing things becuase I am 'hitting the database' for no reason other than to get the table structure poplutaed into the DetailsView control.
Questions:
1. Am I using the right controls? (in particular the SqlDataSource control)
2. Is there a better way of doing this?
Your advice is most appreciated. Thank you

View 7 Replies View Related

Inserting Records

May 29, 2006

pls i have this issue on inserting records into SQL SERVER Express. this is the code so far :-
imports system.data
imports system.data.sqlclient
Protected Sub btn1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn1.Click
Dim strConn As String = ConfigurationManager.ConnectionStrings("Connect1").ConnectionString
Dim ObjConn As New SqlConnection(strConn)
Dim strComm As String = "insert Products(ProductName,UnitPrice) values(@ProductName,@UnitPrice)"
Dim ObjComm As New SqlCommand(strComm, ObjConn)
ObjComm.Parameters.AddWithValue("@ProductName", txtprodname.Text)
ObjComm.Parameters.AddWithValue("@UnitPrice", SqlDbType.Money).Value = txtprice.Text
ObjConn.Open()
ObjComm.ExecuteNonQuery()
ObjConn.Close()
End Sub
End Class
The issue i have is that anytime i click the button to insert a new record, i get two records added to the database automatically

View 6 Replies View Related

Inserting Records

Jul 11, 2006

hi im a beginner trying to build an application that needs the user to enter some data into a table named Delievary. This table is related to another table named Products. The Products table has the following columns :- ProductsId - PK
ProductName
ProductPrice
Delievary table has the following columns
:- DelivaryId - PK
QuantityRecieved
ProductId - FK
i know to select i have to use inner joins but to insert is what i dont really know about. Please help me out

View 5 Replies View Related

Inserting Records Again

Jul 12, 2006

Ok i hav three tables namely customers, orders and products.
This are there structures Customers-: CustomerId-PK
FirstName
LastName
Address
Orders -: OrderId -PK
QuantityOrderd
OrderDate
CustomerId -FK
ProductId -FK
Products -: ProductId -PK
ProductName
UnitPrice
my question is that i need to relate them so that i can get info on a customer order
Thanks

View 1 Replies View Related

Inserting Records And Displaying Them

Oct 26, 2006

Before I start driving myself nuts, I'd like to make sure my approach is correct.I want to create a simple job posting board.I have a text boxes for company name, email, job title, and job description, and a submit button.I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"When someone fills out the fields and clicks submit, it will insert the new records.So,1. Is this the correct approach so far?2. What is the best way to display the job listings? A grid view?3. At submit time, how can I include that day's date?4. How can I get the records in the database to delete after 90 days?Thanks.

View 1 Replies View Related

Inserting Multiple Records Using For Each

Feb 23, 2007

Hi, I am a rookie at sql and asp.net.  I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section.
I am collecting multiple rows from a gridview.  I validated/confirmed I am capturing the correct values.  How ever, I am having major problems passing these to an sql database.  The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement.  I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem.  Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck.
Regards!
 Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then     chkBox = True     gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString      Response.Write(ddIDs + " " + gvIDs + " ")     If addChkBx1Item.Checked Then          chkBox1 = True          Response.Write("Defaut Item")    End If          Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry     Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)"     Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn)     cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString)     cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString)     cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox))     cn.Open()
      For Each .....          Problems here also
      Next
     cmd.ExecuteNonQuery()Catch err As SqlException     Response.Write(err.Message.ToString)Finally     cn.Close()End TryEnd If
 
 

View 7 Replies View Related

Help With Inserting Multiple Records Using A CSV Value.

Jun 28, 2007

I have the Temporary table:ItemDetailID (int)FieldID  (int)FieldTypeID (int)ReferenceName (Varchar(250))[Value] (varChar(MAX))in one instance Value might equal: "1, 2, 3, 4"This only happens when FieldTypeID = 5.So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the Table FieldListValues(ItemDetailID, [value])I have created a function to split the [Value] into a table of INTs Any Advice? 

View 7 Replies View Related

Inserting Multiple Records At Once

Nov 8, 2004

Hi,

I need to insert records in two tables, one is main table and another is child table. From my aspx page I need to pass info. for one records in main table, insert that record into main table, get the is of the inserted table.

Then insert 15 records in the child table.

Everything must be in a transaction, either everything works or everything fails. Should I do it with aspx or should I pass arrays to a stored procedure?

Thanks!

View 7 Replies View Related

Inserting Many Records Go Too Slow

Feb 11, 2000

Hi!


Are there any parameters that could faster the insert statement?
Maybe some cache or buffer?

Michal

View 1 Replies View Related

Inserting Unique Records

Apr 24, 2007

Hello,

I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.

How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?

Does my request make sense? Please let me know if you have questions.

Thank you for your help!

CSDunn

View 3 Replies View Related

Script For Inserting Records

Jan 12, 2015

I'm faced with the task of creating a script that will do a series of inserts. The insert statements will look the same except for one value, which will differ for each insert.

For simplicity's sake, here is a table with some inserts:

CREATE TABLE
#MyTable (
MyPk INT IDENTITY,
MyValue VARCHAR(24)
)

[Code] ....

Rather than having to modify the string for each individual update statement, I'm hoping there is a simple way to create a variable (perhaps an array) which holds an indefinite number of values, then have a loop which automatically does an insert for each specified value in the array. Is something like this possible? Or is there a preferred industry standard for this sort of thing that I should use instead?

View 2 Replies View Related

Inserting Records On Primary Key

Feb 16, 2006

I'm getting a foreign key error as well as Indentity Insert error when I'm trying to run a stored proc that inserts values from another table..help!!

View 4 Replies View Related

Inserting Records From Excel

Jun 27, 2006

I'm having a problem finding step by step instructions in the few books I have on how to upload to your new table and database once those are set up.

I've got some data in an excel file, and I try to run the DTS wizard to create a package to upload the data from excel, but I can't seem to straiten out my data type problems.

How do I get my excel data types to match my table data types? It shouldn't be this hard to upload something so rudimentary. =(

View 5 Replies View Related

Inserting Multiple Records To A Table

Apr 1, 2004

Hi..
I really need a help.
Is there any way to insert multiple records into a table in one go?

I have a table named Fruit.
It contains FruitId,OwnerId,Colour

The list of colour is got from another table name FruitColour.
FruitColour Consists of 2 column, FruitName and Colour.

Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.

Sample case
I have an Apple.
Fruit id=2
OwnerId=2
Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)

I tried to insert using this query:
Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').

Gives me this error
Subqueries are not allowed in this context. Only scalar expressions are allowed.

I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.

Any suggestion are welcomed.
Thank you in advanced.

View 3 Replies View Related

Inserting Records Via Stored Procedure

Mar 23, 2006

I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
 
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
 
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
 
Please help, I am a newb and lost...as you can tell from my code...

View 4 Replies View Related

Help! Trigger Using &#34;inserted&#34; Not Inserting Some Records.

Oct 10, 2001

Hello all,

I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly:

EventName
EventStatusID
NumberofDays
NumberofStores
PreferredDate1
PleaseContactFlag
EventStoreSelection
EventClusterID

Note: The following fields have their Default Value set to (0)
SystemID
EventStatusID
Coop
NewProductFlag
TVSupportFlag
RadioSupportFlag
FSISupportFlag
RollbackPricing
PleaseContactFlag

Default Value set to (1):
KitInformationID

Here is the trigger:

CREATE TRIGGER EmoesImport ON mc_events FOR INSERT
AS
IF @@ROWCOUNT<>0

BEGIN

SET IDENTITY_INSERT mcweb2.dbo.mc_events ON

DECLARE @SystemID int
DECLARE @EventID int
DECLARE @AccountID int
DECLARE @BillingContactID int
DECLARE @EventName varchar(100)
DECLARE @EventStatusID tinyint
DECLARE @Coop bit
DECLARE @CoopSupplier varchar
DECLARE @SamplesPerDay int
DECLARE @BrochuresPerDay int
DECLARE @AverageDailyMovement int
DECLARE @SalesGoal int
DECLARE @NumberofDays int
DECLARE @NumberofHours int
DECLARE @NumberofStores int
DECLARE @WeekNumber tinyint
DECLARE @PreferredHourID tinyint
DECLARE @PreferredHourother char(20)
DECLARE @PreferredDate1 varchar(20)
DECLARE @PreferredDate2 varchar(20)
DECLARE @NewProductFlag bit
DECLARE @TVSupportFlag bit
DECLARE @RadioSupportFlag bit
DECLARE @FSISupportFlag bit
DECLARE @RollbackPricing bit
DECLARE @PleaseContactFlag bit
DECLARE @EventStoreSelection tinyint
DECLARE @EventClusterID int
DECLARE @KitInformationID tinyint
DECLARE @KitDescription varchar(1000)
DECLARE @KitOther varchar(200)
DECLARE @MCProgNum varchar(7)
DECLARE @rowguid uniqueidentifier

SELECT @SystemID = SystemID FROM INSERTED
SELECT @EventID = EventID FROM INSERTED
SELECT @AccountID = AccountID FROM INSERTED
SELECT @BillingContactID = BillingContactID FROM INSERTED
SELECT @EventName = EventName FROM INSERTED
SELECT @EventStatusID = EventStatusID FROM INSERTED
SELECT @Coop = Coop FROM INSERTED
SELECT @CoopSupplier = CoopSupplier FROM INSERTED
SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED
SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED
SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED
SELECT @SalesGoal = SalesGoal FROM INSERTED
SELECT @NumberofDays = NumberofDays FROM INSERTED
SELECT @NumberofHours = NumberofHours FROM INSERTED
SELECT @NumberofStores = NumberofStores FROM INSERTED
SELECT @WeekNumber = WeekNumber FROM INSERTED
SELECT @PreferredHourID = PreferredHourID FROM INSERTED
SELECT @PreferredHourother = PreferredHourother FROM INSERTED
SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED
SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED
SELECT @NewProductFlag = NewProductFlag FROM INSERTED
SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED
SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED
SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED
SELECT @RollbackPricing = RollbackPricing FROM INSERTED
SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED
SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED
SELECT @EventClusterID = EventClusterID FROM INSERTED
SELECT @KitInformationID = KitInformationID FROM INSERTED
SELECT @KitDescription = KitDescription FROM INSERTED
SELECT @KitOther = KitOther FROM INSERTED
SELECT @MCProgNum = MCProgNum FROM INSERTED
SELECT @rowguid = rowguid FROM INSERTED

INSERT INTO mcweb2.dbo.mc_events
(SystemID,
EventID,
AccountID,
BillingContactID,
EventName,
EventStatusID,
Coop,
CoopSupplier,
SamplesPerDay,
BrochuresPerDay,
AverageDailyMovement,
SalesGoal,
NumberofDays,
NumberofHours,
NumberofStores,
WeekNumber,
PreferredHourID,
PreferredHourother,
PreferredDate1,
PreferredDate2,
NewProductFlag,
TVSupportFlag,
RadioSupportFlag,
FSISupportFlag,
RollbackPricing,
PleaseContactFlag,
EventStoreSelection,
EventClusterID,
KitInformationID,
KitDescription,
KitOther,
MCProgNum,
rowguid)
VALUES
(@SystemID,
@EventID,
@AccountID,
@BillingContactID,
@EventName,
@EventStatusID,
@Coop,
@CoopSupplier,
@SamplesPerDay,
@BrochuresPerDay,
@AverageDailyMovement,
@SalesGoal,
@NumberofDays,
@NumberofHours,
@NumberofStores,
@WeekNumber,
@PreferredHourID,
@PreferredHourother,
@PreferredDate1,
@PreferredDate2,
@NewProductFlag,
@TVSupportFlag,
@RadioSupportFlag,
@FSISupportFlag,
@RollbackPricing,
@PleaseContactFlag,
@EventStoreSelection,
@EventClusterID,
@KitInformationID,
@KitDescription,
@KitOther,
@MCProgNum,
@rowguid)

SET IDENTITY_INSERT mcweb2.dbo.mc_events OFF

END


TIA,

Bruce Wexler
Programmer/Analyst
IT Department
Mass Connections
Ph: (562) 365-0200 x1091
Fx: (562) 365-0283
http://www.massconnections.com

View 1 Replies View Related

Inserting Master And Child Records

Nov 5, 2004

Hi,

I need to insert a record in a master table and 20 records in a child table. I want to do this using stored procedure. Is it better to do it in stored procedure? Have somebody already tried this? Or is there any sample that I can use?

Thanks a lot!

View 1 Replies View Related

Inserting Records From Result Set Into A Table

Nov 7, 2014

I am comparing names of people from a table without id field to a table that has those people along with their ids.

I have a select statements using different field combinations that fetches ids of these people and presents the result set like:

table1id,table1firstname,table2firstname,table1lastname,table2lastname

E.g.:

1 john john smith smith
1 john j smith smith
1 john jon smith smit

I want to be able to insert all the records from the result set into another table, like in the eg, but only excluding those ones that match all the above fields. How would I do this...

View 1 Replies View Related

Retrieving Records And Inserting Into Table

Mar 19, 2008

Folks:

I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?


SET NOCOUNT ON

DECLARE @ImportId INT
SET @ImportId = 5151

DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)


SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)


===================================================================================

Same Query when trying to insert the records in a temp table it takes hours.

===================================================================================

SET NOCOUNT ON

DECLARE @ImportId INT
SET @ImportId = 5151

DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)

create table #TResults
([ID] [INT] IDENTITY(1,1) NOT NULL,
DealName VARCHAR(200),
CUSIP VARCHAR(100),
Vintage INT,
PoolType VARCHAR(100),
PaidOff BIT)


INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)

SELECT * FROM #TResults


============================================


Thanks !

View 7 Replies View Related

Tempdb Gets Out Of Control When Inserting Records

Jul 23, 2005

I have a very big table with 20 million records DistinctProjectionKeywhich i join several times to different tables in this query.select distinct distinctprojectionkeyid,dpk.MarketID,dpk.Classificationid,dpk.DistributorID,dpk.ManufacturerID,dpk.LocationID,dpk.TimeID,P4.FACTOR as factor1,P3.FACTOR as factor2 ,P2.FACTOR asfactor3,P1.FACTOR as factor4into Projectionfactors1FROM DistinctProjectionKey dpk INNER JOIN D_Time tON t.TimeID = dpk.TimeIDINNER JOIN (select * from (select distinctClassificationID_Major,'fam' as lab fromStagingOLTP..ClassificationFlat) cf1)cfON cf.ClassificationID_Major = dpk.ClassificationIDLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p4ON t.TheDate = p4.TheDateAND p4.Name = 'FAM'AND cast(cf.Lab as varchar(20)) = cast(p4.Lab as varchar(20))AND dpk.MarketID = p4.MarketIDAND p4.ManufacturerID IS NULLAND p4.ClassificationID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p3ON t.TheDate = p3.TheDateAND p3.Name = 'fam'AND cast(cf.Lab as varchar(20)) = cast(p3.Lab as varchar(20))AND dpk.MarketID = p3.MarketIDAND p3.ClassificationID = dpk.ClassificationIDAND p3.ManufacturerID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p2ON t.TheDate = p2.TheDateAND p2.Name = 'fam'AND cast(cf.Lab as varchar(20)) = cast(p2.Lab as varchar(20))AND dpk.MarketID = p2.MarketIDAND p2.ManufacturerID = dpk.ManufacturerIDAND p2.ClassificationID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p1ON t.TheDate = p1.TheDateAND p1.Name = 'fam'AND cast(cf.Lab as varchar(20))= cast(p1.Lab as varchar(20))AND dpk.MarketID = p1.MarketIDAND p1.ManufacturerID = dpk.ManufacturerIDAND p1.ClassificationID = dpk.ClassificationIDthe other table have fewer number of records .I find that when I try to do the insert tempdb goes out of control ,itgrows above 100 GB?Would anyone know the reason why and the solution to apply to avoidthis problem?The other tables have fewer recodsClassification flat has 5000 records and projection default flat has32652 records.AjayAjay

View 2 Replies View Related

Inserting Records With Limited Privileges

Jul 23, 2005

I am trying to insert records via ASP, with a user that has only writeaccess to the table (db_datawriter, db_denydatareader).That way, if the server is ever compromised, the access informationstored in the source code's connection string will not allow anybody toactually read the database.The problem is that I would like to use ADO methods to insert the data(to prevent SQL injections), but I can't seem to get the rightconnection. It works in plain SQL, but I'd rather not use it.My current code looks like this:connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;DataSource=MYSERVER;Initial Catalog=MYDB;"set conn=server.createobject("ADODB.Connection")conn.mode=2 ' adModeWriteconn.open connectionSet rs = Server.CreateObject ("ADODB.Recordset")rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTablers.AddNewrs.Fields("testfield") = "TESTDATA"rs.UpdateAnd the error I get is:Microsoft OLE DB Provider for SQL Server (0x80040E09)SELECT permission denied on object 'MYTABLE', database 'MYDB', owner'dbo'.(If I use a User with read privileges in the connection stringeverything works fine.)

View 3 Replies View Related

Problems Inserting Records Into Non Dbo Schema

Sep 6, 2006

I have a basic data flow which tries to insert data from an excel spreadsheet to a loading table (sql server 2005). I have created this table in a non dbo schema. I have used the schema owner as the sql server login for this loading step.

The problem is SSIS seems to throw a strange error when I do this:

OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.


When I create this table in the dbo it seems to work ok. I have tried giving the schema owner sa rights on the sql server and it still doesnt work. Im wondering if this is a known bug in ssis.

Does anyone have any ideas?

View 7 Replies View Related

Inserting Large Number Of Records

Jun 20, 2006

Hi!

The DB I am working with has about 10 tables and some of the tables have 200,000 to 500,000 records. All tables have a clustered index on the primary key.

I performance during INSERT could be better I think - I add thousands of records at a time from many connections.

Is there a way to defer the update of indicies? So that, I can update the tables and then let the indicies regenerate ?

thanks,

Jas.

View 4 Replies View Related

Inserting Multiple Records In Different Tables

Apr 10, 2007

i wants to insert fields of one form in more than one table using stored procedure with insert query,but i gets error regarding foreign key

View 3 Replies View Related

Inserting Records Using The Details View Or Programmatically

Aug 26, 2006

I'm a new user to vwd.  If I use a details view control on my page, I have noticed that the "New" link is not visible unless there is at least one record in the table. Is there any way of making it visible where there aren't any records?My web pages are currently hosted at vwdhosting. I have uploaded my database with the record structure onto the web site and I am using a remote connection string to access it. I have had users updating data in another table on the remote database. If I add records to my new table locally and upload the database to the remote site, all the data that my users have been adding will be lost. So, if I can't add my first record using a control on my web page when there are no records in the table, should I be doing it programmatically? If so, how?Thanks,Julie 

View 2 Replies View Related

INDEXIES – Inserting Of Large Amount Of Records

Feb 25, 2007

Is there any way how to create indexies after insertion of any
number of records (I dont want to create index after insertion of every record,
but for example after insertion of 1000 records) ?



 I heard it should be possible with „bulk insert“ or with
transactions. Is it right ? I need do this with MS SQL Server 2005 (Workgroup
edition).



 Thank you for your ideas!

Jan

View 5 Replies View Related

Inserting Of Duplicate Records Error Message

Jan 15, 2004

I would like to know what options I have with regards to trapping a duplicate record
before it tries to post to a SQL database. I have set the column to unique in SQL. But
when I try to use ASP and post a duplicate record I get a system error. I would like to
just create a referential error to notify the user that they cannot post a duplicate record
please try again. Any help would be greatly appreciated.

RT

View 1 Replies View Related

Inserting 25 Millions Records SQL Server Takes 3hr

May 19, 2008

Hi Teachies,



I am using SQL Server Standard Edition with good HardWare configuration.

In one of table i am inserting around 25 millions records and that takes time around more than 3 hrs.

same thing is happening while fetching records from that table.

this database contains only single file group i.e primary

and that table contains .. Clustered as well as non clustered index.

it doesnot have any Triggers.

How do i increase this performance.

Paritioning of table cannot be use in SQL Server Standard Edition.

Or Dropping all non clustered index before insert operation will improve my performance.

Please find the details ..

SERVER CONFIGURATION:

Intel pentium (R) 4 CPU
2.88 GHZ,
2.79 GHZ ,2 GB RAM

Operating System: WINDOWS 2003 R2 STANDARD SERVICE PACK 2



Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Oct 14 2005 00:33:37

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)





DAtABASE DETAILS:


MDF and LDF located on C: Drive

Available Space on C: DRIVE 2.94 GB




TABLES DETAILS

CREATE TABLE [dbo].[TIX_PAYMENT_SCHEDULE](

[PaymentScheduleId] [bigint] IDENTITY(1,1) NOT NULL,

[OwedAmountId] [int] NULL, --NonClusteredIndex

[ProposalId] [int] NOT NULL, --NonClusteredIndex

[BrandId] [int] NULL, -- NonClusteredIndex

[DueDate] [datetime] NULL, --NonClusteredIndex

[OverdueDate] [datetime] NULL,--NonClusteredIndex

[ExpectedAmount] [decimal](18, 2) NULL,

[TransactionStatusId] [tinyint] NULL,--NonClusteredIndex

[IsLate] [char](1) NULL,

[IsPaymentReceived] [char](1) NULL,

[ScheduleBatchJournalId] [bigint] NULL,--NonClusteredIndex

[IsValidSchedule] [char](1) NULL,

[RuleId] [int] NULL,

[ActionId] [int] NOT NULL,

[ReasonId] [tinyint] NULL,

[Comments] [nvarchar](2000) NULL,

[NoofDays] [int] NULL,

[ActualAmountReceived] [decimal](18, 2) NULL,

[CreatedBy] [uniqueidentifier] NULL,

[CreatedDateTime] [datetime] NOT NULL,

[LastUpdatedBy] [uniqueidentifier] NULL,

[LastUpdatedDateTime] [datetime] NOT NULL,

[CaseScheduleId] [bigint] NULL,--NonClusteredIndex

[ActionDate] [datetime] NULL,

[HasExactMatch] [char](1) NULL,

[IsCatchupBalanced] [char](1) NULL,

[HasModified] [char](1) NULL,--NonClusteredIndex

[PendDate] [datetime] NULL,

[IsAutoAccept] [char](1) NULL,

[CatchupBalanceIdentifier] [uniqueidentifier] NULL,--NonClusteredIndex

CONSTRAINT [PK_TIX_PAYMENT_SCHEDULE] PRIMARY KEY CLUSTERED

(

[PaymentScheduleId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]



TABLE 2



CREATE TABLE [dbo].[TIX_PAYMENT_CASE_SCHEDULE](

[CaseScheduleId] [bigint] IDENTITY(1,1) NOT NULL,

[ProposalId] [int] NOT NULL,--NonClusteredIndex

[DueDate] [datetime] NOT NULL,

[OverDueDate] [datetime] NOT NULL,

[TotalExpectedAmount] [decimal](18, 2) NOT NULL,

[TotalActualPaymentReceived] [decimal](18, 2) NOT NULL,

[TransactionStatusId] [int] NOT NULL,--NonClusteredIndex

[ActionId] [int] NULL,

[CreatedBy] [uniqueidentifier] NULL,

[CreatedDateTime] [datetime] NULL,

[LastUpdatedBy] [uniqueidentifier] NULL,

[LastUpdatedDateTime] [datetime] NULL,

[IsValidSchedule] [char](1) NULL,

[ScheduleBatchJournalId] [bigint] NULL,

[IsCatchupBalanced] [char](1) NULL,

[HasModified] [char](1) NULL,--NonClusteredIndex

[CatchupBalanceIdentifier] [uniqueidentifier] NULL,

CONSTRAINT [PK_TIX_PAYMENT_CASE_SCHEDULE] PRIMARY KEY CLUSTERED

(

[CaseScheduleId] ASC

)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO



STORED PROCEDURE:





CREATE PROC [dbo].[TIX_PRC_GENERATE_PAYMENTSCHEDULE_DATA]
(
@XMLParams XML,
@ToDate datetime,
@HasModified char(1)

)
AS
BEGIN
SET NOCOUNT ON
--Exception Handling Variable Declaration
DECLARE @ErrorMessage NVARCHAR(200),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure NVARCHAR(50),
@ErrorLine INT,
@ErrorDesc NVARCHAR(100)

DECLARE @XMLPayment INT
BEGIN TRY
IF @XMLParams IS NOT NULL
BEGIN --BEGIN IF


SET @ErrorDesc='Error Occured While Inserting into TIX_PAYMENT_SCHEDULE FROM XML'

INSERT INTO TIX_PAYMENT_SCHEDULE
(
OwedAmountId,
ProposalId,
BrandId,
DueDate,
OverdueDate ,
CreatedDateTime,
LastUpdatedDateTime,
ExpectedAmount,
ActualAmountReceived,
ScheduleBatchJournalId,
RuleId,
TransactionStatusId,
ActionId,
IsLate,
IsPaymentReceived ,
IsValidSchedule,
--Added by DC : 119
IsCatchupBalanced,
CatchupBalanceIdentifier,
HasModified
---------------------------------------------------
)
SELECT
Main.ELEMENT.value('(OwedAmountId)[1]','int') AS OwedAmountId,
Main.ELEMENT.value('(ProposalId)[1]','int') AS ProposalId,
Main.ELEMENT.value('(BrandId)[1]','int') AS BrandId,
convert(datetime,Main.ELEMENT.value('(DueDate)[1]','varchar(100)')) AS DueDate,
convert(datetime,Main.ELEMENT.value('(OverdueDate)[1]','varchar(100)')) AS OverdueDate,
@ToDate AS CreatedDateTime,
@ToDate AS LastUpdatedDateTime,
convert(decimal(18,2),Main.ELEMENT.value('(ExpectedAmount)[1]','varchar(100)')) AS ExpectedAmount,
convert(decimal(18,2),Main.ELEMENT.value('(ActualAmountReceived)[1]','varchar(100)')) AS ActualAmountReceived,
Main.ELEMENT.value('(ScheduleBatchJournalId)[1]','bigint') AS ScheduleBatchJournalId,
Main.ELEMENT.value('(RuleId)[1]','int') AS RuleId,
Main.ELEMENT.value('(TransactionStatusId)[1]','int') AS TransactionStatusId,
Main.ELEMENT.value('(ActionId)[1]','int') AS ActionId,
Main.ELEMENT.value('(IsLate)[1]','char(1)') AS IsLate,
Main.ELEMENT.value('(IsPaymentReceived)[1]','char(1)') AS IsPaymentReceived,
Main.ELEMENT.value('(IsValidSchedule)[1]','char(1)') AS IsValidSchedule

--Added by DC for 119

,Main.ELEMENT.value('(IsCatchupBalanced)[1]','char(1)') AS IsCatchupBalanced
,Main.ELEMENT.value('(CatchupBalanceIdentifier)[1]','nvarchar(1000)') AS CatchupBalanceIdentifier
,@HasModified
---------------------------------------------------------------------

FROM @XMLParams.nodes ('(/ROOT/DATA)') AS Main(ELEMENT)

END--END IF


END TRY--Main END TRY
BEGIN CATCH --Main BEGIN CATCH



SELECT @ErrorMessage = @ErrorDesc+Char(13)+Error_Message(),
@ErrorSeverity = Error_Severity(),
@ErrorState = Error_State(),
@ErrorNumber = Error_Number(),
@ErrorProcedure = Error_Procedure(),
@ErrorLine = Error_Line()
RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber,
@ErrorProcedure,
@ErrorLine
)
END CATCH --Main END CATCH
END --Main END





STOREDPROCEDURE 2



CREATE PROCEDURE [dbo].[TIX_PRC_GET_PAYMENTSCHEDULE_SCHEDULE_FOR_DATE_RANGE]
(

@ToDate datetime,
@IsValidSchedule char(1)

)
AS
BEGIN
SET NOCOUNT ON

--Execption Handling Variable Declaration
DECLARE @ErrorMessage NVARCHAR(200),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure NVARCHAR(50),
@ErrorLine INT,
@ErrorDesc NVARCHAR(100)


BEGIN TRY --Exception Handling
SET @ErrorDesc='Error Occured while fetching records from TIX_PAYMENT_SCHEDULE'


SELECT
PaymentScheduleId,
OwedAmountId,
ProposalId,
DueDate,
OverdueDate,
ExpectedAmount,
TransactionStatusId,
IsPaymentReceived,
IsLate,
ActionId,
ActualAmountReceived,
IsValidSchedule,
BrandId,
CaseScheduleId,
ReasonId,
Comments,
NoOfDays,
ActionDate,
IsCatchupBalanced,
CatchupBalanceIdentifier,
HasModified
from TIX_PAYMENT_SCHEDULE with (nolock)
WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule


SELECT DISTINCT OwedAmountId,proposalId,brandId from TIX_PAYMENT_SCHEDULE with (nolock) WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule Order By OwedAmountId,ProposalId,BrandId asc
SELECT DISTINCT ProposalId from TIX_PAYMENT_SCHEDULE with (nolock) WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule Order By ProposalId asc


END TRY
BEGIN CATCH
SELECT @ErrorMessage=@ErrorDesc+CHAR(13)+ Error_Message(),
@ErrorNumber=Error_Number(),
@ErrorState=Error_State(),
@ErrorProcedure=Error_Procedure(),
@ErrorLine=Error_Line(),
@ErrorSeverity=Error_Severity()

RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber,
@ErrorProcedure,
@ErrorLine
)

END CATCH

END





Thanks & Regards

Rajesh Varma

View 6 Replies View Related

Inserting Text Value Into Null Records In A Grid

Feb 11, 2014

I have written up a grid consisting of properties and units.The way it works is we have properties, and within properties there are units. They are two seperate tables.Some properties do not have any units so in the unit reference (UN_UREF) column for those records which do not have units and are NULL I would like to insert the text 'NO UNIT'.Please see below for my SQL for the grid which works fine.

create or replace view VWC_PROPMKUNIT_TEST AS ( SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, UN_UREF, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF);

All I need to do now is insert 'NO UNIT' within the Unit Reference column where it is NULL.

View 5 Replies View Related

Selecting Records From One Column In A Table And Inserting..

Feb 16, 2006

Godwin writes "Hello,
Heres my question..
I have 2 tables.2 paticular columns exist in both the tables.
I want to be able to select those 2 columns on the 1st table and insert them on to the same 2 columns on the 2nd table.

Now,this 2nd table has another 3 columns that exist in another table.I would like to take those 3 column values from that 3rd table and insert it into the 2nd table by modifying those existing records in the 2nd table.In the 3rd table,there will be around 5 records...I want to copy the existing records 5 times in the 2nd table and insert the 3rd tables rows inside the 2nd table in that respective column for 5 rows.

I hope you understand what I mean...Im sorry for really confusing..
Please help me
Thanks
Godwin"

View 1 Replies View Related







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