I have a Bit field in my database ,but when i try to insert the bit in database it inserted succesfully but while accessing it returns -1,what would be the problem?
i used integer while inserting same as get the value from database?
Hi.I have an insert query which inserts record that are returned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?
Hi.
I have an insert query which inserts record that are rturned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).
How can I solve this ?
Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren
View 4 Replies View Relatedhi my self avii want to copy data from one table to other table,by giving certaincondition and i want o use insert statement .in this i want to pass somevalue directly and some value from select statement , if i try i ll geterror i.e all column of destination table (i.e in which i want to insertdata) should match with all columns in values column some thing likethis.plz give me some helpful suggetion on this
View 1 Replies View RelatedI need to insert data into a table based on the results returned by a select statement. Basically, the select statement below gives me a list of all the work orders created in the last hour.
select worknumber from worksorderhdr where date_created > DATEADD(HOUR, -1, GETDATE())
This might return anywhere between 5 and 50 records each time. What I then need to do is use each of the work numbers returned to create a record in the spec_checklist_remind table. The other details in the insert statement will be the same for each insert, it's just the worknumber from the select statement that needs to be added to the insert where the ?? are below:
INSERT INTO spec_checklist_remind (form, record_type, linked_to_worknumber, spec_checklist_id) values (5, 0, '??',52)"
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
View 1 Replies View RelatedI had a query and i need to insert record in table and also want to select output of query.
Actually I need to insert and show data in same query.
;with cte as (
select id , status
from tbl_main
where id = 15555
)
insert into testinsert (id , status)
select * from cte
I want to convert xml data with select query and insert into table.
Xml sample like this :
<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>
[code]....
And data should be like this with select query :
NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL
I want simple query in form of above format.
Hi all,
This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro.
Please help me.
Thanks in Advance.
Regards,
Rajeev Vandakar
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
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 RelatedHi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
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 )?
Appreciate your help,
Rajesh.
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
please help
mustfa
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