Extracting Specific Values From One Table And Insert Them Into Another
Jun 3, 2008
Hello. I have a somwhat simple question.
I have a table inside my database where i have some columns. Id like to extract distinct values from one column and inser them into another. My table is named article and id like to slect all the destinct values from the column naned type inside my article table. Then i need to insert my values into a new table called type but i would also like the to have 2 columns inside my type table. 1 called counter witch is an auto increment PK, and another one named type where the results from my query would be inserted.
Iv tried a veriety of querys but none of them have managed to do this.
My data source has some columns I have to 'translate' first and then insert into my destination table.
Example Source data: key size height 1 'Small' 'Tall' 2 'Big' 'Short' has to become 1 'Y' 'P' 2 'N' 'D'
I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest Example: col_name vl_source vl_dest size 'Small' 'Y' size 'Big' 'N' height 'Tall' 'P' ... and so on, I believe you get the point
How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Goal is to get the data after the 2<sup>nd</sup> ; until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them
Table 1 - VendorTransactions
ID PARTY AMOUNT VOUCHER --------------------------------------- 1 A 5000 Purchase 2 B 3000 Purchase 3 C 2000 Purchase
4 A 3000 Payment 5 B 1000 Payment 6 C 2000 Payment 7 A 1000 Payment
Now we have a blank table Table 2 - Liabilities
ID PARTY AMOUNT
I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below
ID PARTY AMOUNT ------------------------- 1 A 1000 2 B 2000 3 C 0
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
I need Insert rows in the OrderDetails Table based on values in the Orders Table
In the Orders table i have a columns called OrderID and ISale. In the OrdersDetails i have columns called OrderID and SaleType
For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD. That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD
Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.
I'm trying to import some data from a spreadsheet to a database table from a package in integration services. The problem is that I see the data when I open the excel file but when I try to run my package , it doesn't insert any rows in the table and it finishes with a success status.
My excel file has some formulas to get the data from other worksheets. I added a Data Viewer and all I see is null values in every cell.
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
I have a table as dbo.Bewegungen and it will be updated every day with new dates
I inserted once the values of this table to another table with group by. I want to know how should i insert the new values every day to the new table? i don't want to insert the previous values again.
it has 5 columns as primary key and one of them is Date.
If I have a column in one table (contracts) that contains a set of values (codes that identify the fields contract code) like as follows...
Code:
Contracts table -------------------------------- Concode - description -------------------------------- KIDD - Kidderminster General UNIV - University Hospitals
and then another table (controls) which has a column called contracts where the data within the 'Contracts' field is set out like the following (yes that is right, each of the data in this column are the Foreign Keys which are separated by '/' in which I need to query against the contracts table ) ...
With the those tables and the columns like they are, how can I get my query to display the following information...
PHP Code:
--------------------------------------------------- Con(Controls table) - Description(contracts table) --------------------------------------------------- BA - Kidderminster General BA - University Hospitals ---------------------------------------------------
I'm guessing my first job is to somehow extract those Foreign Keys from the 'Contracts' column in the 'Control' table
I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension.
My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.
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
Hello,I have 3 tableTable 1 : list of "whatever" programTable 2: list of tasks for each programTable 3: list of user for each taskWhen I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.Is there a way to do that in sql?I do not really want to go through each single task, then copy it with the new program, then get the @@identity of the inserted task and then assign the same user to the newly inserted task.Thanks
What is the easiest way to insert a new row in a existing sql table through web developer.net (visual basic)? E.g. a database called Names and the columns Firstname and Surname and you want to insert "anna","johnsson"?
I have a trouble getting the value for a field from the insert table, well, this is the problem:
I have to make a string from the insert command over the table, e.g.: 'insert into demo(campo) values('hello')' this string have 2 parts: a) 'insert into demo(campo) values(' ---> I already have it , but the second, the core of the problem, I can't get it
this is my code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Marco Antonio García León
-- Create date: 28.04.2008
-- Description: Trigger para INSERT
-- =============================================
ALTER TRIGGER [ti_Prueba]
ON [dbo].[PRUEBA] FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @MyCursor CURSOR --Cursor donde se aloja la estructura
DECLARE @cmd nVARCHAR(1000) --Comando
DECLARE @Cadena nVARCHAR(4000) --Cadena SQL a ejecutar
DECLARE @Campo AS nVarchar(1024) --Nombre del Campo
DECLARE @Tipo AS Integer --Tipo del campo
DECLARE @Valor AS nvarchar(1024) --Valor del campo
DECLARE @Cadena_Valor AS varchar(1000)
DECLARE NewRow CURSOR FOR
SELECT * FROM Inserted
DECLARE cursorStruc CURSOR FOR
SELECT SysColumns.Name, SysColumns.xType
FROM SysObjects JOIN
SysColumns ON(SysObjects.ID=SysColumns.ID)
WHERE SysObjects.Name LIKE 'prueba'
SET @MyCursor = cursorStruc
SET @Cadena = 'INSERT INTO Prueba('
SET @Cadena_Valor = 'VALORES: '
print 'Cargando la data al temporal';
select * into ##tempo from inserted;
-- Insert statements for trigger here
OPEN cursorStruc
-- Perform the first fetch.
FETCH NEXT FROM cursorStruc INTO @Campo, @Tipo
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @cCadena = (select cursorStruc.Name from cursorStruc)
Hello, my problem is that I have 2 textboxes and when the user writes somthing and clicks the submit button I want these values to be stored in a database table. I am using the following code but nothing seems to hapen. Do I have a problem with the Query (Insert)??? Or do I miss something else. Please respond as I can't find this. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Manufacturer2.aspx.cs" Inherits="Manufacturer2" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"><title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Name"></asp:Label> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <asp:Label ID="Label2" runat="server" Text="Password"></asp:Label> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <br /> <br /> <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@TextBox1, @TextBox2)"> <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="TextBox1" PropertyName="Text" /> <asp:ControlParameter ControlID="TextBox2" Name="TextBox2" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource>
Hi, I've got a table with trialsubscriptions. When someone orders a trialsubscription he has to fill in a form. With the values in the form I like to fill a database with some extra fields from the trialsubscription table. I get those extra fields with a datareader. But when I insert I can't use the same datareader (its an insert), I can't make two datareaders because I have to close the first one etc. Does someone had the same problem and has some example code (or make some :-)) Some keywords are also possible! Thanks! Roel
hello everyone , i have a table named "Employee" with EmpID as PK. i want to insert EmpID one by one in another table named "AssignedComplaints" so if i have all the EmpID inserted in "AssignedComplaints" table then on next insert operation , the first EmpID will be inserted and then second on so on. like i gave u a sample i have three EmpIDs in "Employee" table named M1,M2,M3 first M1 is inserted secondly M2 and lastly M3. now i have all EmpID in "AssignedCompalints" table. now if i do want to insert again then the whole process will repeat , first M1 will be inserted secondly M2 and so on. i need the query i have created a trigger and will use this query in that trigger. thanks
Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?
SPbob INsert into b values(1,1,2,2) select 0
(this is a cut up version of the sp just to show about the way it is formated) Please help.
Hi i am trying to create an insert statement that will insert rows into a table based on the information in the table already. the table looks like this
INSERT Into table1(groupid,field1,field2) select -1,@passedvalue,field2 from table1 where field1 = @passedvalue1
assume @passedvalue = 700, @passwedvalue1 = 100 Now this is fine however i cannot have a duplicate key (key is comibantion of all 3 fields) thus the first time this runs it works however if it runs again it fails - how can i change the where clause to ignore rows that already exist? eg if @passedvalue = 300 and passedvalue1 = 500
I am using the Database is Oracle SQL Developer, here the Requirement is like Before insert the values in Database I need to remove the Database table and insert new values.
DELETE FROM XXMBB_NOSVOS_TMP_VO_NO WHERE EFFECTIVE_DATE BETWEEN '01' || '-' || TO_CHAR(to_date(:N_Date,'DD-MM-YY'),'MON-YY') AND to_date(:N_Date,'DD-MM-YY') AND LEDGER_ID = LED and name_rdf='NOSVOS'; COMMIT; DELETE FROM XXMBB_NOSVOS_BAL_TMP_VO_NO WHERE PERIOD_NAME = TO_CHAR(add_months(to_date(:N_Date,'DD-MM-YY'),-1),'MON-YY')
