help to find out why? if i use "select" it can transaction in this code. And here "insert" .My WebAplication could work,but have no result of "insert".and no error information.The "insert" purview of "products" table have selected.
I help you can help me to point out maybe where is error.
Hi allI am looking for examples of scripts that will help me doing these things: - import a text file delimited with the character "*", representing a new month of data, for example data from march 2007 - create a new table with the structure of an existing one to import the data, for example Data_March_2007 - alter an existing totals table adding a new column for the new moth imported, adding a new colum for the month of March 2007.
I am new to SQL7 (in fact new to Database Management as well).
I am trying to write some store procedures to create and manage JOBs within SQL7 Server. For example, to cause data-migration to a different place everyday at certain time. I have tried to use things like sp_add_job. However, I kept getting messages like "Cannot find procedure sp_add_job" although I can see it under Master database. (I have been refering to Master database when I tried these commands).
What I would like to achieve at the end is to be able to specify and create JOBs entirely using Transac-SQL. (I have made it with the Enterprise manager, but that can not be the solution here. )Any help and tips would be greatly appreciated!
I am new to SQL7 (in fact new to Database Management as well).
I am trying to write some store procedures to create and manage JOBs within SQL7 Server. For example, to cause data-migration to a different place everyday at certain time. I have tried to use things like sp_add_job. However, I kept getting messages like "Cannot find procedure sp_add_job" although I can see it under Master database. (I have been refering to Master database when I tried these commands).
What I would like to achieve at the end is to be able to specify and create JOBs entirely using Transac-SQL. (I have made it with the Enterprise manager, but that can not be the solution here. )Any help and tips would be greatly appreciated!
I currently have a problem where I need to create a backup from MSSQL, via a command line, ideally from a batch script. This is on a PC. Each machine has SQL Server 2000 on.
I just need to know the code that dos will tell it to run the sql statement. Ideally the command will also give the directory to place the backup.
I have a historical table on a dedicated SQL Server (let's call it the reporting db) that is populated every morning with production data that does not already exist. The data in the prod table is purged after 7 days and nothing is ever deleted from the historical table. I have set up the linked server between the two 2008 SQL Servers, but when I try to run this simple query from the reporting DB, it takes more than 5 minutes and still "executing". I eventually have to cancel it:
-- INSERT INTO Temp_Import_historical SELECT TOP 1 * FROM [192.168.1.100].ProdDB.dbo.Temp_Import_historical a WHERE NOT EXISTS (select [Temp_Import_ID] from Temp_Import_historical where a.[Temp_Import_ID] = Temp_Import_historical.[Temp_Import_ID])
I have omitted the INSERT statement on purpose, since I can't even get to output 1 row. Why this is such a resource intensive query?
Hi all,We have an application through which we are bulk inserting rows into aview. The definition of the view is such that it selects columns froma table on a remote server. I have added the servers usingsp_addlinkedserver on both database servers.When I call the Commit API of oledb I get the following error:Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.I would like to know if we can bulk insert rows into a view thataccesses a table on the remote server using the "bulk insert" or bcpcommand. I tried a small test through SQL Query Analyser to use "bulkinsert" on a such a view.The test that I performed was the following:On database server 1 :create table iqbal (var1 int, var2 int)On database server 2 (remote server):create view iqbal as select var1,var2 from[DBServer1].[SomeDB].[dbo].[iqbal]set xact_abort onbulk insert iqbal from '\MachineIqbaliqbaldata.txt'The bulk insert operation failed with the following error message:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData(CheckforData()).Server: Msg 11, Level 16, State 1, Line 0General network error. Check your network documentation.Connection BrokenThe file iqbaldata.txt contents were :112233If the table that the view references is on the same server then weare able to bulk insert successfully.Is there a way by which I should be able to bulk insert rows into aview that selects from a table on a remote server. If not then couldanyone suggest a workaround. I would actually like to know someworkaround to get the code working using OLEDB. Due to unavoidablereasons I cannot output the records to the file and then use bcp tobulk insert the records in the remote table. I need to have some wayof doing it using OLEDB.Thanks in advanceIqbal
I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
I have stored procedure .In SP i am using cursur to load data from Parent to several child table.
I have attached the script with this message.
And my problem is how to use direct select and insert or load to speedup the process instead of cursor.
USE [IconicMarketing] GO /****** Object: StoredProcedure [dbo].[SP_DMS_INVENTORY] Script Date: 3/6/2015 3:34:03 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
I am inserting updating few tables from snapshot and reading same bunch of tables from reporting using readcommitted . It is showing some deadlocks i think it is write in this situation as " x" is not compitable with "s" ,"is".
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.
However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?
Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).
I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?
Here is the situation i am stuck with, see the example first and below explained the problem:
-- 'SESSION A
create table foo (
id integer,
pid integer,
data varchar(10)
);
begin transaction
insert into foo values ( 1, 1, 'foo' )
insert into foo values ( 2, 1, 'bar' )
insert into foo values ( 3, 1, 'bozo' )
insert into foo values ( 4, 2, 'snafu' )
insert into foo values ( 5, 2, 'rimrom' )
insert into foo values ( 6, 2, 'blark' )
insert into foo values ( 7, 3, 'smeg' )
commit transaction
create index foo_id_idx on foo ( id )
create index foo_pid_idx on foo ( pid )
begin transaction
insert into foo values ( 9, 3, 'blamo' )
-- 'SESSION B
begin transaction
select id, data from foo with ( updlock, rowlock ) where id = 5;
-- Problem:
-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.
-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name) Dim selectSQL As StringDim con As SqlConnection Dim cmd As SqlCommand Dim reader As SqlDataReader Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;") Dim username As String username = Request.QueryString("username") selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con) con.Open() reader = cmd.ExecuteReader() lbl.Text = selectSQLCatch ex As Exception lbl.Text = ex.Message Finally If (Not con Is Nothing) Then con.Close() con.Dispose() End If End Try End Sub '------------------------------------------------------------------------------------------------------------------------------------------------------------------------- May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
hi need help how to do this INSERT INTO SELECT FROM table + add one row for evry employee (on insert)
this is table tb_test1 empid fname unit fld1 fld2 fld3 fld4 fld5 ---------------------------------------------------------------------------------------- 111 aaa 1 a b c d d 222 bbb 3 a c e g g 333 cccc 5 s h t u j
Code Snippet
INSERT INTO [nili].[dbo].[tb_test2]
([empid]
,[fname]
,[unit]
,[fld1]
,[fld2]
,[fld4]
,[fld5])
SELECT [empid]
,[fname]
,[unit]
,[fld1]
,[fld2]
,[fld4]
,[fld5]
FROM [nili].[dbo].[tb_test1]
i need to insert into tb_test2 and ADD one row for evry employee val_orginal=1 (the orginal ROW) val_orginal=2 (the extra ROW)
this is table tb_test2 after the insert HOW TO THIS
empid fname unit fld1 fld2 fld3 fld4 fld5 val_orginal ------------------------------------------------------------------------------------------------- 111 aaa 1 a b c d d 1 111 aaa 1 - - - - - 2 222 bbb 3 a c e g g 1 222 bbb 3 - - - - - 2 333 cccc 5 s h t u j 1 333 cccc 5 - - - - - 2
Hallo, I am trying to insert into a table HFacility a FacilityID, HotelID FROM Facility. Using Select.Both HFacility and Facility have columns FacilityID, HotelID with same DataType.Insert INTO HFacility Select FacilityID, HotelID FROM FacilityI am getting this error:Server: Msg 213, Level 16, State 4, Line 1Insert Error: Column name or number of supplied values does not match table definition.Thank you for your help
Hi guys,I need help with my query for my web application. The situation is, once i select an item in the dropdownlist and click generate, it will populate the gridview with the query results. Now, what i wanted to do is get all the Document Owner ID results and save it into another table. How will i able to get all the Document ID result and store it in another table? Thanks in advance.
I am attempting to use a SQL statement that I shameless stole from "Using Microsoft SQL Server 6.5 Second Edition" and am failing miserably. I want to use the statement
INSERT INTO CallEntries (CallNum, Entry) VALUES (SELECT CallNum, Notes FROM CallsJunk)
and am getting 2 errors. The first is a syntax error near the keyword 'SELECT', and the second is a syntax error near ')'
These are both tables in the same database. Anyone know what I'm screwing up here?
I'm trying to use a select statement to retrieve a value and then use this value in an insert. I've written the entire code inside a stored proc. Darn thing refuses to work. Please help Here is a call to this stored proc Set_NewUserName 'mm', 'mm', 'student', 'email@em.com', 'mm', 'mm', 'mm', 'add1', 'add2', '600041', 'hybad', '93805', 'city', 'chennai', '13/10/1972', '6'
the code above sends third param 'student' to retrieve UserType_ID from tblUserType table. Also the last parameter is Class ID in the insert statement, for which I am passing the class value which will in turn retrieve the class ID from the tblClass table.
STORED PROCEDURE CODE BELOW HERE
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[Set_NewUserName] -- Following parameters will be sent to this proc by a webpage -- We need to use the value in UserType_Name to identify if this is a -- teacher or a student and insert into tblUser the appropriate value -- similarly for class Id, retrive from class table using the value user -- selects from drop down @Login_IDnvarchar(20),@Password nvarchar(30),@UserType_Name nvarchar(30), @Email nvarchar(30),@FirstName nvarchar(10),@LastName nvarchar(10), @FullName nvarchar(20), @add1 nvarchar(10),@add2 nvarchar(10),@pin nvarchar(10), @city nvarchar(10),@phone nvarchar(10),@HintQuestion nvarchar(MAX), @HintAnswer nvarchar(50),@DOB nvarchar(10),@Class_Name nvarchar(10) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Declare @Class_IDuniqueidentifier Declare @UserType_IDuniqueidentifier
-- retrieve the class id first from the tblClass Table SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION IF NOT EXISTS (select Class_ID from tblClass where Class_Name = @Class_Name) RAISERROR('select Class_ID from tblClass where Class_Name',11,1)
BEGIN ROLLBACK TRANSACTION RAISERROR('You must provide a valid Class ID',11,1) END
-- retrieve the usertype id first from the UserType Table SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION IF NOT EXISTS (select UserType_ID from tblUserType where UserType_Name = 'student') RAISERROR('select UserType_ID from tblUserType where UserType_Name',11,1)
BEGIN ROLLBACK TRANSACTION RAISERROR('You must provide a valid Status ID',11,1) RETURN END
-- At last the Insert statements for the procedure -- is created below here
I'm a beginner and I've been searching most of the day for the right syntax.
Table UserData has 3 fields UserID TypeID SomeNullField
I want to insert into TypeID the value 207 if it doesn't already exits. ============================================= I thought this would work, but doesn't
INSERT INTO UserData (TypeID) VALUES ( 207) SELECT UserID FROM UserData WHERE (NOT exists(SELECT FROM UserData WHERE UserData.TypeID = 207));
++++++++++++++++++++++++++++++++++++++++++++++ Am I even close???? Like I said I am new at this game. Appreciate any help anyone can reach down and lend.
I have an insert into query that uses a select to get the valuse form other tbls. however on of the column in the tbl i am inseting into requers a values so i can not just leave it out. however I cant get that value for another tbl ither so long and short of it I need to know how to us the insert into with the select and also beable to set that odd column to 0.
This is what I have so far:
Code:
INSERT INTO CheckDetail(FK,Leaseid,BU,LOC,DuplicateVerification) Select distinct t1.ID, t2.site_id,t3.CompanyCode,t3.CostCenter From PMTK_tbl as t1, Leaseinfo as t2 left join CostCenters as t3 on t2.market = t3.market and t2.market_region = t3.RegionCode where t2.site_id = '9SA0998A' order by t1.ID ASC
The DuplicationVerification needs to be set to 0.. How do I do that
I dont know why this code is not working (for MS SQL 2000)DECLARE @Name nVarChar@Name = SELECT DISTINCT Users.Name FROM Users ORDER BY Users.NameINSERT INTO Local(Numb, Name, Level) VALUES (0, @Name , 1)I want to insert into the table Local all the Distinct Names from Usersthe datatype of name :[Name] [nvarchar] (100) NOT NULL UNIQUE NONCLUSTEREDif the name allready exists in the table Localisation I must jump over the error how can i do it ?thank you
Haii... What i want to do is to select a data from other table and insert into another table. I using below code, but an error occur "There is an object name Bill in database".
SELECT OrderID, TableID INTO Bill FROM Orders WHERE (OrderID =(SELECT MAX(OrderID) FROM Orders))
