MS SQL Server :: Select Into Vs Insert

INSERT-SELECT Depending On The Select:ed Order

Aug 15, 2006

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).

View 3 Replies View Related

INSERT INTO... SELECT... Cannot Insert Duplicate Key...

Jul 20, 2005

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 Related

Insert Should Aquire Only Exclusive Rowlock. Why Does Insert Block Select With ( Updlock, Rowlock )?

Mar 12, 2007

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.

View 5 Replies View Related

INSERT INTO SELECT FROM + Add 1 Row On INSERT

Mar 15, 2008

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

View 12 Replies View Related

INSERT SELECT

Jul 7, 2006

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

View 4 Replies View Related

Select Then Insert

Nov 28, 2007

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 Related

Insert/select

Apr 5, 2001

Hello 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

View 1 Replies View Related

Select & Insert

Jun 29, 2000

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

View 3 Replies View Related

Select Into Vs. Insert Into

Aug 15, 2000

Which is more efficient SELECT INTO or INSERT INTO?
Thanks

View 1 Replies View Related

Insert W/ Select

Sep 15, 1999

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

View 3 Replies View Related

SELECT INTO Vs. INSERT INTO

Mar 11, 2002

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

View 1 Replies View Related

A Select And Then An Insert Into Sp

Jul 20, 2007

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

View 4 Replies View Related

Insert Into Vs. Select Into

Jan 4, 2005

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.

View 4 Replies View Related

Insert Value Select ... Where Not

Oct 19, 2004

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.

View 10 Replies View Related

INSERT With A Select

Apr 17, 2007

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

View 2 Replies View Related

Insert From Select

Dec 7, 2006

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 Related

Insert Into Select

Jan 14, 2007

Hi

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

View 2 Replies View Related

Select And Insert

Apr 8, 2008

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))

View 5 Replies View Related

Select ....insert

Jun 10, 2008

Hi Experts,
Is it possible to insert a select statement directly to a table.

TIA:)

RKNAIR

View 3 Replies View Related

Select ID After INSERT INTO

Jan 10, 2006

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

View 12 Replies View Related

USING INSERT INTO SELECT

Nov 6, 2006

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

View 7 Replies View Related

Insert Into Using Select

Jan 2, 2008

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

View 9 Replies View Related

INSERT SELECT

Jan 31, 2008

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

View 7 Replies View Related

Insert Into With Select

Jul 23, 2005

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 Related

Insert Select Help

Jul 20, 2005

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!

View 2 Replies View Related

Select And Insert In One Sp

Jul 20, 2005

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

View 5 Replies View Related

Insert Select

Feb 23, 2007

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?

Thanks for your help

View 1 Replies View Related

Insert During Select

Apr 17, 2008

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.

View 3 Replies View Related

Select Insert Into

Feb 27, 2008

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?

View 7 Replies View Related

SELECT With INSERT

Sep 8, 2006

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!

View 4 Replies View Related

SQL Insert With Select And Specify Value For A Field

Oct 24, 2007

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 :)

View 4 Replies View Related

How To Insert Into A Table Using A Select From Xml

Dec 20, 2007

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

View 1 Replies View Related