Inserting Data Into Two Tables (Getting ID From Table 1 And Inserting Into Table 2)
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)
Values
(@title,@description,@link,@Whatever)
Select WhateverID from Table1 Where Description = @Description
Insert into Table2
(CategoryID,WhateverID)
Values
(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
View Complete Forum Thread with Replies
Related Forum Messages:
Inserting Distinct Data From One Table In Another Table, How?!?Really Urgent And Needing Help!!!
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View Replies !
Inserting From Datas In One Table From Multiple Tables
Hi guys,I have a problem with my query. What i want to happen is to populate my table EV_NOTIFICATIONDETAILS (Docownerid, CurrentSentDate, LastSentDate, detailsID, GeneralRemarks) using the datas from the two different tables EV_NOTIFICATIONHEADER and EV_DOCDETAILS.I tried to create some a query but im having a error. The problem is once i insert the data from datas to the columns Docownerid, CurrentSentDate, LastSentDate the datas are stored in the database and when i tried to insert the remaining columns TO EV_NOTIFICATIONDETAILS detailsID, GeneralRemarks getting the datas from EV_DOCDETAIL it creates a new set of records in the database. Meaning it doesn't update the records in the table EV_NOTIFICATIONDETAILS but it creates a new set of records.here's my code:INSERT INTO EV_NOTIFICATIONDETAILS (Docownerid, CurrentNoticeSentDate, LastNoticeSentDate) SELECTDocownerid, CurrentSentDate, LastSentDateFROMEV_NOTIFICATIONHEADERINSERT INTO EV_NOTIFICATIONDETAILS (detailsID,GeneralRemarks) SELECTdetailsID,GeneralRemarksFROMEV_DOCDETAIL Any ideas and suggestions will be greatly appreciated.
View Replies !
Inserting Data Into A Table Referencing PK From Another Table
How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event Schedules data is entered into the Schedules Table first then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table. How do i insert data into Event table referencing the (PK ID) from Schedules Table ? Fields inside each of the tables can be found below: Event Table (PK,FK) ScheduleID EventTitle AccountManager Presenter EventStatus Comment Schedule Table (PK) ID AletrnateID name UserID UserName StartTime EndTime ReserveSource Status StatusRetry NextStatusDateTime StatusRemarks
View Replies !
Inserting Values From Multiple Tables To Only One Column Of A Seperate Table
Hi all, I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise. Thanks
View Replies !
Inserting Data From One Table To Another
Hi, I have two tables in a data base and i'm inserting the data from one into the other...no probs. What i was wondering is, in table1 i have an column of ID numbers. In the table2 i have a matching set of ID numbers. There are 5 PersonID numbers in table one and 10 in table two, the same 5 numbers as in table1 but each ID has a duplicate with different data in the two rows. SAMPLE: INSERT INTO Table1 (PersonID, level1, Level2, Level3, Level4) SELECT PersonID,level1, Level2, Level3, Level4 FROM Table2 When i insert the data into table1 it leaves the first 5 rows of data as null and then populates the table with all the data from table two. Is there anyway of preventing these first 5 columns from remaining empty.... I hope that makes sense
View Replies !
Inserting Data Into Another Table
Hi, I'm fairly new to SQL Server 2005. i have a table that creates customer id's along with other data (let's call it Customer) I would like to take the same customer_id data and import it into a different table (HQ_Customer) the new table also has different column names. Is there a script that can be used for this problem?
View Replies !
Inserting Data From One Table To Another
Well, I think this should be an easy question, but here goes: I'm taking data from one table and inserting it into another. According to the SQL Server Mobile Book Online, the syntax goes like this: INSERT INTO Table1 (col1, col2) SELECT (col1, col2) from Table2 So while I can do this with my tables: INSERT INTO sensor_stream (sensor_stream_id) SELECT (sensor_stream_id) FROM sensor_stream_temp If I add any more columns, I get an error. Like this: INSERT INTO sensor_stream (sensor_stream_id, sensor_stream_type_id) SELECT (sensor_stream_id, sensor_stream_type_id) FROM sensor_stream_temp The error is "There was an error parsing th equery. [ Token line number =1, Token line offset = 98, Token in error = ',' ]" Anyone have any ideas about why I cannot do more than one column at a time? TIA, -Dana
View Replies !
Inserting Data To Table From Textbox
Hello all.... I am trying to submit data from a form(textbox) to a sql table. but I am getting an error message "NullReferenceException was unhandled by user code" Can any help me with this? This is my code inProtected Sub btnSubmit_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim cnstr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()Dim pa1 As Data.SqlClient.SqlParameter = New Data.SqlClient.SqlParameter("Keyword", Data.SqlDbType.VarChar, 50, Data.ParameterDirection.Input) pa1.Value = Keyword.Text SqlHelper.ExecuteNonQuery(cnstr, Data.CommandType.StoredProcedure, "spNewRec", pa1) Thanks in advance...
View Replies !
Inserting Data From A Constructed Table
I can do this in an old (~2003) way, but I'm trying to figure out a new (2005) way. What I've got is an e-commerce project in which users select various products from a catalog and add them to a shopping cart. Then they go to a checkout page which displays the current contents of the shopping cart, which are contained in a manually-constructed data table (system.data.datatable). On the checkout page a GridView displays the contents of the data table. At that point they can check out via button click, which launches the following (somewhat simplified) ADO.NET code:1 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) 2 'Some variables 3 Dim intCounter As Integer 'Used to count the loop 4 Dim prmQuantity As New System.Data.SqlClient.SqlParameter() 'A parameter 5 Dim prmProduct As New System.Data.SqlClient.SqlParameter() 'A parameter 6 Dim prmPrice As New System.Data.SqlClient.SqlParameter() 'A parameter 7 Dim InsertCommand As New System.Data.SqlClient.SqlCommand 'The SQL insert command 8 Dim dbConnection As New System.Data.SqlClient.SqlConnection 'The connection to the DB 9 10 'Set the parameters for the SQL statement 11 prmQuantity.ParameterName = "@Quantity" 12 prmQuantity.SqlDbType = Data.SqlDbType.Int 13 prmQuantity.Size = 18 14 prmQuantity.Direction = Data.ParameterDirection.Input 15 16 prmProduct.ParameterName = "@Product" 17 prmProduct.SqlDbType = Data.SqlDbType.VarChar 18 prmProduct.Size = 50 19 prmProduct.Direction = Data.ParameterDirection.Input 20 21 prmPrice.ParameterName = "@Price" 22 prmPrice.SqlDbType = Data.SqlDbType.Int 23 prmPrice.Size = 18 24 prmPrice.Direction = Data.ParameterDirection.Input 25 26 'Create the connection to the database using web.config 27 dbConnection.ConnectionString = ConfigurationManager.ConnectionStrings("MyDB").ConnectionString 28 29 'Various command settings 30 InsertCommand.CommandText = "INSERT INTO [Orders] ([Quantity], [Product], [OrderDate], [ItemPrice]) VALUES (@Quantity, @Product, {fn NOW()}, @Price)" 31 InsertCommand.CommandType = Data.CommandType.Text 32 InsertCommand.Connection = dbConnection 33 34 'Add the rows to the database 35 For intCounter = 0 To objDT.Rows.Count - 1 36 37 'Re-inserts the data rows from objDT 38 objDR = objDT.Rows(intCounter) 39 40 'Set param values 41 prmQuantity.Value = objDR("Quantity") 42 prmProduct.Value = objDR("Product") 43 prmPrice.Value = objDR("Price") 44 45 'Add params to insert command 46 InsertCommand.Parameters.Add(prmQuantity) 47 InsertCommand.Parameters.Add(prmProduct) 48 InsertCommand.Parameters.Add(prmPrice) 49 50 'Open connection 51 InsertCommand.Connection.Open() 52 53 'Execute the insert command 54 InsertCommand.ExecuteNonQuery() 55 56 'Close connection and clear parameters for the next loop 57 InsertCommand.Connection.Close() 58 InsertCommand.Parameters.Clear() 59 60 Next 61 62 Response.Redirect("done.aspx") 63 64 End Sub 65 What I'd like to do is see if I can instead use a simplified approach based on 2.0 controls. Specifically, I was hoping that I could use an SqlDataSource and simply tap into its built-in Insert capabilities. So I tried this alternate procedure:Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)Dim intCounter As Integer 'Used to count the loopFor intCounter = 0 To objDT.Rows.Count - 1DSOrdersNew.InsertParameters("Product") = objDR("Product")DSOrdersNew.InsertParameters("Quantity") = objDR("Quantity")DSOrdersNew.Insert()NextEnd SubWhen I run this I get an error message saying: "Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'."Am I just way out in left field here? I guess I don't mind doing it the old-fashioned way, but it seems like there ought to be a way to do this. Any suggestions would be appreciated. Thanks!
View Replies !
Table Order When Inserting Data
I have to import data into a empty database, that has many tables.some tables have to be inserted first than others due to the foreignkeys.How do I find out the order of the tables that I have to insert datainto?Thanks in advance!Sam
View Replies !
Inserting Varchar Data To Table...
It appears that when I insert data into a varchar(8000) field, SQL Server truncates everything after the 256th byte. When I change the field to text, this problem is eliminated. Can someone give me an explanation of why? And can I actually to the insert with the field being a varchar(8000) instead of a text data type. This will do wonders for the size and indexing.
View Replies !
Inserting Data Into A Table Using A Query
I want to insert the results of a query into a temp table If exists(select * from sysobjects where id = object_id(N'[TempTable]')) Begin Drop Table DatabaseName.dbo.TempTable End CREATE TABLE DatabaseName.dbo.TempTable( RecordID char (12) NULL, Name char (50) NULL, StreetAddress char (50) NULL, City char (50) NULL, County char (50) NOT NULL, StateProvince char (50) NULL, PostalCode char (9) NULL, Country char (50) NULL
View Replies !
Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
View Replies !
Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
View Replies !
Inserting Data In A Table From Another Table
I have a pb when i transfer data from a table named INCIDENT to a table GI_INCIDENTS.... In the table INCIDENT i have startdate,enddate,starttime,endtime And in the table GI_INCIDENTS i have startdate and enddate -->format yyyy/MM/dd hh:mm:ss.ttt.. INCIDENT is a migrated table from access...Then with a query i transfered datas to GI_INCIDENTS... The pb is in INCIDENT Table, date of beginning incident is (2003/06/18 ) but when i execute my insert query,in the table GI_INCIDENTS, date of beginning incident is (2003/06/06)... So i have 2 days delay in the all colums... INCIDENT-->enddate (2006/11/30) GI_INCIDENT-->enddate(2006/11/28) I don't understand the fact... The query: Insert into GI_INCIDENTS(GIIN_ID,GIIN_STA_INCIDENT,STARTDATE,ENDDATE,GIIN_TYPE,GIIN_RESUME,GIIN_DESCRIPTION,GIIN_IMPACT_ANTENNE,GIIN_INITIATEUR) select NUMINCIDENT,CODETAT,STARTDATE+ ' ' + STARTTIME, ENDDATE + ' ' + ENDTIME,CODETYPE,NATURE,DESINCIDENT,CODBLOQ,NUMEXPL From INCIDENT set IDENTITY_INSERT GI_INCIDENTS off Thanks a lot for your help..
View Replies !
Inserting Data Into A Sybase Temporary Table
I'd like to select data out of an oracle table, and UPDATE a sybase table. So far I've got as far as the following: 1. create a package 2. add 2 connection managers, set retainsameconnection=true on both just to be sure. 3. add an executesql command which creates a temporary table create table #temp_wm ( instru_id int) 4. add a dataflow task with: i) an oledb source with a select statement: "select instru_id from mytable" ii) an oledb destination with open rowset: #temp_wm added an external column called instru_id in the inputs and outputs tab when i run, the create temp table task work, the select works, but the insert into fails. If I change the select statement to: "select instru_id from mytable where 1=0" it all executes fine. So everythings copacetic as long as i don't need to actually insert any real records = brilliant! 1. HAS ANYONE OUTTHERE SUCCESSULLY USED SSIS TO INSERT DATA INTO A SYBASE TEMPORARY TALBE - MAYBE ITS JUST NOT POSSIBLE?? 2. Any idea how I can fix my setup? I'm usign Sybase ASE OLE DB Drivers Note: i also tried ## temp tables, no difference. This is the error output. [OLE DB Destination [255]] Error: An OLE DB error has occurred. Error code: 0x80004005. [OLE DB Destination [255]] Error: The "input "OLE DB Destination Input" (268)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (268)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (255) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
View Replies !
Inserting Data Returned From A Sproc Into A Table
i am writing a sproc that calls another sproc. this 2nd sproc returns 3 or 4 rows. i want to be able to insert these rows into a table. this sproc is inside a cursor, as i have to call it many times. how do i insert the rows that it returns into another table??
View Replies !
Inserting Data From Text File To SQL ME Table
Hello, I have an application taht requires the use of a table. The device that this application works on, has a local memory that does not allow me to insert the 800,000 records that I need. Therefore I have two approaches: 1. To insert less records into my local memory database e.g 40,000 but not row by row, bulk insert is better. How do I do the bulk insert? 2. This is the most prefferable way: To find a way to insert all 800,000 records into a table on the storage card which is 1GB. What do you suggest? Will using threads be helpfull? Any ideas? I use C# from VS 2005, SQL ME, compact framework 2.0 and windows 4.2. Thanks in advance, John.
View Replies !
Need Help Inserting Data Into Table With Sql Insert Into Using Textbox Values
the error message I get is {"Object reference not set to an instance of an object."} and it points to < Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text > this is my code": Protected Sub TickMastBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TickMastBtn.Click REM Collect variablesDim Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text Dim Comp As String = CType(FindControl("CoTextbx"), TextBox).TextDim Exch As String = CType(FindControl("ExchTextbx"), TextBox).Text REM Create connection and command objectsDim cn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataVTRADE.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")Dim cmd As New SqlCommand cmd.Connection = cn REM Build our parameterized insert statementDim sql As New StringBuilder sql.Append("INSERT INTO TickerMaster ")sql.Append("(Ticker,Company,Exchange,) ")sql.Append("VALUES (@Tickr,@Comp,@Exch,)") cmd.CommandText = sql.ToString REM Add parameter values to command REM Parameters used to protect DB from SQL injection attacksWith cmd.Parameters .Add("Tickr", SqlDbType.Int).Value = Tickr.Add("Comp", SqlDbType.VarChar).Value = Comp .Add("Exch", SqlDbType.VarChar).Value = Exch End With REM Now execute the statement cn.Open() cmd.ExecuteNonQuery() cn.Close() End Sub
View Replies !
Default Sort Order Of The Data When Inserting From One Table To Another
I have a data load process that reads data from flat file into a Stage table in sql server. The order of the records in the stage table is exactly same as the order in the flat file. The identity column on the Stage table (which is also the clustered index) represents the exact line/row number of the data in the filat file. I perform some transformations on the data in the stage table and then insert it into a cumulative table which has a clustered index on an identity column again. When I do this, does the order of the data in the cumulative table be in the same order as the data in the stage table? Anyone, please let me know if I can rely on SQL server to maintain the same order or I will be forcing a sort order on the Identity column (clustered index) of the stage table when I insert the data into a cumulative table. Thanks in advance!!
View Replies !
Skip Field Terminator While Inserting Data To A Table-Bulk Insert
Hi, I have a data file which consists of data as below, 4 PPU_FFA7485E0D|| T_GLR_DET_11|| While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table, here is my query iam using to insert the data using bulk insert. BULK INSERT TABLE_NAME FROM FILE_PATH WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''') Can any one help on this. Thanks, -Badri
View Replies !
Error Inserting Image Into SQL Server2000 Table From Pocket PC Application Only When Using Stored Procedure In Table Adapter Wiz
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard. main.ds.MailsSignature.Clear(); main.ds.MailsSignature.AcceptChanges(); string[] signFiles = Directory.GetFiles(Settings.signDirectory); foreach (string signFile in signFiles) { mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow(); mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image. main.ds.MailsSignature.Rows.Add(mailsSignatureRow); } mailsSignatureTableAdapter.Update(main.ds.MailsSignature); But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard. ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image ) AS SET NOCOUNT OFF; INSERT INTO MailsSignature (Singnature) VALUES (@Singnature); SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY()) For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table. Is there any limitation in CF? Regards, Professor Corrie.
View Replies !
Calculating Value From Two Separate Rows In The Same Table And Inserting As New Row In The Same Table
Code Block Hi, I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem: Table Name: metrics_ladder id security_id metric_id value 1 3 80 125.45 2 3 81 548.45 3 3 82 145.14 4 3 83 123.32 6 4 80 453.75 7 4 81 234.23 8 4 82 675.42 . . . Table Name: metric_details id metric_id metric_type_id metric_name 1 80 2 Fiscal Enterprise Value Historic Year 1 2 81 2 Fiscal Enterprise Value Current Fiscal Year 3 82 2 Fiscal Enterprise value Forward Fiscal year 1 4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2 5 101 3 Calendar Enterprise value Historic Year 1 6 102 3 Calendar Enterprise Value Current Fiscal Year 5 103 3 Calendar Enterprise value Forward Year 1 6 104 3 Calendar Enterprise Value Forward Year 2 Table Name: metric_type_details id metric_type_id metric_type_name 1 1 Raw 2 2 Fiscal 3 3 Calendar 4 4 Calculated The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder: id security_id metric_id value 1 3 101 <calculated value> 2 3 102 <calculated value> 3 3 103 <calculated value> 4 3 104 <calculated value> Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81. Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82, metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use). As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes: security_id 80 81 82 83 101 102 ----------- -- -- -- -- -- -- 3 125.45 548.45 145.14 123.32 <calculated value> <calculated value> 4 ... . . . which is then unpivoted. The SQL that achieves this is more or less as follows: ********* START SQL ********* declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...) -- Dummy year variable to make it easier to use MONTH() function -- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc... DECLARE @DUMMY_YEAR VARCHAR(4) SET @DUMMY_YEAR = 1900; with temp(security_id, metric_id, value) as ( select ml.security_id, ml.metric_id, ml.value from metrics_ladder ml where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...) -- only consider securities with fiscal year end not equal to december and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec') ) insert into @calendar_averages select temppivot.security_id -- Net Income ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103] ,NULL as [104] -- Share Holders Equity ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107] ,NULL as [108] -- Capex -- Sales -- Accounts payable etc... .. .. from temp pivot ( sum(value) for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...) ) as temppivot inner join company_details cd on temppivot.security_id = cd.security_id ********* END SQL ********* The result then needs to be unpivoted and stored in metrics_ladder. And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET. Many thanks (if you've read this far!) M.
View Replies !
Need Help Inserting Data In A Table That Already Has Data
I need to create a stored procedure that will insert data with some already exsisting data in a table. The data is in a spreadsheet, my issue is that I dont want to violate the primary key rules. can anyone help please CREATE PROCEDURE InsertTerms AS INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #], [FirstName], [LastName], [SocialSecurityNumber], [DateHired], [Status], [Title], [DepartmentName], [Pictures]) SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS FROM EmployeeGamingLicense AS a WHERE a.STATUS = 'TERMINATED' IF @@Error <> '0' RETURN GO
View Replies !
Inserting Data In Multiple Tables
i want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it? i think; it should be implemented through transaction statements but not much aware about these
View Replies !
Problem Inserting Data Into Onw Of My Tables
i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2 My Problem: It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me. Total Specification Requirements: i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical. Any Help would be graetlty appreciated Kind Regards Carel Greaves
View Replies !
Inserting Data Into Multiple Tables In 1 Transaction
Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
View Replies !
Inserting Data To Tables Belonging To Different Databases
Hi all,we are developing an internal application that involves "Timesheets","Support" and "Project Management".Imagine that there are 3 different databases for the above scenario,under SQL Server 2000.My task is to create one or a few table triggers forINSERT/UPDATE/DELETE operations. For example:- if a row is added in Table A of "Timesheets" database, then Table Bof "Project Management" needs to be updated.The concept is clear i think. The question is how we do the above.Note that I am a new progremmer to SQL Server (I have been dealingwith Oracle so far), and I don't know how we programmatically connectto different database within a trigger, how do we check thepriviledges etc.Can someone help me?ThanksChristos Andronicou
View Replies !
Creating Tables And Automatically Inserting Data-HELP!!
I have created a table with the following columns...Date(datetime),Actual (Int),Planned (Int)I need to insert weekending dates starting from 23/04/04 loopingthru'for the next 52weeks automatically into the date column.Then in the actual and planned colums, I need to insert a count ofsome records in the table.I will appreciate help on a SQL query to achieve this!
View Replies !
Inserting Data Into Multiple Related Tables
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other. What I want to know is: 1)How do you go abouts inserting data into multiple related tables that have constraints on them? 2)Would you use a stored procedure in an instance like this? 3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc Any help would be greatly appreciated! Say for instance I have three related tables. table1 ------ tbl1_id tbl1_data1 tbl1_data2 table2 ------ tbl2_id tbl2_data1 tbl2_data2 table3 ------ tbl3_id tbl3_data1 tbl3_data2 table1 has a one-to-many relationship with table2 table3 has a one-to-one relationship with table2
View Replies !
Problem In Displaying Or Inserting Data Into Sqlserver Tables Using Utf
i have such a error in my sql server db i examined arabic_ci_as and SQL_Latin1_General_CP1256_CI_AS but in my web pages that uses utf-8 codepage that retrieves data using ado and asp scripting the ouput or inserted that dispalyed in both of query analyzer and Enterprise manager replaces or display '?????' for characters . i am using nchar and nvarchar and ntext
View Replies !
Inserting Data Into Two Tables With Parent-child Relationship
I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first? If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database. The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.
View Replies !
RDA Pull Method Not Creating Tables/inserting Data
I can't see what is going on, this is the situation: I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far: I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner. So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code: ------------------------------------------------------------------------------------------------------------------ string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values) string connectionString = "Data Source="\Program Files\client\db\MobileDB.sdf""; SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll", connectionString); IList _tableNames = new ArrayList(); IList _queries = new ArrayList(); ############ Code that prepares tables and queries ############ for (int counter = 0; counter < _tableNames.Count; counter++) { rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError"); } the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.
View Replies !
Inserting Into A Sql Table
VWD 2005 Express. Visual Basic. Sql Server 2005. How may I insert a record into a table from code behind (I am doing this without any entries to a form). The connection name is GoodNews_Extranet. The table is Login. The fields I want to write are UserId and dtTime. Thanks for any help. The insert command is: INSERT INTO [Login] ([SystemUserId], [dtTime]) VALUES (@SystemUserId, @dtTime) I will provide the @ parameters programmatically. I just need to know the commands I should use in Visual Basic to actually execute the INSERT command. Thanks for the help.
View Replies !
Inserting Into More Than 1 Table
Hey all, Quick question for ya. If I wanted to insert 1 value into 1 table and another value into another table, how would I do that with closing connections and reopening and whatnot? I am using Visual Web Dev and SQL server 2005 express. I have been trying to mess around with it but I can't figure it out. Here is the situation. I want to insert a GroupName and GroupDescription that a user fills in in 2 text boxes named GroupNametxt and GroupDescriptiontxt. This data will go into the "Group" table. I then also want to Insert the data UserID (taken from the aspnet ID table), and GroupName from the Grouptxt.text into a GroupMembership table. How would I do all this in 1 button click? I know some basic TSQL but I don't know how to handle the opening and closing of connections and whatnot. Thanks, Chris
View Replies !
Help Inserting XML Into MS SQL Table.
I've got a string value in C# that contains: "<?xml version="1.0"?><?qbxml version="2.0"?><QBXML><QBXMLMsgsRq onError="stopOnError"><JournalEntryAddRq requestID="1"><JournalEntryAdd><TxnDate>2003-11-18</TxnDate><JournalDebitLine><AccountRef> <FullName>1200</FullName> </AccountRef> <Amount>160.06</Amount> <Memo>613663</Memo> <ClassRef> <FullName>Buffalo</FullName> </ClassRef></JournalDebitLine><JournalCreditLine><AccountRef> <FullName>5687</FullName> </AccountRef> <Amount>160.06</Amount> <Memo>613663</Memo> <ClassRef> <FullName>Buffalo</FullName> </ClassRef></JournalCreditLine><JournalDebitLine><AccountRef> <FullName>1200</FullName> </AccountRef> <Amount>133.85</Amount> <Memo>300931</Memo> <ClassRef> <FullName>Buffalo</FullName> </ClassRef></JournalDebitLine><JournalCreditLine><AccountRef> <FullName>5687</FullName> </AccountRef> <Amount>133.85</Amount> <Memo>300931</Memo> <ClassRef> <FullName>Buffalo</FullName> </ClassRef></JournalCreditLine></Journ alEntryAdd></JournalEntryAddRq></QBXMLMsgsRq></QBXML>" I'm passing that string to an insert stored procedure that hits a varchar field (varchar 5000). problem is, nothing is showing up in the database. there are no errors, but the field I have shows nothign in it after the insert. if i run a test and set my string to "this is a test" it goes into the table from the SP_insert no problem. So i'm figuring, somehow the formatting of the XML string in C# isn't kosher with MS SQL. the above text is the exact string value from VS.NET command output (? mystring). I'm really in need of help here guys. I'm trying to log this XML result into a log table for reference if there are ever any problems I can look in the log table @ the xml. but i can't get it to insert. Help!
View Replies !
Inserting Into Table
I have the 3 tables below. I want to be able to add more than one item to a quote. I thought I had designed the tables so that I could have more than one item in a quote....? I've tried to do this: insert into has_quote(date_of_quote,service_desk_contact,category,cat_ref) values('09/12/2002', 'me',2,'C92') where quote_id = '1' but it doesn't work. Am I totally missing something here? Code: CREATE TABLE Item ( cat_ref VARCHAR(5)PRIMARY KEY, descrip VARCHAR(50), date_added SMALLDATETIME, date_last_pricecheck SMALLDATETIME, cat_type VARCHAR(20), contract VARCHAR(10), cost_price SMALLMONEY, installation_charge SMALLMONEY, commercial_markup SMALLMONEY, supplier_name VARCHAR(20), supplier_phone VARCHAR(20), notes VARCHAR(500)) CREATE TABLE has_quote ( quote_id INT IDENTITY (1,1) PRIMARY KEY, date_of_quote SMALLDATETIME, service_desk_contact VARCHAR(20), category INTEGER, cat_ref VARCHAR(5)FOREIGN KEY REFERENCES Item(cat_ref), first_name VARCHAR(10), surname VARCHAR(10), customer_phone VARCHAR(20), FOREIGN KEY (first_name, surname, customer_phone) REFERENCES Customer(first_name, surname, customer_phone) ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE Customer ( first_name VARCHAR(10), surname VARCHAR(10), customer_phone VARCHAR(20), contract VARCHAR(10), location VARCHAR(20), email VARCHAR(50), cust_id INT IDENTITY (1,1), PRIMARY KEY (first_name, surname, customer_phone))
View Replies !
|
TRACKER
