Copy Info From One Table Two Another With Conditions

Oct 25, 2005

I am importing a csv file into a temp table (works fine), now I want to take the data in the temp table based off of the temp id compared to the id of the permanant file.

basically if the record id exists skip to the next line and so forth, I am able to import everything just fine but I cannot get the WHERE condition to acually work.

INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 WHERE table2.col1 <> table1.col1

this is what my actual code is


INSERT INTO USER_INFO (fpu_id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id <> dbo.USER_INFO.USERID

this is the actual error that I get.

Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'USER_INFO' does not match with a table name or alias name used in the query.

-----
thanks for the help

View 1 Replies


ADVERTISEMENT

Is There A System Table With Timestamp Info Or DTS Job Info?

May 7, 2007

I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.

ddave

View 3 Replies View Related

JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions

Jan 10, 2008

My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:


SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)


No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.

--Version 1:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE

UserRoles.Active = 'TRUE'


-- Version 2

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId

AND UserRoles.Active = 'TRUE')


So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.

So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?

Thanks for whatever information you can provide.

View 7 Replies View Related

Add A New Column To A Table With Conditions?

May 28, 2013

i want to add a column to a table with conditions below :

1- it has a default value=0

2- this column can get null value

3- no constraint should be created

is it possible ?

View 3 Replies View Related

Transact SQL :: Update Table From Another With Conditions

Dec 2, 2015

I am trying to do a simple update in one sql table from another where a certain condition in both tables are met.I am missing something basic and have tried numerous posted examples, without success.Here is my code (that does not update) :

        opdragaliasnaaminsit.Connection = konneksie
        opdragaliasnaaminsit.CommandText = "UPDATE vyfjaarontledings " & _
        "SET aliasnaam = T2.aliasnaam" & _
        " FROM  vyfjaarontledings T1" & _
        " INNER " & _
        "JOIN blokke T2 " & _
        " ON T1.plaasno = T2.plaasno " & _
         "WHERE T1.plaasno = T2.plaasno"
opdragaliasnaaminsit.ExecuteNonQuery()

I am trying to update aliasnaam in vyfjaarontledings from blokke.

View 10 Replies View Related

Load The Conditional Split Conditions From A Table

Apr 23, 2007

Hi everybody,



I want to load the conditions for a conditional split from a table, so that the bussiness logic can be changed with a simple user interface .



How can I do this?





Best regards



John

View 5 Replies View Related

How To Do Bulk Update / Insert In A Table With Conditions

Oct 30, 2015

I have Three tables Student,Daily_Attendance_Master and Daily_Attendence_Details.

I want to run sql of insert or update of student attendence(apsent or present) in Daily_Attendence_Details based on Daily_Attendance_Master_Id and Student_Id(from one roll number to another).

If Both are present in table Daily_Attendence_Details then i want to run Updating of attendance from one roll number to another roll number in Daily_Attendence_Details on the basis of Daily_Attendence_Details_Id

And if both or any one is not present i want to run insert of student attendense from  one roll number to another roll number in Daily_Attendence_Details.

I give below the structure of three tables Student,Daily_Attendance_Master and Daily_Attendance_Details.

Student:-
CREATE TABLE [dbo].[Student](
[Student_Id] [bigint] IDENTITY(1,1) NOT NULL,
[Course_Id] [smallint] NULL,
[Class_Id] [int] NULL,
[Batch_Year] [varchar](20) NULL,
[Student_Initials] [varchar](20) NULL,

[Code] ....

View 13 Replies View Related

SQL Server 2008 :: Join Another Table But With Select Conditions?

Mar 24, 2015

I have this sql....

Select
DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName,
s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType,
ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,

[code]....

however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'

So the stand alone sql could be....

Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc

I'm just not sure how to incorporate that into my sql or whether there is a better way,

View 9 Replies View Related

Power Pivot :: Extracting MAX From A Table Based On Certain Conditions

Nov 20, 2015

I want to calculate the target based on Flag value if Flag value is "Y" ....than MAX(Customer Target) else MAX(SLA target).Flag column contains "Y" , "N" and some blank values . Flag, Customer Target and SLA target are the columns in Table1. I have used the below formulas

Target:=IF('Table1'[ Flag]= "Y",MAX('Table1'[Customer Target]),MAX('Table1'[SLA Target]))
Target:=IFERROR(IF('Table1'[Flag]= "Y",MAX('Table1'[Customer Target]),MAX('KPI'[SLA Target])),BLANK())

View 7 Replies View Related

Delete Rows In One Table By Referencing Another Table Info

Sep 16, 2004

I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.

Here is a basic breakdown of what I am trying to do:

Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)

Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)

I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.

Code:


SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x


This did not seem to do what I needed, it did not delete any rows at all.

I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2

Then I tried another scenerio that I also needed to do:

Code:


SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'



'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.

Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.

Any ideas on what I am doing wrong here?

View 3 Replies View Related

Power Pivot :: DAX Formula For Extracting A Column Value From Table Based On Certain Conditions

Jul 20, 2015

I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.

Here is the scenario:

I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).

I would like to extract  Key_member where key_member_type="PM" and active as of tb1(month_end_date).

Is this possible using DAX ?

View 6 Replies View Related

Not Sure How To Get Info From Other Table

Aug 31, 2005

I am having problems with my store procedure displaying my last column name which is "DISPLAY_CATEGORY_DESC". I am trying to display the description from another table. But in order to get to the description I have to go through 2 tables. The tables look like something like this.

First TableTABLE_CATEGORY

PRIMARY_KEY = DIR_CATEGORY_ID = 5000

Column Name
DIR_CATEGORY_LVL_ID1 = 100
DIR_CATEGORY_LVL_ID2 = 200
DIR_CATEGORY_LVL_ID3 = 300

Second TableTABLE_CATEGORY_DESCRIPTION
PRIMARY KEY DIR_CATEGORY_LVL_ID1 = 100

Column Name
DESCRIPTION = "Car Model Company"

This is my DISPLAY_CATEGORY_DESC

Not sure how to get to this description. Thanks for your help.


select cwebs.SESSION_ID, webs.ENTERED_DATE, cwebs.DIR_CATEGORY_ID, DISPLAY_CATEGORY_DESC

FROM di_webclickstats cwebs
left join di_websessions webs on cwebs.session_id = webs.session_id
where cwebs.DIR_CATEGORY_ID <> 0 and webs.ENTERED_DATE between @startDate and @endDate+1

View 3 Replies View Related

Table Usage Info

Jan 14, 2002

Is there a utility or function or 3rd party product that will show information about tables and other objects such as:

Last change date
Last used date
Where Used
more .....

I'm from an AS/400 environment and found it VERY useful to be able to access that kind of information to clean-up, monitor & manage the systems.

Any Ideas ?

View 1 Replies View Related

How To Select Table Info?

Apr 7, 2008

Hi

Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?

something like:

SELECT
TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM
...this is where you come in
ORDER BY
TABLE_NAME

View 6 Replies View Related

Same Table - Subset Of Info

Oct 25, 2007

I'm beating my head up against a wall here. I have one very LARGE table called op_hist.

CREATE TABLE [dbo].[op_hist] (
[op_trnbr] [int] NOT NULL ,
[op_type] [varchar] (80) ,
[op_date] [smalldatetime] NULL ,
[op_act_setup] [decimal](38, 10) NULL ,
[op_act_run] [decimal](38, 10) NULL ,
[op_qty_comp] [decimal](38, 10) NULL ,
[op_wkctr] [varchar] (80) ,
[op_part] [varchar] (30)

)

I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.

SELECT op_wkctr, pcs, hrs.b
FROM
dbo.op_hist
INNER JOIN
(
SELECT op_part, SUM(op_qty_comp) as pcs
FROM dbo.op_hist
where op_type='BACKFLSH'
group by op_hist.op_part

UNION ALL

SELECT op_part, SUM(op_act_setup + op_act_run) as b
FROM dbo.op_hist
where op_type='LABOR'
group by op_hist.op_part
) AS hrs
ON op_hist.op_part = hrs.op_part
where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'

Help me oby one - you're my only hope.

Sincerely,
Frustrated!

View 4 Replies View Related

Move Info From A Table In One Db To Another

Mar 25, 2008

Hello All.

I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).

Can anyone point me in the right direction?

View 4 Replies View Related

How Would You... (binary Info In A Table)

Sep 15, 2007

I have run into a challenge that I'm not sure how to best solve. I'dappreciate any opinions or input.I am working with a third party database. They are storing some datathat I need to use in a binary field. I've got the code to parse thebinary and reconstruct what I need. Unfortunately, there might bemultiple "entries" stored in a single binary field. a certain byte,let's just say the first, will always be the count of "entries" inthis particular SQL entry.An example:then entry might be:0x01000012341234where 12341234 is the data entry that I will parse.Another possible entry is:0x03000012341234567856789ABC9ABCThe first byte indicates that there are three data values I want toparse out:12341234567856789ABC9ABCThe portions of the binary I need are always the same length and theremay be from 1 to ~100 of them. (usually 1 if it matters.)The big question:How could a SQL query return an entry for each of the "entries" in thebinary field? For the second example I would want three entries in myresults, each row returning a different section of the binary data.For the first, only one row. I'll be querying the set and expecting toget back more results than the number of entries in the set.I haven't really tried much with this yet, (other than some mentalcalisthenics were I fell down onto the mat pretty hard), so just somedirection on where to begin would be helpful.(Yes, I understand that the difficulty is that this data shouldn't bestored this way, but there is not much I can do about that.)Thanks...James FraserJoin Bytes!

View 4 Replies View Related

How To Get Table Info From RID Deadlock ?

May 7, 2007

Hi There



I am trying to troubleshoot deadlock issues.

The dead lock is with a RID.



Now from various links i have established that you have to use DBCC PAGE() with relevant info to get more information, i get back a object and index id, m_objid.



However i dont know how to get the tablename form here, msdn simply says that it is an allocation id not and actual table id, so how can i from here get to table information?



All i am trying to figure out is which table is affected fromt he RID dealock, but that does not seem to be a simple process.



Thanx

View 5 Replies View Related

Table Size Info

Apr 24, 2008

Does TSQL (2005) offer a way to retrieve a given table's total size (data size vs index size)? Would like to be able to get at this info using TSQL (not management studio).

thanks

View 3 Replies View Related

How To Select Table Info?

Apr 7, 2008

Hi

Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?

something like:

SELECT

TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM

...this is where you come in
ORDER BY

TABLE_NAME

View 3 Replies View Related

TaskPad Missing Table Info

Apr 5, 2001

Hi:

After I used "copy database" to upgrade a SQL 7 database to SQL 2000, all are fine except the TaskPad view. The table info section is blank. Though we know all tables are in the table object.

Any thought?

David

View 2 Replies View Related

Amending Query To Get Info From New Table

Oct 19, 2013

I used to use this code to extract coupon usage from two tables orders and coupons

select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber

[code]....

Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are

ID in Promotions

and

PromotionID in PromotionUsage

Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?

View 5 Replies View Related

Dbo.Table Of A Database In The .SQLEXPRESS Object Explorer: How To Copy The Dbo.Table To The Another Blank Dbo.Table?

Jan 9, 2008

Hi all,

The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Regions
dbo.Shippers
dbo.Suppliers
dbo.Territories.

But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?

I right clicked on the dbo.Categories and I saw the following thing:
dbo.Categories
New Table...
Modify
Open Table
Script Table as |> CREATYE To |>
DROP To |>
SELECT To |>
INSERT To |> New Query Editor Window
File....
Clipboard
UPDATE To |>
DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.

Thanks in advance,
Scott Chang

P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.

View 3 Replies View Related

How To Access The Info Of A Table Created In Other Database

Aug 24, 2006

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V

View 2 Replies View Related

Printing Table And Columns Properties Info

Dec 14, 2005

I'm kinda new to SQL. I'm trying to get a print out of a specific table and its columns and column types. Is there a specific command or code that I can use to accomplish this?.

thx,



Newbie.

View 3 Replies View Related

Stored Procedure Returning Info About A Table

Sep 28, 2007

i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.

could you give a little code sample?

thanx

View 4 Replies View Related

Which System Table Holds Job Related Info

Dec 28, 2007

Hi All,

Can anyone help me find out which system table/s are out there that hold SQL Server Job/Agent information?

Thanks!

View 3 Replies View Related

Switching Database's And Retrieving Table, Etc Info

Jul 8, 2007

I have a query that gets the tables, columns, indexes, index_columns for all tables in a data base.

But, I need to be able to select any data base on the server
and then drill down into the tables, columns, etc.

I looked at sys.databases but can't see how to relate that to the
sys.tables to extend my query.

Is there a way?

I've looked at the information_schema area and catalog views
but I don't see a solution yet.

View 7 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related

Update Certain Fields In User Group Table With New Info

Apr 28, 2014

I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.

Here is the SELECT statement
SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu,
user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id,
user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex,
user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,

[code]....

We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is

UPDATE user_group
SET user_group.description = Consultants.description
FROM user_group
INNER JOIN Consultants
ON user_group.description = consultants.description
WHERE name like 'Adam Froth%'

but it keeps erroring and saying that it could 'Not be bound'.

View 2 Replies View Related

Updating MSSQL Table With Info From Excel Sheet

May 11, 2007

MSSQL noob here. Sorry guys, but I really need some help.


I have a MSSQL (2000) database and a Products Table. Now, there was three columns used for pricing that I was not able to populate on the initial usage. So I exported the table as an Excel document, and got rid of everything except the UID (unique identifier column) the Name, and the Three columns, so that the customer could take their time in filling out the information.


Ok, so These are what are common to both the MSSQL database and the Excel sheet

column uid = unique id integer
column Name = text
column eBay = number
column PriceGrabber = number
column Amazon = number

So, any idea how I can update my Products Table with this?

Please HELP!

View 18 Replies View Related

How To Make A Temp Table Using Info Fromtwo Tables

Mar 8, 2008

I have one database named StudInfo. It has two tables named StudentInfo, and GradeInfo.
StudentInfo conntains 4 columns. The 1st one is StudentID (PK) int, LastName varchar(10), FirstName varchar(10), and PhoneNumber int.

GradeInfo contains 4 columns also StudentID (FK) int, GradeID varchar(10), Grade int, Date Datetime.

What I would like to know is how using a T-sql query I could make a temp table with studentID, LastName, FirstName, and then the average of all the different types under GradeID. As of right now I have been limiting the names that are put into GradeID to Homework, Daily, Test, Quiz, and Bonus. When I say average I mean the average of all Homeworks under one studentID, and all Daily under one studentID... etc. I would like the info returned for each student in studentID. Allow Nulls has been turned off.

Never assume someone knows what you are talking about.

View 6 Replies View Related

Trigger In Child Table That Required Parent Info

Aug 5, 2007

Hi,
I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE.

I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore.

How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table.

Please advice.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved