INSERT Record Through A View
Apr 20, 2004
Is is possible to insert a record through a view. If so, how?
USE Northwind
GO
CREATE TABLE tbForms (
FormID INT IDENTITY (1,1) NOT NULL,
Form varchar (100) NOT NULL
)
GO
ALTER TABLE tbForms
ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID)
GO
CREATE TABLE tbDoubleTeeForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
Height FLOAT,
Flange FLOAT,
Leg FLOAT,
LegCount INT
)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE TABLE tbFlatPanelForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
HEIGHT FLOAT
)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE VIEW MyProducts AS
SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL
SELECT fkFormID, FOrm FROM tbFlatPanelForms
GO
-- How can I insert a new record, the pk of the forms table is identity.
-- Can this be done?
INSERT INTO MyProducts (Form)
VALUES ('My First Entry')
GO
SELECT * FROM MyProducts
GO
DROP VIEW MyProducts
GO
DROP TABLE tbFlatPanelForms
GO
DROP TABLE tbDoubleTeeForms
GO
DROP TABLE tbForms
GO
Mike B
View 13 Replies
ADVERTISEMENT
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Mar 26, 2008
Hi All,
I am trying to create package something like that..
1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I will be appreciated if you can help me...
View 3 Replies
View Related
Jul 20, 2005
Hi All,I am using Microsoft SQL Enterprise Manager version 8.0 and havecreated a view from a combination of 4 different tables. I would liketo be able to go into sql and open the view and select a row anddelete that row however this seem impossible right now. I am not sureif it's possible to delete a row from a view?? Or could it be thatthese tables are all interconnected and in order to delete a recordthat is joined to one or more of the tables it has to be deleted atthe top level of the join heirarchy etc etc. (do you understand what imean?) Can this be done??Thanks in advance,Erin
View 4 Replies
View Related
Nov 13, 2003
Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?
For example let’s say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?
recno persons
1person1
2person2
3person3
Please help me
I will be very grateful if you also reply your answers also and to my email
Email: stavrinc@hotmail.com
Thank you
Christos Stavrinou
View 2 Replies
View Related
Jul 24, 2013
I have a view where the results would be like this.(userid,name,rolekey are my col names with data)
userid name rolekey
test1 tname rolekey1
test1 tname rolekey2
test1 tname rolekey3
is this possible to retireve data from view where i need only userid with rolekey1.? tried with a function but its taking more time? any options in doing it in the view itself?
View 5 Replies
View Related
Mar 28, 2006
Hi All,
I have a view that contains 30 million records.I want to move the view to a table in my database using DTS,but it is taking a lot of time,and making my tempdb to grow fast in giga bytes.Please is there anyway i can copy this view into the table easily in minutes.The view structure and the table structure are the same.Also, how can I index a view and can I add unique key to a view.
Thanks All in advance.
Mokah
View 6 Replies
View Related
Sep 16, 2014
I have a flat file I need to generate, wanted to create my file from a SQL view.
Is there a way to have a Header and Detail Record for each Record in my view?
Fields would be:
Line no type period ref amt date Inv_no
0 M 1 3/3/2014
1 M Pay inv: 400.00 12345
where 0 is the header and 1 is the detail. Only certain fields will be in the header and others in the detail.
View 1 Replies
View Related
Jul 24, 2015
I am creating a simple application form using visual studio 2015. I can create database.mdf successfully and create dbo.table successfully. but when i tried to view table by expanding the table icon on the server explorer, the table should be able to show list of table but it didn't show any record and why is it like that.
View 4 Replies
View Related
Jan 4, 2007
Hi everyone,
I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.
So.. I need help from you guys, cause I don't know How.
This is my DTS description.
My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.
Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.
BUT How??? Can someone please help?? Thanks
View 10 Replies
View Related
Apr 17, 2008
Hello,
In SQL Server Management Studio (2005), 'Open table' command or a SELECT query displays table rows in a grid (or text).Please tell how to view a single row at a time i.e. all only ONE row is displayed at a time (evenly arranged to cover the screen).
This feature (Single record view) is available in other database client like TOAD.
Thank You
View 5 Replies
View Related
Mar 14, 2006
I have a problem with inserting records into table when an indexed viewis based on it.Table has text field (without it there is no problem, but I need it).Here is a sample code:USE testGOCREATE TABLE dbo.aTable ([id] INT NOT NULL, [text] TEXT NOT NULL)GOCREATE VIEW dbo.aViewWITH SCHEMABINDING ASSELECT [id], CAST([text] AS VARCHAR(8000)) [text]FROM dbo.aTableGOCREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERTASBEGININSERT INTO aTableSELECT [id], [text]FROM insertedENDGODo the insert into aTable (also through aView).INSERT INTO dbo.aTable VALUES (1, 'a')INSERT INTO dbo.aView VALUES (2, 'b')Still do not have any problem. But when I need index on viewCREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])GOI get following error while inserting record into aTable:-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4-- Only text pointers are allowed in work tables, never text, ntext, orimage columns. The query processor produced a query plan that requireda text, ntext, or image column in a work table.Does anyone know what causes the error?
View 1 Replies
View Related
Feb 24, 2006
Ok this is a simple question but i cant seem to find out how since i had the access of a query builder for web matrix.I have the following:A Table called Customer, with Cust_Name, Cust_phoneI have modified my CreateUserWizard to accomodate mroe text boxes which would finally store to vales for Cust_Name, Cust_phone.Question is how can i perform an INSERT query? or rather i noticed that the query builder doesnt allow me to, instead the query builder is towards inserting new records thur GridView and FormView.I've designed a query to handle wildcards i.e @Cust_Nameso i'm thinking if i can have a working insert statment i can do something liketbCust_name.txt = @Cust_Namethen finally perform the insert queryAny ideas? thanks
View 2 Replies
View Related
Dec 29, 2006
Hi,
I would like to know is it possible to combine these 2 statements into 1, as I want to insert a single record set in tblRefLockers.
statement 1:
INSERT INTO tblRefLockers (Locker_No)
SELECT Locker_No from tblLockersInfo
WHERE MemberNo = 1234
statment 2:
UPDATE tblRefLockers
SET Locker_Condition = 'BAD'
WHERE MemberNo = 1234
Can I do that?
Thanks,
KNVB
View 6 Replies
View Related
Aug 4, 2007
Hello I’m using express edition to create my trail test
application. Below is the code that I have and I’m trying to insert data to the
database table named “Coin�.
Protected Sub
btnSave_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnview.Click
Dim s As
String = txtCname.Text
'Dim myConnection As New
SqlConnection(myConnString)
Dim desc As
String = txtCDesc.Text
Dim ConStr As
New SqlClient.SqlConnection
ConStr.ConnectionString = "server=testsqlinstance;
Integrated Security=True"
Response.Write("Connection string: " & ConStr.ConnectionString)
Try
Dim SelectQuery As
String = "SELECT
max(coinid) from coin"
Dim idval As
Integer = 0
Dim command As
New SqlCommand(SelectQuery, ConStr)
ConStr.Open()
idval = command.ExecuteScalar()
Console.WriteLine(idval)
idval = idval + 1
Dim InsertQuery As
String = "INSERT
INTO COIN(coinname, coinid, ebayid, ebaymember, ebaymemid , amount , coindesc)
VALUES('1992-Proof'," & idval & ",'eewerwer','sp6937','serwryana',67.70,'Matt
Proff of 1992- Mint Set')"
Dim command1 As
New SqlCommand(InsertQuery, ConStr)
command1.ExecuteScalar()
Dim S1 As
String = "Record
insert - Successful!"
Console.WriteLine(S1)
Catch ex As
Exception
Label2.Text = ex.ToString
Finally
ConStr.Close()
End Try
End Sub
This program is
throwing an exception (mentioned below)
Exception ---> System.Data.SqlClient.SqlException:
Invalid object name 'coin'. at
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection) at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj) at
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at
System.Data.SqlClient.SqlDataReader.get_MetaData() at
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult
result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at
System.Data.SqlClient.SqlCommand.ExecuteScalar() at _Default.btnview_Click(Object
sender, EventArgs e) in C:Documents and SettingsarshaMy DocumentsVisual
Studio 2005WebSitesWebSite1Default.aspx.vb:line 92
“Coin� is the table name – which is in SQL server. Kindly
help me to handle and overcome this exception.
Thanks
View 2 Replies
View Related
Jan 12, 2008
Hi
I have a table in sql server with a numeric field as Primary Key. When i insert a new record i need that primary key increments automatic (like access auto increment) because i want avoid the possibility of duplicate Primary Keys.
Is that possible?
Thank you
View 2 Replies
View Related
Jan 29, 2006
hi all,i have a question that how to get id afer insert a new record.i just read from the following article, but still can't get the meaning:http://forums.asp.net/1008337/ShowPost.aspx
and my code Dim sql_3 As String sql_3 = "insert into paper_type (name, enable,creation_date,lastupdate,user_id) values " sql_3 += "('" & name & "','" & enable & "', getdate() ,getdate ," & user_id & ")" Call OpenRs(sql_3) ' I want to get the id immediately, but how?? (i am using sql server) can somebody help me to solve the problem??thanks a lot
View 9 Replies
View Related
May 29, 2006
I'm trying to make a website that people can upload file to the server, then the webpage will automatically insert the username and the file name into the database. I have thought about this for couple days but still got nothing. can some one help me?
can some one post a very simple aspx file that can insert a record to the sql database with C#? Not the code that generated by ASP.net 2.0!
I want to see how to access the database manually.
Thank you.
View 2 Replies
View Related
Dec 9, 2002
I am trying to do an update to a database with the code below, the code will work in generating a single record (if i exclude the IF statement) but i need it to create multiple new records based on the IF statement (or something similar) i have in the code. It needs to create updates for every record that has a parentguid that matches the one specified in the code.
the idea is, the hierachy structure in the tables are:
Location1
--> Part1
--> part2 etc
Location2
--> part1 etc
i want it so that when i input a new part it updates to all the locations. The locations all have the same parentguid (the one specified in the code)
With regards to the stored procedure it executes, this stored procedure creates the entry. I don't really want to change the stored procedure because other functions reference and use it.
My code:
DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID2 VARCHAR(50)
select @parentguid2 = parentguid from dsdba.itemgroups
if @PARENTGUID2 = '8CF850AD-2026-411B-AABE-BF1584624EB3'
BEGIN
SET @PREFIX = 'JUST'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS WHERE CODE = @PREFIX
SET @CODE = @PREFIX + '02'
SELECT @GUID = NEWID()
EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, 'JMB', @PARENTGUID, 1
END
:confused:
View 1 Replies
View Related
Oct 3, 2003
Given the following code, why won't the information that I have at the end of my WHILE loop insert at the end of the table?
For some reason (there are over 7000) records being written to this table, the row that I am trying to insert is always included at row 73.
Any ideas? All I want to do is have it insert at the end of this script at the end of the table.
Thanks,
Steve
ALTER procedure sp_Cigna_build_export
as
Declare CignaExport Cursor
For
Select
exprectype,
expssn,
expfiller,
expbody
From cigna_employee_sort
Declare
@RecordType char(2),
@SSN char(11),
@Filler char(20),
@Body char(867),
@Counter int,
@CountLength int,
@RecordCount char(9)
select @counter = 0
Open CignaExport
Fetch Next from CignaExport
Into
@RecordType,
@SSN,
@Filler,
@Body
While @@fetch_status = 0
Begin
select @counter = @counter + 1
insert into cigna_export_table
(exportfield)
select
@recordtype + @SSN + @Filler + @Body
Fetch next from CignaExport
Into
@RecordType,
@SSN,
@Filler,
@Body
End
-- Adding two here accounts for the header/trailer records
select @recordtype = '99'
select @counter = @counter + 2
select @RecordCount = '000000000'
select @CountLength = len(@counter)
print @counter
insert into cigna_export_table
(exportfield)
select
@recordtype + stuff(@recordcount,10-@countlength,@countlength,@counter)
View 5 Replies
View Related
Mar 3, 2004
can anyone help to explain how to insert multiple record into one/two table?
ex:lets say when user specify start date and end date, then we need to created and insert the record on that duration.
and how to do with insert the record weekly or monthly?until the end of the date!
View 1 Replies
View Related
Oct 6, 2003
Hello folks,
I am new to msSQL and ASP, I need some help writing an SQL statement that will first check to see if a combination or record exists, if none found thant it will add it. I am working a section of a site that adds favorites to the database. Each user can have more that one favorite hence it has to check for that unique combination of the fields, UserID and FavID
My Code:
Dim objConnection, objRecordset, strSQL
Dim strFavID, strUserID
strFavID = request.Form("favid")
strUserID = request.Form("userid")
Set objConnection = Server.CreateObject("ADODB.Connection")
Set objRecordset = Server.CreateObject("ADODB.Recordset")
objConnection.Open Application("ConnectionString")
strSQL = "INSERT INTO FAV (UserID,FavID) (SELECT DISTINCT " & strUserID & " AS UserID " & _
strFavID & " AS FavID" & " FROM FAV)" & _
" WHERE " & strUserID & " & " & strFavID & " NOT IN (SELECT UserID, FavID FROM FAV)"
response.Write(strSQL)
'response.End()
If strFavID <> "" Then
On Error Resume Next
objConnection.Execute(strSQL)
objConnection.Close
Set objConnection = Nothing
End If
This code is giving me a syntax error, how do I write the correct statement. I am using MS Access 2k
Thanks for your help
View 9 Replies
View Related
Jun 21, 2004
/*if key values exist don't insert new record*/
SELECT
/*if exists don't insert*/
CASE
WHEN ISNULL(gradeId, -1) = -1 THEN
INSERT INTO tblScores
(gtStudentId, assignmentId, score)
VALUES (@nStudent, @nAssignment, 0)
END
FROM tblScores
WHERE gtStudentId = @nStudent AND assignmentId = @nAssignment
tblScores has two fields comprising its primary key (gtStudentId, assignmentId) and the gradeId field is a required filed in this table.
I'm getting syntax errors when I click check syntax (near keywords insert from and end).
one other note: this CASE END is nested inside a BEGIN END loop, is this the problem? Is the 'End" of the 'Case' closing the 'End' of the 'Begin'?
thanks
View 6 Replies
View Related
Mar 22, 2006
SOS
I am working with SQL 2000 located on a Networksolutions server and I have a Test record I inserted manually, but I am unable to insert a new record and get this error "can't insert new record to database".
No doubt this is a no brainer, but I can't solve it.
Many Thanks
Who ever you are.
View 7 Replies
View Related
Jan 31, 2007
hi. we have 1 production database and a 2nd backup database. one of our users accidentally deleted a case and we would like to get it back. our production database is updated nightly so none of the case's information would've changed.
how can i insert the data from the backup database to the production database? i need to copy one row. thanks for your help!
View 1 Replies
View Related
May 30, 2008
Using trigger
want to update a field being inserted from another record in the same table.
the record being inserted I want to pull the bkjrcode from another record where the account = 1040 that also has the same ord# and inv# as the record being inserted.
Here is what I've tried with no luck.
create trigger [updategbkmut] on [dbo].[gbkmut]
after insert
as
update g1
set g1.bkjrcode = g2.bkjrcode
from gbkmut g1
inner join inserted i
on i.ord_no = g1.ord_no and i.inv_no = g1.inv_no
inner join gbkmut g2
on i.ord_no = g2.ord_no and i.inv_no = g2.inv_no
where i.freefield3 = 'Rebate' and g1.account = '1040'
View 2 Replies
View Related
Mar 22, 2006
George writes "SQL server 2005 Express, Windows XP Pro SP2
I want to create a stored procedure that will insert a record in a child table (tblcontproj)propulating two columns with columns from the parent (InterestListoriginal)and two columns with user selected values. I created an insert select statement that looks like ..
" INSERT INTO tblcontproj(proj_rpt_id, proj_name) SELECT proj_rpt_id, project_name FROM InterestListoriginal WHERE (proj_rpt_id = @proj_rpt_id)"
This works great! Now can I add two columns to the INTO clause for projcontshortname and projconttye and use a VALUE clause that sets tblcontproj.projcontshortname and tblcontproj.projconttype to @projcontshortname and @projconttype which are user selected values from a downdownlist.
Or add an UPDATE query?
Thanks "
View 1 Replies
View Related
Oct 30, 2007
Is there a way to INSERT a new empty record without having to designate a field?
I tried the obvious but they issue errors.
--PhB
View 11 Replies
View Related
May 27, 2006
i have a table name is HH tableit has two columns 'hhno' and hhname'HH tabelehhno hhname100 suresh101 baba103 rami want to insert a one record(102 , chandra) in HH table between(101,baba) and( 103 ,ram).how can i insert them please help ,me thanks
View 10 Replies
View Related
May 2, 2007
Hi everyone:Using Sql Server SQL 8I'm trying to INSERT records into a "can software package" batchtable. I have a work-table that mimics the batch table. Aftermanipulating the records in the work-file I want to INSERT them intothe batch table.The problem is the batch table in the can software has a trigger onthe batch table which is going to force me to INSERT one record at atime. I've always been able to do an INSERT with no problem.The batch table has pretty basic columns:BatchIDBatchDateSeqNumberThese three fields are the key and then just some miscellaneouscolumns. Any easy way to loop thru my work-file to insert theserecords. Never done a loop in SQL so an example would be reallyreally appreciated. I have a sequence number so I was hoping to do aWhile loop but I really don't know enough about creating a loop tomake that call. Thanks in advance for any help.
View 6 Replies
View Related
Jul 6, 2007
I receive the following error when trying to INSERT INTO; am using Access db.
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
The code is as follows:
<%
Dim dbConn
set dbConn = server.CreateObject("adodb.connection")
dbConn.open("connect")
dbConn.Execute("INSERT INTO tbl_country (region, countryName, population, country_currency, description, imageURL, imageALT) VALUES ('" & Request.Form("region") & "', '" & Request.Form("countryName") & "', '" & Request.Form("population") & "', '" & Request.Form("country_currency") & "', '" & Request.Form("description") & "', '" & Request.Form("imageURL") & "', '" & Request.Form("imageALT") & "'),")
Response.Redirect("admin_master.asp")
%>
I would greatly appreciate any help you can give me.
View 7 Replies
View Related
Dec 17, 2005
Well, how do you do it? I've not seen any report on this... so could it be next to impossible?
Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.
View 26 Replies
View Related