Possible To Update A Table Based On Excel?
May 14, 2006
Hi all
I was wondering if it was possible to update a table based off of information from Excel. here is what I though would have worked.
update MyTest Set acctNumber='111' FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') where [ProductGroup]='Hal Butts'
with 'Update MyTest' being the table name. It does have the same name as the excel file. Just to rule that out.
It gives me this error
Ambiguous column name 'ProductGroup'.
If it is possible what is the correct syntax??
tibor
View 7 Replies
ADVERTISEMENT
Jun 14, 2006
l've the following situation,
l've some excel files controlled by Vendor which changing frequently. The only thing does not change is the header name of each column.
So my question is, is there any way to create a new table based on the excel file selected including the column name in SSIS? So that l can use the data reader as source to select those columns l am interested on and start the integration.
Thanks.
Regards,
Yong Boon, Lim
p/s : The excel header is at the row 7.
View 3 Replies
View Related
Dec 2, 2015
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
Note: ROW is Partition By ColumnA,ColumnB
TableName: PromotionList
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
Expected outcome after performing update on the table
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
View 4 Replies
View Related
Jan 22, 2008
Hello Friends,
I have two tables, And also I have Sample data in them.
create table X
(y int,
m int,
v int)
insert into X select 2007,1,5
insert into X select 2007,1,3
insert into X select 2007,2,9
insert into X select 2007,2,1
select * from X
Create table Y
(fy int,
fm int,
v int)
insert into Y select 2007,1,0
insert into Y select 2007,2,0
insert into Y select 2007,3,0
select * from X
select * from Y
I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m
Using temporary table cannot be done.
Thanks in Advance,
Babz
View 1 Replies
View Related
Nov 17, 2015
I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
View 4 Replies
View Related
Oct 13, 2005
Here is my situation;I have two tables in a MS-SQL DB. One table with dollar amounts and servicecodes. I have a second table that I want to move some information into fromthe first table. The catch is I want to move one field as is from the firsttable to the second, but the rest of the fields in the second table arecalculations based on fields in the first table.The first table is called XFILE. It has fields SVCCODE, PRICE, DWAGES,DMATLS, etc. The second table has the same field names and I want to movethe SVCCODE from XFILE to Cost_Percent with no changes. For DWAGES in theCost_Percent table I want to do the following calculation;[ XFILE.DWAGE] divided by [XFILE.PRICE] and put the results in Cost_Percenttable DWAGES fieldSo basically I am putting a percent in the Cost_Percent table. I can movethe data from one table to another ok, but I can not figure out how to writethe query in the Query Analyzer to do this.I am ruining SQL2000 Standard on a Win2K3 server. I am using Query Analyzerand SQL Enterprise Manager from an XP-Pro WS.I have looked in the 'Books On-Line' for the answer but I sort of new to SQLand can't find the answer that I am sure is staring me in the face.Thanks in advance for any help.Mike Charneym charney at dunlap hospital dot org
View 5 Replies
View Related
Dec 15, 2005
Guys - sorry for the long post - hope it's clear
DDL/DML below
I want to update the startdate column (for all rows) so that when period is 0 then the new value
is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the
enddate column for the row of the previous column (with the same freq)
ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq
create table #periods (period int , startdate datetime , [enddate] datetime , freq int)
insert #periods ( period , startdate , enddate , freq)
select 0 , '01-Jan-1900' , '31-Jan-2001' , 1
union all
select 1 , '01-Jan-1900' , '28-Feb-2001' , 1
union all
select 2 , '01-Jan-1900' , '31-Mar-2001' , 1
union all
select 3 , '01-Jan-1900' , '30-Apr-2001' , 1
union all
select 4 , '01-Jan-1900' , '31-May-2001' , 1
union all
select 0 , '01-Jan-1900' , '31-Jan-2002' , 3
union all
select 1 , '01-Jan-1900' , '28-Feb-2002' , 3
union all
select 2 , '01-Jan-1900' , '31-Mar-2002' , 3
union all
select 3 , '01-Jan-1900' , '30-Apr-2002' , 3
union all
select 4 , '01-Jan-1900' , '31-May-2002' , 3
select * from #periods -- gives
periodstartendfreq
01900-01-01 00:00:00.0002001-01-31 00:00:00.0001
11900-01-01 00:00:00.0002001-02-28 00:00:00.0001
21900-01-01 00:00:00.0002001-03-31 00:00:00.0001
31900-01-01 00:00:00.0002001-04-30 00:00:00.0001
41900-01-01 00:00:00.0002001-05-31 00:00:00.0001
01900-01-01 00:00:00.0002002-01-31 00:00:00.0003
11900-01-01 00:00:00.0002002-02-28 00:00:00.0003
21900-01-01 00:00:00.0002002-03-31 00:00:00.0003
31900-01-01 00:00:00.0002002-04-30 00:00:00.0003
41900-01-01 00:00:00.0002002-05-31 00:00:00.0003
Desired result
select * from #periods -- gives
periodstartendfreq
02000-12-01 00:00:00.0002001-01-31 00:00:00.0001
12001-01-31 00:00:00.0002001-02-28 00:00:00.0001
22001-02-28 00:00:00.0002001-03-31 00:00:00.0001
32001-03-31 00:00:00.0002001-04-30 00:00:00.0001
42001-04-30 00:00:00.0002001-05-31 00:00:00.0001
02000-12-01 00:00:00.0002002-01-31 00:00:00.0003
12002-01-31 00:00:00.0002002-02-28 00:00:00.0003
22002-02-28 00:00:00.0002002-03-31 00:00:00.0003
32002-03-31 00:00:00.0002002-04-30 00:00:00.0003
42002-04-30 00:00:00.0002002-05-31 00:00:00.0003
/*
I know I need a case statement to test for column 0 and to join the table on itself and have put something together
but it fails for column 0 and updates to NULL - I think it must be to do with the join ??
This is what I've got so far :
UPDATE PA1
SET
PA1.Startdate =
CASE
WHEN PA2.period = 0
THEN
2000-12-01 00:00:00.000
ELSE
PA1.Enddate
END
FROM #periods AS PA1
JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1
Any help gratefully received as always
*/
View 5 Replies
View Related
Jul 9, 2007
I have to update dates column based on other table
1)MFGDt date which is a column in PRODWIN table should be before awaredt(say between 1 to 3 months)
which is in table incidents
2)Expirydt date from prodwin table should be after awaredt (incidents table)(say between 2 or 3 months)
UPDATE prodwin
SET expirydt =awaredt + DATEPART(hour, invcompleteddt)%5 + 1
It is giving me this error because not in the same table "Invalid column name"
3)prodrecddt should be after AWAREDT and before Invcomleteddt (incidents table)
View 8 Replies
View Related
Feb 20, 2001
hi, I have two tables named state and state2, please click on the link to view both tables. I want to update the second table by inserting the
state from table one. I tried to make it but failed,I would appreciate your
help.
http://66.61.28.119/test/state.asp
update state2
set state = (select code from state1 where id= select id from state1 where...I do not know how
where id =(select id from state.....I do not know how
anyway, I appreciate your help.
Ali
View 1 Replies
View Related
Sep 2, 2005
Hi, I have two tables. I want to update two columns in my first table,[ADD_BSL_SALES] and [ADD_BSL_COST] with two values [Sales] and[Costs] held in my #temp table but based on a RUN_DATE from my firsttable.Can anyone point me in the right direction?Thanks in Advance �?�BryanCREATE TABLE [GROSMARG_AUDIT_ADDITION] ([RUN_DATE] [datetime] NULL ,[SALES_DIFF] [numeric](19, 6) NULL ,[COST_DIFF] [numeric](19, 6) NULL ,[ADD_BSL_SALES] [numeric](18, 0) NULL ,[ADD_BSL_COST] [numeric](18, 0) NULL ,[ADD_SALES_DIFF] [numeric](18, 0) NULL ,[ADD_COST_DIFF] [numeric](18, 0) NULL) ON [PRIMARY]GOINSERT RUN_DATE,datetime,INSERT SALES_DIFF,numeric(19,6),INSERT COST_DIFF,numeric(19,6)INSERT ADD_BSL_SALES,numeric(18,0),INSERT ADD_BSL_COST,numeric(18,0),INSERT ADD_SALES_DIFF,numeric(18,0)INSERT ADD_COST_DIFF,numeric(18,0)--- Second TableCREATE TABLE #DUPTOTALS[Sales][Costs]
View 1 Replies
View Related
May 9, 2007
***** SQL Server 2005 ********
I have a table that needs to be updated with a sequential number based on criteria.
I am trying to update the SeqID and LinkSeqID with the same sequential number if the ProductID and StoreID are in the same group. For instance the 1st three rows below are in the same group 752534 and 4, therefore the SeqID and LinkSeqID should be 1,2,3 and restart at 1 once the grouping of ProductID and StoreID changes. Please look at the examples below.
SALES Table as IS:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1
752534 4 2
752534 4 3
896784 2 4
896784 2 5
896784 4 6
898874 2 7
898968 2 8
This is what the table should look like after the update in complete.
SALES after UPDATE:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1 1 1
752534 4 2 2 2
752534 4 3 3 3
896784 2 4 1 1
896784 2 5 2 2
896784 4 6 1 1
898874 2 7 1 1
898968 2 8 1 1
Can anyone HELP me please?
View 4 Replies
View Related
Oct 18, 2014
I have the following table
Code:
10012014-09-01 00:00:00.000BH1-Z-1280180
20012014-09-01 00:00:00.000BH1-Z-9990300
30012014-09-01 00:00:00.000CHO1-Z-1280180
40012014-09-01 00:00:00.000CHO1-Z-9990306
50012014-09-01 00:00:00.000OT11-Z-99906
60012014-09-01 00:00:00.000WRK1-Z-1280180
70012014-09-01 00:00:00.000WRK1-Z-9990306
80022014-09-01 00:00:00.000BH1-Z-0800480
90022014-09-01 00:00:00.000CHO1-Z-0800480
100022014-09-01 00:00:00.000WRK1-Z-0800480
110022014-09-02 00:00:00.000BH1-Z-0800480
120022014-09-02 00:00:00.000CHO1-Z-0800600
130022014-09-02 00:00:00.000OT11-Z-0800120
140022014-09-02 00:00:00.000WRK1-Z-0800600
150012014-09-02 00:00:00.000BH1-Z-1280480
160012014-09-02 00:00:00.000CHO1-Z-1280480
What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.
EG for 2014-09-01 for empref 001 the result would be
Code:
0012014-09-01 00:00:00.000BH1-Z-12837.037180
0012014-09-01 00:00:00.000BH1-Z-99961.728300
0012014-09-01 00:00:00.000CHO1-Z-12837.037180
0012014-09-01 00:00:00.000CHO1-Z-99962.963306
0012014-09-01 00:00:00.000OT11-Z-9991.2356
0012014-09-01 00:00:00.000WRK1-Z-12837.037180
0012014-09-01 00:00:00.000WRK1-Z-99962.963306
IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)
I can write a query to do this individually but how can I so this as a query for the full table.
Code:
declare @@total as float
set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate
View 2 Replies
View Related
Apr 8, 2008
I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".
ID LOC CODE
-- ----- ------
11 MAIN B
11 INT A
22 MAIN C
22 INT A
I want the result to be:
ID LOC CODE
-- ----- ------
11 MAIN B
11 INT IB
22 MAIN C
22 INT IC
I am stumped as to how to do this - any help or advice would be appreciated.
The only thing I've come up with is:
UPDATE S
SET s.code = B.code
FROM tbltest B
LEFT OUTER JOIN tbltest S ON B.id = S.id
WHERE (S.loc = 'INT')
But when I run it, it says "0 rows affected".
View 5 Replies
View Related
Oct 18, 2014
I have the following table
10012014-09-01 00:00:00.000BH1-Z-1280180
20012014-09-01 00:00:00.000BH1-Z-9990300
30012014-09-01 00:00:00.000CHO1-Z-1280180
40012014-09-01 00:00:00.000CHO1-Z-9990306
50012014-09-01 00:00:00.000OT11-Z-99906
[Code] ....
What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.
EG for 2014-09-01 for empref 001 the result would be
0012014-09-01 00:00:00.000BH1-Z-12837.037180
0012014-09-01 00:00:00.000BH1-Z-99961.728300
0012014-09-01 00:00:00.000CHO1-Z-12837.037180
0012014-09-01 00:00:00.000CHO1-Z-99962.963306
0012014-09-01 00:00:00.000OT11-Z-9991.2356
0012014-09-01 00:00:00.000WRK1-Z-12837.037180
0012014-09-01 00:00:00.000WRK1-Z-99962.963306
IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)
I can write a query to do this individually but how can I so this as a query for the full table.
declare @@total as float
set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate
View 1 Replies
View Related
Jul 20, 2005
Hello,I have 2 ways of updating data I'm using often1) via a cursor on TABLE1 update fields in TABLE22) via an some of variables ...SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVARUPDATE TABLE2SET FLDx = @var1, FLDy = @var2WHERE ...Now I have a system with 2 databases and I need to update table DB2.TABbased on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,I thought it would be much easier to get the required data immediately fromDB1.TAB in the update-statement of DB2.TAB ... but the way to do thatconfuses me. I've checked books online and a lot of newsgrouppostingsgiving good information but still I keep getting errors like this ...The column prefix 'x.ADS' does not match with a table name or alias nameused in the query.while executing the following statement ...UPDATE DB2.dbo.TABSETFLD1 = x.FLD1,FLD2 = x.FLD2,...FROM DB1.dbo.TAB x, DB2.dbo.ADSWHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =@InputParameterSo in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 oftable DB1.TABAND I only want to update the row in DB2.TAB with the unique keyfieldIDOFTAB2 equal to variable @InputParameterDo you see what I'm doing wrong?--Thank you,Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
View 8 Replies
View Related
Sep 7, 2015
I have table A with colums ID and Product. I have table B with ID (same as table A) and revenue of product.
I need to update the field Product of table A with the revenue of table B.
I'm confuse with the update joining both tables. I tried this, but obviously has an error:
Update A set Product=B.Revenue where A.ID=B.ID
View 6 Replies
View Related
Feb 15, 2005
Hi all,
I have two tables (staging and Cdate) and neither objects has any constraints.
staging table has ID, date, A, B, and C fields and Cdate has id,date and day fields. I need to update/insert date from Vdate into staging where staging ID=' ' and date is null
Here is the code I wrote, however, it seemed the information was updated to one date only instead of time series - Cdate contains time series in column date.
Anyone can help to fix it? Thank you for the help!
update s
set s.date=c.date
FROM cdate c join staging s on(s.id=c.id)
Where s.date is null and id=2
View 3 Replies
View Related
Sep 26, 2007
Hello again,
I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.
Code Snippet
CREATE TABLE #Assignment
(CallID INT IDENTITY(1500,1) PRIMARY KEY,
AssignmentGroup VARCHAR(25),
Assignee VARCHAR(25)
)
GO
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Jim Smith')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Mickey Mouse')
GO
SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee
FROM #Assignment
GROUP BY AssignmentGroup, Assignee
ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee
What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.
So, the logic for the trigger would be
UPDATE #Assignment
SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))
But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.
Does any one have an idea or pointers as to how I should go about this?
Grateful for any advice, thanks
matt
View 5 Replies
View Related
Feb 13, 2014
I have a table dbo.Sales that contains all sales records. There is a column in that table called ItemNumber that I'd like to match with ItemNumber in a flat file and update the ItemCost based on the ItemCost column in the flat file.
So while there will be many sales records for each ItemNumber, I need to loop through and update the ItemCost in that sales record based on the corresponding ItemCost in the flat file. Does this make sense? I really need this for court and I can't figure out how to do it. I took a SQL course about 7 years ago but have forgotten everything.
Database Name: BTData
Database Table: dbo.Sales
Database Columns: ItemNumber (match on this), ItemCost (update this)
FlatFile Name: InventoryCosts.txt
FlatFile Columns: ItemNumber, ItemCost
There will be many sales records for each ItemNumber in the database table. I need to update each one with correct cost based on the item number and cost mapping from flat file.
View 1 Replies
View Related
Oct 27, 2015
I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work
DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000
[code]...
View 8 Replies
View Related
Sep 15, 2015
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cd permit_nbr reference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008
View 3 Replies
View Related
Jun 17, 2005
Can anyone tell me how to update a sql server table with the data from excel sheet? That would be very helpful.Thanks.
View 6 Replies
View Related
Oct 20, 2012
I use sql 2000. I have an excel table containing some fields with the first field named code, the same table with more fields exits in my sql database, the issue:
I want to UPDATE all of the fields in sql-db that matches the ones in the excel file where code of the record matches, in sql it sorta look like this:
update mytable (Fields 2, 3, 4,... till last one /the first is the code field/)
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=C:Book1.xls','select * from [Sheet1$]')
WHERE mytable.code = myexcel.code
View 6 Replies
View Related
Jul 23, 2005
I had previously posted this in an Access forumwith negative results so will try here.Although this question specifies an Access database,I also wish to accomplish this with a large MS SQL Serverdatabase that we have.Question follows:The following SQL statement, used in VBScript,will COPY a table from Excel to an Access mdb.SQL = "SELECT * INTO C1R0" & _" FROM [C1R0$] IN ''" & _" 'Excel 8.0;database=c:excelUpdateFinal1.xls';"What is the SQL statement that willUPDATE an already existing Access tablewith all rows from Excel spreadsheet?The columns of both Spreadsheet and database are thesame.ThanksJim
View 12 Replies
View Related
Jun 29, 2007
I have two tables.First-----RollNo Number(PK) Appno Number Second-------RollNo NumberAppno_1 NumberNow I want to update "Second" table's "Appno_1" field with the "Appno" field of the "First" table and the "RollNO" of "First" table should match with the "Second" table "RollNo" field.Actually both the tables are different .but by mistake i have entered some blank data in second table .So i want to update the second table.Plz Help...............How i will i do it :Plz Help-----------------------
View 2 Replies
View Related
Feb 20, 2001
hi, I have two tables named state and state2, please click on the link to view both tables. I want to update the second table by inserting the
state from table one. I tried to make it but failed,I would appreciate your
help.
http://66.61.28.119/test/state.asp
update state2
set state = (select code from state1 where id= select id from state1 where...I do not know how
where id =(select id from state.....I do not know how
anyway, I appreciate your help.
Ali
View 1 Replies
View Related
Sep 11, 2013
I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql
App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB
OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag
App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null
App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE
View 5 Replies
View Related
Mar 20, 2008
I cant seem to reference columns within a linked Excel server
using MS SQL express
Code:
UPDATE dbo.Items
SET CCC=XL_SERVER.CCC
FROM XL_SERVER...sheet1$
WHERE BBB LIKE '7%'
i.e I want to update column CCC in my Items table from an Excel table column CCC where column BBB begins with 7
I have set up XL_SERVER correctly.
Help!
View 3 Replies
View Related
Aug 29, 2006
Hi all,
I've had little success gooling/searching for this (so far).
Given a simple spreadsheet:
StoreNumber StoreName
1
UPDStoreName_1
2
UPDStoreName_2
3
UPDStoreName_3
4
NEWStoreName_4
I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255))
StoreNumber StoreName
1
StoreName_1
2
StoreName_2
3
StoreName_3
5 StoreName_5
.. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. :
StoreNumber StoreName
1
UPDStoreName_1
2
UPDStoreName_2
3
UPDStoreName_3
4
NEWStoreName_4
5 StoreName_5
(the UPD and NEW are added to simplify the example).
Now the default action of an excel source into an ole db destination is
an insert into the table - so PK constraints causes failures.
Now, given that the table is referred to by other table, and is in a
24x7 website, how do I change the SSIS package such that, on a
row-by-row basis, anUpSert (update or insert) is performed?
The only idea I have so far is:
create temp table
insert excel data into temp table
iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP
Isn't there a better way?
View 7 Replies
View Related
Jul 31, 2015
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
View 5 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Jul 9, 2006
Every month a client sends a spreadsheet with data which we use to update matching rows in a table in the database. I want to automate this using a DTS package but am having quite a bit of trouble accomplishing what I think should be trivial task. I've been attempting to use a Transform Data Task with a modification lookup but I just keep inserting the rows from the source excel spreadsheet in to the existing destination table without ever modifying the existing data.
Any guidance would be greatly appreciated as to a best practice approach.
View 3 Replies
View Related
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
