SQL 2005, I have 3 tables:
User
UserId
UserName
Group
GroupId
GroupName
UserGroup
UserId
GroupId
Now I want to add a user to all groups (into UserGroup). I only know the username. Can this be done in one statement?
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 ?
View 2 Replies View RelatedHere 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 )?
Appreciate your help,
Rajesh.
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
and tnx for the help
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.
View 2 Replies View RelatedHello all,
I'm trying to copy the contence from one table to another with the following statement:
insert tmembers
select cardno,voornaam,achternaam,straat,nummer,toevoegin g, postcode,woonplaats,regio,land,geboortedatum,gesla cht,leeftijd,telefoon,mobiel,homepage,email,id_bew ijs,id_nummer,uitgiftedatum,expiratiedatum,delict, hobbies,soort_bezoeker,muziek_voorkeur,mailing,ema il_lijst,reactie,notitie,haarkleur,oogkleur,kenmer ken,fingerprint,faceprint,cam_pos from members
I get the following error on execution
Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated.
Can anyone tell me what this means or suggest a better way on how to copy the contence between two tables.
Kind regards,
Rob
Hi,
Does anybody know how to query a table to get the distinct rows, and insert
these rows into another table in another database
Thanks in advance
Which is more efficient SELECT INTO or INSERT INTO?
Thanks
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?
The example I was given is
Insert into addresses
what's the difference between SELECT INTO and INSERT INTO.
because when I insert value into a temp table using this two function, Select into runa 3 times faster compare to insert into.
does anyone know what's the reason for this.
Thank You,
John
Hi,
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
insert into tblUser
values (
newid(),
@Login_ID,@Password,
@UserType_ID,
@Email,
@FirstName, @LastName, @FullName,
@add1, @add2,@pin, @city,
@phone,
'True',
@HintQuestion,@HintAnswer,
@DOB,
@Class_ID
)
END
can somebody tell me the difference between select into and insert into statements in sql server.
for example: How
SELECT LastName,FirstName INTO Student_bk FROM Student
is different from
INSERT INTO Student_bk(LastName,FirstName) Select (LastName,FirstName) from Student
Thanks.
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.
Quick question....
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
View 14 Replies View RelatedHi
for MS SQL 2000
how can I do ? :
INSERT INTO [Users] (Numb, Name, Level) VALUES
(
2,
(SELECT DISTINCT Names FROM Others) ,
3
)
Users has an autoIncrement ID = Users_id
thank you for helping
set nocount on
create table #t1(col_1 char(6),col_2 char(11))
select getdate()
select col_1,col_2 into #t2 from #t0
select getdate()
insert #t1(col_1,col_2) select col_1,col_2 from #t0
select getdate()
----
-----------------------
2008-05-22 10:36:35.447
-----------------------
2008-05-22 10:36:36.790
-----------------------
2008-05-22 10:36:44.480
it seems so strange the to "select into" execution which much faster than "insert" ... is it correct or anything i missed out?
platform: sql server 2005
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))
Hi Experts,
Is it possible to insert a select statement directly to a table.
TIA:)
RKNAIR
Hi,
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?
thanks,
mellamokb
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)
Thanks
Hi guys, I have the following problem :
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 ?
Thank you very much and cheers from Switzerland
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.
View 2 Replies View RelatedI 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!
View 2 Replies View RelatedI 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
View 5 Replies View RelatedHello 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?
Thanks for your help
Hello.
Can i insert a record in a table B during reading records in a table A (Sql Compact Edition 3.1)?
Something like this:
SqlCeConnection c=new SqlCeConnection();
SqlCeConnection d=new SqlCeConnection();
SqlCeCommand cmd=new SqlCeCommand(c,"SELECT * FROM TABLE A");
SqlCeCommand cmd1=new SqlCeCommand();
cmd1.Connection=d;
cmd.Connection.Open();
cmd1.Connection.Open();
SqlceDataReader dr=cmd.ExecuteReader();
while (dr.read())
{
string a=dr.GetString(0);
string b=dr.GetString(1);
cmd1.Text="INSERT INTO B VALUES('"+a+"','"+b+"')";
cmd1.ExecuteNonQuery();
}
dr.Close();
c.CLose();
d.Close();
Thanks,Simon.
Hi. I have 3 tables:
Table1: AccountInfo
AccountInfoID bigint <-- primary key
AccountNumber char
PostingDate datetime
TransactionReferenceNumber char
SequenceNumber numeric
Table2: LodgingSummary
LodgingSummaryID bigint
AccountInfoID bigint <-- foreign key
LoadTransactionCode tinyint
NoShowIndicator decimal
CheckInDate datetime
DailyRoomRate decimal
TotalOtherCharges decimal
Table3: Load_LodgingSummary
LodgingSummaryID bigint
LoadTransactionCode tinyint
AccountNumber varchar
PostingDate datetime
TransactionReferenceNumber varchar
SequenceNumber numeric
NoShowIndicator numeric
CheckInDate datetime
DailyRoomRate numeric
TotalOtherCharges numeric
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):
LodgingSummaryID AccountInfoID LoadTransactionCode
1 1 4
2 1 4
3 1 4
4 1 4
etc...I want LodgingSummary to look like:LodgingSummaryID AccountInfoID LoadTransactionCode
1 1 4
2 2 4
3 3 4
4 4 4
etc...
How do I fix the subquery in the select statement (in red above) to get what I want? Thanks!
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