Select The Insert Value Into Field In Another Table!!!!!!

May 24, 2002

i have tbl_location which includes userid, building, room. i combine the building and room into one feild called mailstop

SELECT Userid, Building +'/'+Room AS mailstop
FROM tbl_Location

i then want to take this recordset and insert it into a field called mailstop in my employee table. but they must based upon the userid of the location table and the userid of the employee table.

so the userid of the location table must match the userid of the employee table and insert that mailstop value into the mailstop feild in employee table. i want to get this right the first time. any help would be greatly appreciated.

View 1 Replies

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

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table

Aug 5, 2007

I have the following query in sql 2005:

PROCEDURE [dbo].[uspInsert_Blob] (

@fName varchar(60),

@fType char(5),

@fID numeric(18, 0),

@bID char(3),

@fPath nvarchar(60)

)

as

DECLARE @QUERY VARCHAR(2000)

SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)

SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)

AS Document"

EXEC (@QUERY)

This puts some values including a pdf or .doc file into a table, tblDocTable.

Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.

tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.

Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.

Any ideas how to do this?

View 1 Replies View Related

Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE

Nov 21, 2006

For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View 6 Replies View Related

Insert Into A Table With Only One Field (ID)

Nov 25, 2007

Hello,
I would like to know the sql-query to insert a new row into a table which has only one column (ID) which is the primary key.

Thanks a lot for any suggestion !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 4 Replies View Related

How Can I Insert ' (Apostrophe) Into Sql Table Field ?

May 30, 2007

How can I insert ' (Apostrophe) into sql table field ?
Insert Into Table(Field) Values(?)
After executing sql statement above, I want to see ' (apostrope) in field.
Thanks

View 3 Replies View Related

How To INSERT INTO [Table] ([Field]) VALUES('I Have A ' In Value')

Jun 2, 2004

Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')

please teach me how to

xxx

View 2 Replies View Related

Problem To Insert A Key Field In Another Table

Apr 16, 2008

hi everybody I have this case which I couldn't solve and need badly your help

table1

id1 field1 field2
1 a b
2 a c

table2

id1 field1 field2
a b
a c

I need to update table2 on the (id1) if the value of field1 and field2 in table 1 is equal to field1 and field2 in table 2 and inserting null when there is no match:::

I'm trying with this code but couldn't get thru

UPDATE table2
SET id1= COALESCE(table2.id1, 0)
from table1
left outer Join table2
ON
table1.id1=table2.id1

thanks

View 1 Replies View Related

Insert A Field (IDENTITY) Into Table

Apr 25, 2007

Hi,

I have a table with 1000000 records i try to add a field with following`spec.

[mkey] [int] IDENTITY(1,1) NOT NULL

I get the following meassage:-

yearly' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

Then I Have the following error:-
Time out error

- How is the best way to copy a large table from one to anther.

regards

View 2 Replies View Related

Insert Data Into Table (one Field Is A PK)

Apr 1, 2008

hey all

I am new to this side of the sql and just testing some statements

what I want to do

insert one line into a table that already has data in it

KBCopy = Database name
KBStatusID = (PK, tinyint, not null)
KBStatusName = (varchar(100) not null)

the following is what I have tried.

use KBCopy

go

insert into KBStatus ("KBStatusID", "KBStatusName") Values ("4", "Archived")

the error I receive

Msg 128, Level 15, State 1, Line 2

The name "4" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables.
Column names are not permitted.

I presume this is to do with the ID?

thanks
jewel

View 4 Replies View Related

SELECT Table Field Names

Nov 14, 2001

I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy

View 6 Replies View Related

Looking For Field Name From Table On Select Criteria

Sep 19, 2006

Hari writes "Sub:- looking for Field name from table on select criteria

dear friends

I have an table [assosories] which have 100 fields. initial 10 fields have some date, varchar, int types rest 90 are bool type.

for a single row I need to know the field name which have true vaules in rest 90 fields

I need to insert this field name into another table as a row.

Thanks
HARI"

View 2 Replies View Related

How To Select Field Names Of Table?

Mar 15, 2008

Can someone write a query that select all the fields of tables in database that have type 'image'?

Something like this:

Select TableName, FieldName FROM TableWhereTheyKeepThoseThings
WHERE TableWhereTheyKeepThoseThings.FieldType='Image'

... olny it should work :)

View 2 Replies View Related

Retrieving Identity Field From Table On INSERT

Feb 29, 2008

I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?

View 3 Replies View Related

Help With A Query- Select A Top Field And Join It With Another Table

Feb 1, 2008

hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!

View 2 Replies View Related

How Can I Insert Id Field Automatic In Table By Stored Procedure

Sep 25, 2005

i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key got error because allready record has same value .how i can know the last row's student_id value and input a new valid value in one stored procedure thanks

View 1 Replies View Related

Insert Multiple Records Into A Single Field On Another Table

Feb 15, 2012

I have a table JOBCODE which contains a list of codes.

I want to insert these values into table VIEWS as a list separated by spaces.

E.G.

Table Jobcodes looks like this

code
1
2
3
4
5
6

And I want table Views to look like this:

field1
1 2 3 4 5 6

How do I go about this?

View 4 Replies View Related

SQL Server 2012 :: XML File Insert Into A Table Field

Aug 31, 2015

I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.

Is a blob the best datatype. How to I insert that XML file into it?

View 2 Replies View Related

Insert GUID Into SQL Mobile Database Table Field

Feb 17, 2006

Hi,

I have created a database in the SQL Server 2005 and the same database in sql mobile. I have a field, which has a GUID and uniqueidentifier, when i tried to insert the same data which is there in sql server 2005 database into sql mobile database it is giving me error as below

Major Error 0x80040E14, Minor Error 25501

> INSERT INTO TypeValue (TypeValue_ID,DisplayValue,TypeName,OrderIndex,IsActive)

VALUES (b6483fe1-1118-4ba1-9106-0069715a75c0,Asian,ETHNICITY,1,TRUE)

There was an error parsing the query. [ Token line number = 2,Token line offset = 24,Token in error = ba1 ]

Please suggest me any solution.

Thank you,

Prashant

View 1 Replies View Related

Need Help W/ SELECT From One Table, One Field, Multiple Unique Records

Apr 22, 2006

I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:

Anna Smith
John Doe

etc. I did not see IDs listed for any of the names in the table when I looked.

There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.

Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:

strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"

This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.

Is there a way to modify this statement to pull only the records that I want?

Thanks for any help you can give.

AJ

View 3 Replies View Related

SQL Server 2012 :: Use Select Within A Table Field In 2nd Query

Aug 27, 2015

I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)

Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'

What I need is to take each select statement in turn and add the data into a temp table. I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.

View 6 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

INSERT INTO - SELECT With Pk In Table

Apr 11, 2008

Is it possible to use the INSERT INTO - SELECT syntax with a pk in the target table that has no auto increment?

The tables structure is:

CREATE TABLE objectlang (
objectlangID integer NOT NULL, <== PK
objectID integer NOT NULL,
langID integer NOT NULL,
somedata integer NOT NULL,
[[...more columns here...]]
)

The statement should look like:

INSERT INTO objectlang
SELECT [[??set pk here??]], o.objectID, l.langID, 1, [[...more data here...]]
FROMobject o, lang l
WHERE[[...conditions...]]

I thought of something like reading the current max value of objectlangID and adding a value,
but no idea where this value should come from.

I need this for more than one project in mssqlserver 2000/2005, postgresql and oracle.

tia

View 2 Replies View Related

Skip Field Terminator While Inserting Data To A Table-Bulk Insert

Oct 10, 2007

Hi,
I have a data file which consists of data as below,
4
PPU_FFA7485E0D||
T_GLR_DET_11||

While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table,
here is my query iam using to insert the data using bulk insert.

BULK INSERT TABLE_NAME FROM FILE_PATH
WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''')

Can any one help on this.

Thanks,
-Badri

View 7 Replies View Related

How To Insert Results Of A SQL Query Into The Field Of Another Table, As Comma Seperated Values.

Mar 7, 2008

I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values.

For example, to explain things in detail:

create table dbo.phone_details

(country varchar(20),
state varchar(30),
list_of_toll_free_numbers text)

insert into dbo,phone_details
values
( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' )

The final output I desire is:

country state list_of_toll_free_numbers

---------- ------- -----------------------------------------
USA CA 8009877654, 8665764398, 8776543219

View 8 Replies View Related

How To Perform SELECT Query With Multiple Parameters In A Single Field In A Table

Sep 13, 2006

i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.

View 1 Replies View Related

Need To Set A Field In A Select Statement Equal To Yes Or No If Record Exists In Separate Table

Jan 8, 2008

Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.

View 2 Replies View Related

How To Select && Insert Data In The Same Table

May 24, 2007

Hi
I have a table called Version and its attributes are Version_ID, Project_ID , Hospital_ ID , Date_Created and comments. I want to select the data by Version_ID,Project_ID and Hospital_ID and the selected data is inserted in the same table(Version) as new row .
Table: Version (Version_ID(Primary_key), Project_ID(Foreign_Key),Hospital_ID(Foreign_Key),Date_Created,Comments).
Iam using Visual Web Developer Express and SQL Server 2005. Iam doing on asp.net 2.0.
Could anyone please send me the code asp.net 2.0 for the above problem.

View 6 Replies View Related

SqlDataSource Select From One Table And Insert To Another

Mar 13, 2008

Is it possible within a Sqldatasource to select data from one database table and the insert that data into another?
eg:
1 <asp:SqlDataSource ID="SqlDataSource" runat="server"
2 ConnectionString="<% Something %>"
3 InsertCommand="INSERT INTO [Resumes] ([ResumeID], [UserID], [CompanyID], [ResumeTitle], [ResumeDesc]) VALUES (@ResumeID, @UserID, @CompanyID, @ResumeTitle, @ResumeDesc)"
4 SelectCommand="SELECT [UserId], [CompanyID], [CompanyName] FROM [Info] WHERE ([UserId] = @UserId)" <SelectParameters>
5 <skm:MembershipUserIdParameter Name="UserId" />
7 </SelectParameters>
8 <InsertParameters>
9 <skm:MembershipUserIdParameter Name="UserId" />
10 <asp:Parameter Name="CompanyID" Type="Int32" />
11 <asp:Parameter Name="ResumeTitle" Type="String" />
12 <asp:Parameter Name="ResumeDesc" Type="String" />
13 </InsertParameters>
14 </asp:SqlDataSource>

View 1 Replies View Related

Insert And Select From Large Table

May 21, 2013

What will be the best way to go to select summary data from a big table with detail records and insert it into another table.

The source table contains approx 100 million detail records for a couple of months. I have a select statement that select a summary of the latest month's data and it average about 15 million records for the month which i want to insert into another table.
In the past i just used a standard insert into statement but not the best way of doing it.

If a view is created with just the last months summary data and i select from the view will the performance be better or will it just add more overhead?

Will a SSIS package work better to insert the summary data?

View 1 Replies View Related

Select Form 1 Table And Insert Into Another

Jan 30, 2004

Can someone point me in the right direction or show me a sample that may assist me in the following.

Thanks,
-dw

I need to select some data from 1 table and insert it into another using a stored procedure.

I also would like to select data from table 1 and insert a new record into table 1 modifying a field with new data passed by parameter.

Is it something like:

parameters passed = @selectJobNumber, @newJobNumber

declare @DeliveryMethodIDint

SELECT
@DeliveryMethodID=DeliveryMethodID,
... etc - more fields

FROM
jobDeliveryAddress

WHERE
(JobNumber= @selectJobNumber)

INSERT INTO [jobDeliveryAddress]
(
[JobNumber],
[DeliveryMethodID],
... etc - more fields

)
VALUES
(
@newJobNumber,
@DeliveryMethodID,
... etc

)

View 2 Replies View Related

Select Form 1 Table And Insert Into Another

Jan 30, 2004

View 6 Replies View Related