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 )?
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))
I have a table consisting of a primary key (ID) and some fields. I want to (from an ASP page) insert some data entered by a user from the table, and then retrieve the ID from the record that was just inserted. Then that ID will be used in another table to tie the two tables together. How do I write the SQL to do this?
if i have an existing table table2 and i want to copy the rows corresponding to : where mycol='myvalue' in table1 to the existing table table2, but table 2 has a column_id and scope identity.
Runing just: insert into table2 select * from table1 mycol='myvalue' gives me the error:
An explicit value for the identity column in table 'statventebck' can only be specified when a column list is used and IDENTITY_INSERT is ON. so what should i do to exclude the scope id column or what should i do to make my command work Thank you
Can seem to get it right, can anybody spot what I'm missing?
INSERT INTO dbo.StockMasterUPDATE(GroupCode) SELECT GroupCode FROM dbo.StockMasterBAK WHERE (dbo.StockMasterBAK.StockCode = dbo.StockMasterUPDATE.StockCode)
I have basically 3 tables, Customer, Car and Rental. Customer is composed of Customer_Id and Customer_Name. Car is composed of Car_Id and Make. And to finish Rental is composed of R_Id (primary key), Customer_Id, Car_Id and Number_Days.
Ok, what I've been trying to do is to add a new field in Rental (using only 1 SQL Statement). Car_Id, Number_Days, and Customer_Name are given. So I tried :
INSERT INTO RENTAL (Customer_Id, Car_Id, Number_Days) VALUES ((SELECT DISTINCT Customer.Costumer_Id From Costumer, Rental WHERE Rental.Costumer_Id=Costumer.Costumer_Id AND Costumer_Name="Müller"), 5, 4);
But then I got an error message. So I tried it again without VALUES
INSERT INTO RENTAL (Customer_Id, Car_Id, Number_Days) SELECT (SELECT DISTINCT Customer.Costumer_Id FROM Costumer, Rental WHERE Rental.Costumer_Id=Costumer.Costumer_Id AND Costumer_Name="Müller"), 5, 4 FROM Rental;
But then more than 70 rows were added to the table. So I tried again :
INSERT INTO RENTAL (Customer_Id, Car_Id, Number_Days) SELECT DISTINCT (SELECT DISTINCT Customer.Costumer_Id FROM Costumer, Rental WHERE Rental.Costumer_Id=Costumer.Costumer_Id AND Costumer_Name="Müller"), 5, 4 FROM Rental;
This time I could accomplish what I originally wanted but I still believe that there is an easier way to do this, right ? Could someone please help me ?
I'm having a problem with a SQL statment. I've got two tables:Table Stage:Location Date Sales Exp TaxNewYork 1/1/01 100.50 5.75 11.25and so on with about 20 account columns.I want to move this data into another table like this:Table PlanData:Location Date Account AmountNewYork 1/1/01 Sales 100.50NewYork 1/1//01 Exp 5.75NewYork 1/1/01 Tax 11.25Here's my statement:INSERT INTO PlanData (Location, Date, Account, Amount)SELECT Location, Date, "Sales" AS Account, SalesFROM StageThis is executing but I'm getting an integer into Column 3 in myPlanData table and the value in the amount table, like this:NewYork 1/1/01 100 100.50Can anyone help? I'm a newb so I'm sure it's a stupid error on mypart.
I need a query that looks at one table and appends another if newcustomer data is added. I think I need an Insert, Select statement usingthe NOT IN clause.I need to compare Division, CustomerNumber of the two tables.Help, Example Appreciated. ThanksFrank*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I want to write a single sp where I pass in the column values and ifit finds an exact match it returns the refid of that match else if itdoesn't find it, it adds a new row using the passed in values andreturns the RefID (primary key) of the new row (identity). I onlywant one return value, being the RefID of the found or new row.I currently am doing a select and then testing the @@ROWCOUNT. If < 1I do an INSERT and return the identity. It doesn't give the desiredresults.Thanks,RickN
Hello all, Im trying to insert into a table INSERT INTO [SPIResConv5].[dbo].[TransactionsTemp] ([RESORT_ID] ,[TRANSTYPE_ID] ,[BILLCODE_ID] ,[MAINTENANCE_ID] ,[CONTACT_ID] ,[POSTED] ,[DATE] ,[USER_ID] ,[BATCH] ,[TYPE] ,[AMOUNT] ,[PAYMENTCODE_ID] ,[BANKCODE_ID] ,[DOCNO] ,[Shift4Trx] ) Select Resort_ID. '' as TRANSTYPE_ID, '' as BILLCODE_ID, '' as MAINTENANCE_ID, Contact_ID, 'False' as Posted, Date = getdate(), 'Hwells' as [USER_ID], 3000 as BATCH, 2 as Type, Amount, 'LockBox' as PAYMENTCODE_ID, 'Conv' as BANKCODE_ID, DOCNO, '' as [Shift4Trx] from TransactionTempToTransaction
but I get a --
Cannot insert the value NULL into column 'TRXNO', table 'SPIResConv5.dbo.TransactionsTemp'; column does not allow nulls. INSERT fails.
The TRXNO is the first column in the table--but it has a wierd setup. For example
TRXNO RESORT_ID 3 ELL2 3 FAC 3 CSI 3 ATR 4 CSI 4 FAC 4 ELL2
It Creates a tranaction code- based on the last resort itselfs transaction -instead just adding a number for the next transaction.
How would I know the last tranasaction for the resort to insert the data from the other table?
I need to insert the AccountInfoID from AccountInfo along with LodgingSummaryID, LoadTransactionCode, NoShowIndicator, CheckInDate, DailyRoomRate, TotalOtherCharges from Load_LodgingSummary into the LodgingSummary table (which will be empty from the start).
I have devised the following query:
set identity_insert LodgingSummary on insert into LodgingSummary ( LodgingSummaryID, AccountInfoID, LoadTransactionCode, NoShowIndicator, CheckInDate, DailyRoomRate, TotalOtherCharges ) select LodgingSummaryID, (select min(ai.AccountInfoID) from AccountInfo ai where ai.AccountInfoID not exists (select AccountInfoID from LodgingSummary l where l.AccountInfoID= ai.AccountInfoID)) as AccountInfoID, LoadTransactionCode, NoShowIndicator, CheckInDate, DailyRoomRate, TotalOtherCharges, TotalTaxAmount from Load_LodgingSummary set identity_insert lodgingsummary off
When I run the query, I only get the first AccountInfoID from AccountInfo. The data in LodgingSummary looks like (table shortened for brevity):
Hi, Can someone tell me if it is possible to do an SQL insert with a select (to copy specific records) query and specify the value for a specific field to insert in the new records instead of using the value in the field in the select statement. If so can you provide me with a simple example. Cheers Mark :)
Good Day, I am passing some XML into a stored procedure: <answers> <answer id="60" text="" /> <answer id="65" text="A moderate form of learning disability" /> <answer id="68" text="We will keep ASD checked" /> <answer id="70" text="" /> </answers> Along with a memberid and questionid. I was wondering how I can get this into a table CREATE TABLE [dbo].[Answers]( [PrimaryKeyID] [int] NOT NULL, [MemberID] [int] NOT NULL, [QuestionID] [int] NOT NULL, [AnswerID] [int] NOT NULL, [FreText] [varchar](255) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY]What I would also like to do is if the text attribute is empty then put a NULL in the FreText field.I think I am looking for Insert into MyTable ( Select @MemID, @QuesID, 'somexpathforanswer', 'somexpathfortext' -- if empty then NULL From @MyXML ) Any ideas - places to to look - thoughts AprreciatedKal