Import Hadoop Data into Analysis Services Tabular | Mentioned in the article is an example of using openquery to perform select statement on a Hive table through a linkedserver.
I was wondering how can i also perform an insert statement with openquery?
I understand that for example to insert into Hive is to use a Load command, like:
Code:
load data inpath '/tmp/data.csv' overwrite into table tableA;
How do i execute this with openquery? I've tried the example below and some slight variations but all I get in return were syntax errors
Code:
insert openquery (linkedserverName, 'load data inpath '/tmp/data.csv' overwrite into table tableA;')
I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK) Select * From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
The error that I get is the LA1 column cant be found? any help thanks
I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK) Select * From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF, 'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF, 'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
select DEPTNUMB,DEPTNAME from openquery(m1db2u,"select DEPTNUMB,DEPTNAME from Q.ORG where DEPTNAME=@deptname")
Server: Msg 7399, Level 16, State 1, Line 4 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0206N "@DEPTNAME " is not a column in an inserted table, updated table, or any table identified in a FROM clause or is not a valid transition variable for the subject table of a trigger. SQLSTATE=42703 ]
I am trying to use Bulk Insert for a user that is not sysadmin.I have already set up the user as a member of "bulkadmin".When I run the following script:DECLARE @SQLVARCHAR(1000)CREATE TABLE amdbo.#temp ([id] [varchar] (10) NULL,[fld2] [varchar] (10) NULL,[fld3] [varchar] (10) NULL)set @SQL ='BULK INSERT amdbo.#tempFROM ''F: est.txt''WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR= '''')'EXEC (@SQL)select * from #tempI still get the message ...Server: Msg 8104, Level 16, State 2, Line 1The current user is not the database or object owner of table '#temp'.Cannot perform SET operation.Anyone have an idea what I am doing wrong?Drew.
Running above code would give me the following error:
OLE DB provider "SQLNCLI" for linked server "eurodata3" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "eurodata3" could not INSERT INTO table "[SQLNCLI]" because of column "xmlCol". The user did not have permission to write to the column.
Location: memilb.cpp:1493
Expression: (*ppilb)->m_cRef == 0
SPID: 59
Process ID: 1660
Don't be misled by permission statement. I could successfully run similar query as long as the column is not xml on remote server. I would appreciate some ideas to get around with it.
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
I was wondering if any SQL people out there know if i can INSERT INTO 2 tables at the same time? I havent found a yes to the question so i thought i would throw it out there and see what everyone has to say. word
I have put together the following code, but am getting a syntax error on the Insert Into statement. Could someone kindly tell me what I am doing wrong? Thanks 1 Protected Sub SubmitButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton2.Click2 authAndSaveToDB()3 End Sub4 Private Sub authAndSaveToDB()5 On Error GoTo errhand6 If connectToDB() Then 'connection to database was successful7 'insert candidate in the database 8 insertCandidate(ddlPositions.Text, txtName.Text, txtBio.Text, conn)9 Response.Redirect("FileUpload.aspx")10 11 Else 12 Response.Write("<font color = red><strong>Database connection failure. Please try again in a few minutes or contact system administrator.</strong></font>")13 End If 14 closeDB() 'end the connection to the database 15 Exit Sub 16 errhand:17 Response.Write("<br>ERROR: " & Err.Description & "<br>")18 End Sub19 Private Sub insertCandidate(ByVal strPositions As String, ByVal strCandidate As String, ByVal strBiography As String, ByVal myconn As OleDb.OleDbConnection)20 Dim cmd As OleDb.OleDbCommand21 Dim strSQL As String 22 23 strSQL = "INSERT INTO Position (Positions,Candidate,Biography) VALUES ('" & _ 24 strPositions & "'," & _ 25 "'" & strCandidate & "'," & _ 26 "'" & strBiography & "')" 27 cmd = New OleDb.OleDbCommand(strSQL, myconn)28 cmd.ExecuteNonQuery()29 End Sub30 End Class
Hi, I am retriving the value time from my database and i have the convert function on it CONVERT (varchar, Time, 8) When i edit this and try and insert it back to the database i am losing the information. I am not sure how the insert statement shoulld read in order to do this. Thanks Mike
Hi forum, currently I insert a mobile phone number into colomn OK but what I need to do is add +61 as a prefix.Users provide 055 555 555, I want to store number as +61 55 555 555 (note this also drops the 0) my SQL skills a little lacking so appreciate all good info, cheers P
I am having difficulties with my first hand coded insert statement. The record inserts BUT the first item VALUE is selected for all the drop down lists ( 3 of them are optional) I have Prerenders on the page to insert a null value at the top of the list. For the dropdown that is mandatory it only enter first item. Thats without specifying type. As soon as I specify type I get - Input string not in correct format - doesn't actually tell me which one!!!!!! All char types match the database.
Here is my insert:
Sub Add_Click(sender as object, e as EventArgs)
Dim conBooks As SqlConnection Dim strInsert As String Dim cmdInsert As SqlCommand
Hi All, I am trying to insert data into table1 by getting data from table2, table3 and looking whether the data is already not exist in table1. Here is my code... insert into table1 (first_name, last_name, user_login, email, organization_name) values (select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3 wepsUsers where ru.id = wepsUsers.user_id and not exists (select user_login from table1 where user_login = ru.UserID))
When I excute the code, I am getting the following error..... Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'select'.Server: Msg 170, Level 15, State 1, Line 7Line 7: Incorrect syntax near ')'. But, If execute the following code.... select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3 wepsUsers where ru.id = wepsUsers.user_id and not exists (select user_login from table1 where user_login = ru.UserID).... I am able to see the values.
Can any one shade on my code? and please let me know, where I am doing wrong. I am appreciate your help.
Is there anyway that we can use insert into statement to insert data into the first row of a table, or initialize the column with the new inserted data.
I am trying to do an insert in a particular View...one of the field has got an 'Apostrophe'.(Ex: SQl Server's). When i do this i get an error. the system does not allow me to insert.... is there any way i can do an Insert with that...???
How do I 1.) take two seperate columns and combine the data 2.) take that combined data and populate a third column.
Here is an example of what I've been trying to do:
Insert INTO Table_1 (Column1) Select Column2 + Column3 From Table_1
I continue to get this error message:
Server: Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'Column_2', table 'DB1.dbo.Table_1'; column does not allow nulls. INSERT fails. The statement has been terminated.
If some one could please give me a general script that could do this I would appreciate it. I'm new to using SQL and I'm a little bit lost.
I need an insert statement that will grab the maximum of TimeGenerated from the Events table and insert it into LastEventTime in the Host Table. But it has to be inserted based on the relationship of SystemNetName.
This is what I had so far but didn't work: Insert into host(LastEventTime) Select(Max(TimeGenerated) ) from Events Where Events.SystemNetName = Host.SystemNetName
Is is possible to use a Where Clause in a Insert statement in MS SQL? I keep getting a syntax error near 'Where'. Is there something wrong with this?
Insert INTO CPU (CPUNumber, Name, MaxClockSpeed, SystemNetName) Values('CPU2', 'Intel', '2392', 'TWEETY') Where 'CPU2' NOT IN (Select CPUNumber from CPU)
I want to insert into the cpu table as long as what I am inserting is not already in the table.
Can someone help me parse this oracle insert statement into SQL Server please?
insert into notify_template (subject, body, priority) values ( ' Budgeted time exceeded for task [18040] ', ' Actual Time exceeds Budgeted time for the following task' || chr(10) || chr(10) || 'Task Name : [18040] Root Program Name : [18049] Actual Days : [2000000100] Budgeted Days : [2000000101] Estimated Time : [2000000102]' || chr(10) || chr(10) || ' The following resource(s) in the above task have exceeded the budgeted time:' || chr(10) || chr(10) || ' [2000000784]' || chr(10) || chr(10) || 'Click here : [12865] ', 2)
i am writing a trigger that inserts from one table to the next. i have an issue with the table being inserted into have 2 more columns than the one being inserted from. Here is the trigger just in case
CREATE TRIGGER [Insert40801] ON [dbo].[tPA10801] FOR INSERT
