List Tables Depending If Column Exists

Jul 27, 2004

Hi there,

Is there a quick way to list all the tables in a DB that contain a certain column name?

Thanks

S

View 5 Replies


ADVERTISEMENT

SQL Server 2012 :: Count From Multiple Tables For Same Column Exists In Database?

May 19, 2014

i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.

View 9 Replies View Related

DB Design :: Update List Of Records In A Table With New Value Depending On Old Value

Jun 15, 2015

I have a table called acc1152 with the field accno. depending on what the value of this field is, i need to replace it with a new value. These are the values i need to update

old value new value
7007 4007
7008 4008
4008 7
7009 4009
7011 4011
4011 ' '
7010 4010
4010 1
7016 4016
4016 1
4506 4006
4512 4012

how do I write one query that will accomplish this?

View 3 Replies View Related

Track The Changes To Normalised Tables And Update The Denormalised Tables Depending On The Changes To Normalised Tables

Dec 7, 2006

We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process

View 3 Replies View Related

Data To Get From 3 Different Tables Depending On Some Conditions

Nov 2, 2007

Hi,

I know it sounds very simple with the subject line but for me it's not.

BACK GROUND

My Client is using Crystal Reports 8.5 and SQL Server.
a View is bound to this report and they use Select Expert of CR 8.5 IDE to generate Reports manually.

now there are 3 tables in Question
Billing_Revenue, Billing_Revenue_History and Billing_Revenue_Revision
and the Fields in Question are Interstate_Revenue and International_Revenue.

Every end of the Quarter the Billing_Revenue data is moved to Billing_Revenue_History.
Billing_Revenue_Revision has the revised data if the "Filer" has submited the changed/Revised Data again to the Company.

Bill Run = is the Process they call when they generate the Reports for a perticular Billing Period.
they do it every month. But sometimes they need to generate Invoices for the OLD Periods.

if we put the problem in simple words,

they want to get the Revenue Columns from Billing_Revenu if they run the "Bill Run" in the Current Quarter (Billing Cycle),
else if the Billing Cycle is older than current Quarter (cycle), then they want to first check in the Revision Table and if that Filer has submitted the revised data in that Cycle then get it from there
else
look into History Table and if the filer is found there for that cycle than get the data from there

here's the requirement given to me by my system Analyst.

User enters list of filer ID=USER_FILER_ID and cycle ID=USER_CYCLE_ID

Get CURRENT_CYCLE= Max CYCLE_ID from Billing_Cycle
Get USER_PERIOD_ID = Period ID from Billing_Cycle using USER_CYCLE_ID
Get PERIOD_FIRST_CYCLE= Min Cycle ID for USER_PERIOD_ID from Billing_Cycle

For each USER_FILER_ID
If USER_CYCLE_ID=CURRENT_CYCLE
Pull revenue from BILLING_REVENUE /*Process stops here*/
ELSE
{
If USER_PERIOD_ID exists in BILLING_REVENUE_REVISIONS BRR for USER_FILER_ID

Get the max (cycle_id) from BRR to pull revenue columns with most recent filing of USER_PERIOD_ID from BRR /*one filer may have more than one revision for the same period id*/

ELSE

IF PERIOD_FIRST_CYCLE exists in BILLING_REVENUE_HISTORY for USER_FILER_ID
Pull revenue information
}

here's the View being used which is the First Case which is happening currently


SELECT dbo.BILLING_PROVIDER.CONT_NAME_FIRST, dbo.BILLING_PROVIDER.CONT_NAME_LAST, dbo.BILLING_PROVIDER.ADD_STREET_LINE2,

dbo.BILLING_PROVIDER.ADD_STREET_LINE1, dbo.BILLING_PROVIDER.ADD_STREET_LINE3, dbo.BILLING_PROVIDER.ADD_CITY,

dbo.BILLING_PROVIDER.ADD_STATE, dbo.BILLING_PROVIDER.ADD_ZIP, dbo.BILLING_PROVIDER.CONTACT_TEL,

dbo.BILLING_SUMMARY.INVOICE_NUM, dbo.BILLING_SUMMARY.BALANCE_DUE, dbo.BILLING_SUMMARY.PREVIOUS_BALANCE,

dbo.BILLING_SUMMARY.SUM_TRANS, dbo.BILLING_SUMMARY.M_BASE, dbo.BILLING_SUMMARY.M_CONTRIBUTION,

dbo.BILLING_SUMMARY.ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.EST_Q_CONTRIBUTION,

dbo.BILLING_SUMMARY.PRE_DM_ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.Q_CONTRIBUTION_BASE,

dbo.BILLING_SUMMARY.Q_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.Q_LIRE_STATUS,

dbo.BILLING_SUMMARY.A_DM_STATUS, dbo.BILLING_SUMMARY.A_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_DM_STATUS,

dbo.BILLING_SUMMARY.A_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.A_LIRE_STATUS, dbo.BILLING_DETAILS.TRANSACTION_TYPE,

dbo.BILLING_DETAILS.LINE_ITEM_TYPE, dbo.BILLING_DETAILS.SUPPORT_MECHANISM_TYPE, dbo.BILLING_DETAILS.LINE_ITEM_NUMBER,

dbo.BILLING_DETAILS.LINE_ITEM_AMT, dbo.BILLING_DETAILS.TRANSACTION_DATE, dbo.BILLING_DETAILS.REASON_CODE,

dbo.BILLING_CYCLE.STATEMENT_DT, dbo.BILLING_CYCLE.PAYMENT_DUE_DT, dbo.BILLING_CYCLE.MAILING_DT,

dbo.BILLING_REVENUE.INTERSTATE_REVENUE, dbo.BILLING_REVENUE.INTERNATIONAL_REVENUE, dbo.BILLING_SUMMARY.CYCLE_ID,

dbo.BILLING_SUMMARY.FILER_ID, dbo.BILLING_PROVIDER.CARRIER_NAME, dbo.BILLING_CYCLE.PERIOD_ID,

dbo.BILLING_PERIOD.PERIOD_TYPE_ID, dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_DESCR, dbo.BILLING_PERIOD.FCC_PERCENTAGE_ACTUAL,

dbo.BILLING_PERIOD.FCC_PERCENTAGE_EST, dbo.BILLING_PERIOD.HC_PERCENTAGE, dbo.BILLING_PERIOD.SL_PERCENTAGE,

dbo.BILLING_PERIOD.RHC_PERCENTAGE, dbo.BILLING_PERIOD.LI_PERCENTAGE, dbo.BILLING_SUMMARY.U_CONTRIBUTION_BASE,

dbo.BILLING_PERIOD.FCC_CIRCULARITY_FACTOR, dbo.BILLING_SUMMARY.A_LIRE_REVENUE, dbo.BILLING_SUMMARY.ANNUAL_BASE,

dbo.BILLING_REVENUE.RECEIVED_DT

FROM dbo.ACCT_LINE_ITEM_TYPE RIGHT OUTER JOIN

dbo.BILLING_DETAILS RIGHT OUTER JOIN

dbo.BILLING_PERIOD INNER JOIN

dbo.BILLING_CYCLE INNER JOIN

dbo.BILLING_SUMMARY ON dbo.BILLING_CYCLE.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON

dbo.BILLING_PERIOD.PERIOD_ID = dbo.BILLING_CYCLE.PERIOD_ID ON dbo.BILLING_DETAILS.FILER_ID = dbo.BILLING_SUMMARY.FILER_ID AND

dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON

dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_TYPE = dbo.BILLING_DETAILS.LINE_ITEM_TYPE LEFT OUTER JOIN

dbo.BILLING_PROVIDER LEFT OUTER JOIN

dbo.BILLING_REVENUE ON dbo.BILLING_PROVIDER.FILER_ID = dbo.BILLING_REVENUE.FILER_ID ON

dbo.BILLING_SUMMARY.FILER_ID = dbo.BILLING_PROVIDER.FILER_ID

WHERE (dbo.BILLING_SUMMARY.FILER_ID NOT IN

(SELECT DISTINCT BILLING_DETAILS.FILER_ID

FROM dbo.BILLING_DETAILS

WHERE (dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_CYCLE.CYCLE_ID) AND (BILLING_DETAILS.LINE_ITEM_TYPE IN ('SLDADJ', 'SLDC',

'RHCDC', 'RHCDADJ'))))

Current Situation.
1. They use CR 8.5 IDE to enter the parameters
and this version of CR doesn't allow using Stored PRocedures.
2. I'm not very good with the Database side and Complex Queries always leave me in the middle of nowhere.
But i really really need to do this
3. please give me some clue.. some idea how to resolve this.


here's the primary database diagram
http://deepak.palkar.googlepages.com/Billing2.jpg

thanks a lot..
Deepak

View 3 Replies View Related

Transact SQL :: Using EXISTS In SELECT List

Feb 18, 2010

I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.

View 15 Replies View Related

Insert Identity Depending On The Value Of A Column

Apr 23, 2008

Would like to have Identity Inserted wrt a date column
Eg:
ProdDate ID Details
============================
2008.04.01 1 afafafaf
2008.04.01 2 GAFSGHFGF
2008.04.02 1 GAGJAGSDH
2008.04.02 2 QYTYTT
2008.04.03 3 QYTWRRT

At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?

View 13 Replies View Related

How To Sum A Column Depending Of Another Colum Into Different Vars?

May 20, 2006

Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example

DECLARE @Initial decimal(18,2), @incomings decimal(18,2), @outgoings decimal(18,2)

SELECT
@initial = CASE WHEN type = 1 THEN SUM(amount) END,
@incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount

What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where
The incoming transactions is type 2,
outgoing transactions is type 3

Thanks for the help


@puy

View 5 Replies View Related

Number Of Rows Depending On A Column Value

Sep 26, 2007



Hi all,
I have a table with artikels and count, sample:

Art Count
------------
12A 3
54G 2
54A 4

I would like to query this table and for each 'count' retrieve one row:
query result:

Art Count
------------
12A 3
12A 3
12A 3
54G 2
54G 2
54A 4
54A 4
54A 4
54A 4



Is this possible?

Thanks, Perry

View 3 Replies View Related

Trigger To Work Depending On Datatype And Column Name

Jan 24, 2008

Hi,

I have a trigger that fires on update, populating a varchar field [ExampleDate_Str] with the dd/mm/yy format of the inserted datetime field [ExampleDate]. It works, but I don't want to rewrite it for the thousands of datetime fields in hundreds of tables in my db. So I am looking for a way to do something like this in a trigger -

for each column in triggered tableset @ColName = (the column name)if datatype(@ColName) = datetimeset @ColName + "_Str" = convert(varchar(8),ExampleDate,3)next

Obviously the above doesn't work in SQL or indeed any other language yet invented - is it possible to make it so that it does, and if so, how? The main points are to read the column name into a variable, check the datatype, and modify the column named @ColName + "_Str".

Thanks...

View 7 Replies View Related

How Can I Split Fields And Depending One Column Decide The Foreing Key

Oct 19, 2007

I´m wondering how to solve the following scenario with SSIS

I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY:


the state table is like this:


StateCode(PK) stateLegalCode stateName
============= ============== =========
1 01 Florida


the city table is like this:


citycode(PK) cityLegalCode cityname StateCode(FK)
============ ============= ======== =============
1 1001 Quakertown 1


the file has the following information


cityLegalCode cityName
============= ========
01-1001 Quakertown
...

how can I load the file into CITY table:

1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK).

how can I do something like that using SSIS???

thanks

View 3 Replies View Related

Error: Only One Expression Can Be Specified In The Select List When The Subquery Is Not Introduced With EXISTS.

Nov 7, 2007

Hi,
  When i try to save my stored procedure.. i am getting the above error and this is my sproc
 1 INSERT INTO Statement..ClientSources
2 (
3 ClientId,
4 ClientSourceId,
5 SourceName
6 )
7 Select Distinct
8 @ClientId,
9 SOURCE_NUM,
10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
11 WHEN 2 Then SRC2NAME
12 WHEN 3 THEN SRC3NAME
13 WHEN 4 THEN SRC4NAME
14 WHEN 5 THEN SRC5NAME
15 WHEN 6 THEN SRC6NAME
16 WHEN 7 THEN SRC7NAME
17 WHEN 8 THEN SRC8NAME
18 WHEN 9 THEN SRC9NAME
19 WHEN 10 THEN SRC10NAME
20 WHEN 11 THEN SRC11NAME
21 WHEN 12 THEN SRC12NAME
22 WHEN 13 THEN SRC13NAME
23 WHEN 14 THEN SRC14NAME
24 WHEN 15 THEN SRC15NAME
25 END
26 FROM
27 PlanDBF p
28 Where
29 p.PLAN_NUM = s.PLAN_NUM
30 ) as SourceName
31 FROM
32 SourceDBF s
33 Where
34 SOURCE_NUM NOT IN (
35 SELECT DISTINCT
36 ClientSourceId
37 --SourceName
38 FROM
39 Statement..ClientSources
40 Where
41 ClientId = @ClientId
42 )

 
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name  i get this error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Any help will be appreciated.
Regards
Karen

View 4 Replies View Related

SQL 2012 :: Only One Expression Can Be Specified In Select List When Subquery Not Introduced With EXISTS

Apr 28, 2015

I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.

==============================================
Declare @mSql2 Nvarchar(max)

SET @mSql2= (select * from Tablename)
================================================

View 2 Replies View Related

SQL 2012 :: Insert ID Column Value Into Table Depending Upon Input Filename

May 19, 2015

I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.

Lets Say
Location1 : C:LoadFilesImport
Location2 : D:LoadFilesImport

Location one has three different set files starting with

First Set: AP_1, AP_2,AP_3,
Second Set: VD_1, VD_2, VD_3,
Third Set: BK_1,BK_2,BK_3,

This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101

AP_1 goes to Table1
AP_2 goes to Table2
AP_3 goes to Table3

If the file name Starts with VD_ , custiD values should be as 201
If the file name Starts with BK_ , custiD values should be as 301

after processing all these files in first location, the SSIS looks for files in second location and does the same?

--How to achieve CustID depending upon file name ?

View 0 Replies View Related

Insert Value List Doest Not Match Column List

Apr 18, 2007

HI...



I need to do a simple task but it's difficult to a newbie on ssis..



i have two tables...



first one has an identity column and the second has fk to the first...



to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!



i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"



here is the script



INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)



what's the problem ??

View 7 Replies View Related

Derived Column Usage When Column Does Not Exist In Source (but Exists In Destination)

Sep 11, 2007





Posted - 09/10/2007 : 15:53:26



Hey all - got a problem that seems like it would be simple (and probably is : )

I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.

Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:


'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.

thanks in advance!

jm

View 1 Replies View Related

Set Column Value Depending As % Of Another Column Value (was: Help!!)

May 11, 2007

Hi,
I'm not sure if this can even be done but I will ask anyway.
I have a table that has a sales price field in it. I have another field in that table called commission. The commission is 15% of the sale price. Is there any SQL that will calculate and automatically put that value in it, or only allow you to put the correct value in?
Hope this makes sense and hope someone can help.
Thanks for your time.
Liam
(Using MS SQL by the way)

View 5 Replies View Related

Using Not Exists Between Two Unrelated Tables

Apr 23, 2008



Hello,

I'm struggling with a query that needs to use Not Exists between two unrelated tables. I'm not sure of the best way to handle this with SQL Query Analyzer.

I have three tables, employees, courses and training_records. There is no link between employees and courses other than through the training records table. My problem is that I am unsure of how to pull all employees and an associated course id and course title when there is not a training record for that employee/course?

I had a query that would work when I specified the course id, but I wanted to do this for a list of about 25 courses.


SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName

FROM empdata e

WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr

LEFT JOIN #TrainingCourses tc

ON tc.ClassID = tr.classid

WHERE tc.ClassID = 1377

AND tr.employee = e.empnumber)

I then modified it to get the classid and name

SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName, tc.ClassID, tc.ClassName

FROM empdata e

LEFT JOIN #TrainingCourses tc

ON tc.ClassID = 1377

WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr

WHERE tr.ClassID = tc.ClassID

AND tr.employee = e.empnumber)


I then got it to work using the full list


SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName, tc.ClassID, tc.ClassName

FROM empdata e

LEFT JOIN #TrainingCourses tc

ON tc.ClassID in (1032, 1054, 1059, 1060, 1062, 1063, 1069, 1072, 1074, 1075, 1122, 1189, 1190, 1191, 1192, 1193, 1210, 1218, 1219, 1220, 1310, 1377, 1411)

WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr

WHERE tr.ClassID = tc.ClassID

AND tr.employee = e.empnumber)

My results seem to work like I want them to, but I have never joined a table without actually joining on a value from each table. In this case I join to a list of values and that just looks odd to me. Is this good or crappy development? Is there a better way this should be done?

View 6 Replies View Related

Running Selected Not Exists On Two Tables On Two

Feb 29, 2008

Hi all,


I have the following SQL script that works fine, but I like to view all the fields for the records that are not exists in “capdb.dbo.abc “ for “capdb2.dbo.abc� table instead of some fields :

select distinct cp2abc.subj_num , cp2abc.abc_age, cp2abc.ABC_LETHARGY, cp2abc.ABC_STEREOTYPY
, cp2abc.ABC_STEREOTYPY, cp2abc.ABC_HYPERACTIVITY, cp2abc.ABC_INAPPROPRIATE_SPEECH


from capdb2.dbo.abc as cp2abc, capdb.dbo.abc as cp1abc

where not exists

(select cp1abc.subj_num, cp1abc.abc_date from capdb.dbo.abc as cp1abc where cp2abc.subj_num = cp1abc.subj_num

and cp2abc.abc_DATE = cp1abc.abc_date)




I tried cp2abc.*, but the returns entire the records in the tables.



select cp2abc.*

from capdb2.dbo.abc as cp2abc, capdb.dbo.abc as cp1abc

where not exists

(select cp1abc.subj_num from capdb.dbo.abc as cp1abc where cp2abc.subj_num = cp1abc.subj_num

and cp2abc.abc_DATE = cp1abc.abc_date)



Is there a way to accomplish this
Thanks for any help.

Regards,

Abrahim

(moved from Script Library by Jeff)

View 1 Replies View Related

Dynamic Sql - How To Use 'if Exists' With Variable Tables..?

Jul 20, 2005

Hi allIn the SP below im (trying to) do some dynamic sql. As you can see the tableto use is set as a variable and the 'exec' method used to run thesqlstatements.My problem is that the 'if exists' method is not doing what i was hoping itcould do.The @presql command returns somewhere between 0 or 50 rows (give and take) -i just want the 'if exists' part to determine if the select statementreturns something or not since i then will have to update a current row - orinsert a new one.Even if there is no rows returned, the 'if exists' command will return true:-/Any suggestions to a different way of approach...?Thanks in advance :-)######## Stored procedure start ########[various @ variables]....declare @presql varchar(200)select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as varchar(6))+''IF exists((@presql))BEGINdeclare @UpdateSQL varchar(400)set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate asvarchar(12))+''''exec(@UpdateSQL)ENDELSEBEGINdeclare @InsertSQL varchar(2000)select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '(btDate,btTime)VALUES('''+ cast(@FileDate as varchar(12)) + ''','+ cast(@ImportTime as varchar(6)) + ')'EXEC(@InsertSQL)END######## Stored procedure end ########

View 2 Replies View Related

Finding If A Record Exists In Either Of Two Tables

Mar 17, 2008



Hello,

I have two tables with the same field layout, and they both have the same field as the Primary Key. They just contain different data. I would like to know if a record exists in one, or both, tables.

The tables are InvTemp1 and SalesTemp1. The key for both is stock_number.

Here is the command so far:


SELECT COUNT(*)

FROM InvTemp1 INNER JOIN SalesTemp1 ON InvTemp1.Stock_number = SalesTemp1.Stock_number

WHERE (InvTemp1.Stock_number = '101053')

Thank you for any ideas,
Tom

View 3 Replies View Related

If Exists Column ?

Nov 30, 2005

Hello

How do you check if a column exist ?

for a table :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO

but I dont find it for a column

Thank you

View 4 Replies View Related

Inserting Unique Values Into A Different Tables If They Don't Exists Already.

Jul 30, 2007



Hi

I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.




Code Snippet

SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'







WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM Members M



INNER JOIN _MemberProfileLookupValues ML

ON M.MemberID = ML.MemberID

WHERE M.Active = 1

AND OptionID <> 6






When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID

I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately

ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.

Any ideas / help will be greatly appreciated. Please help.

Kind Regards
Carel Greaves

View 3 Replies View Related

Transact SQL :: Creating PK On All Existing Tables Where It Does Not Exists?

May 6, 2015

I have been asked to create PK on many tables using a query on all tables where we do not have clustered indexes. Some of the tables contains PK but non-clustered. If in a table there are no PK, then how to decide on which column PK can be created? can we do it with the query without data loss and without human intervention?

View 5 Replies View Related

Email If Errors Exists In The Error Tables

Aug 7, 2007



I am setting my error tables.What i want to do is.If i find any data within my error tables i want an email to be sent to me.

How can i achieve this??
Please let me know

View 7 Replies View Related

How To: Check If A Column Exists, And If Not, Add It?

Feb 5, 2007

Well, actually, as in the title. I have a table. The script should add a column if that column doesn't exist already. I use VB to combine the two queries. So what I want:

Query1: Does the column exists:

if No,

query2: create the column

How can I achieve this?

View 6 Replies View Related

Error: Column Name X Appears More Than Once In The Result Column List.

Oct 3, 2006

Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below.  Any clues on how to fix this?  Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: }
Line 1445: try {
Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
Line 1447: return returnValue;
Line 1448: }

View 3 Replies View Related

How Can I Get All Records For Both Tables With The Latest Begin Date If Exists?

Jun 15, 2006

Itemlookup tableField names : index_id (primary key), itemno, description.It has a child table, which is ItemPriceHistory tableThe relationship to the child table is one (parent table)-to-many(child table). - It is possible to have no child record for some rowsin the parent table.ItemPriceHistory tableField names: index_id (primary key), itemlookupID (foreign key of theItemlookup table), date begin, priceIt is a child table of the itemlookup table.How can I get all records for both tables with the latest begin date ifexists?I also need to show the records in the parent table if there is norelated record in the child table.Please help

View 4 Replies View Related

Whether A Column Exists In My Table?(Urgent)

Feb 4, 2004

Hi

Can anybody tell me how can I find whether a column exists in my table or not???

Piyush

View 12 Replies View Related

How To Check That Column Does Not Exists Before Adding It?

Jun 22, 2007

How to check to make sure a column does not exist before adding it? Be nice to do this in t-sql code instead of using ado.net. It seems as if the IF NOT EXISTS is not supported in SqlCe 3.1? I am trying to do this but I get the token error:

IF NOT EXISTS (
select *
from Information_SCHEMA.columns
where Table_name='authors' and column_name='NewColumn'
)
select 'no'


Is there a list of all CE supported t-sql commands?

Thanks,

Dan

View 5 Replies View Related

SQL Server 2008 :: If Exists Placement Theory For Optional Tables

Nov 4, 2015

I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.

Surgery table
CaseID
Patient
(Sample data: 101,SallyDoe 102,JohnDoe)

Anesthesia table
CaseID
CrnaID
(Sample data:
101,Melvin
102,Bart
102,Jack)

AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases)
CaseID
CrnaID
TimeIn
TimeOut
(Sample data:
102,Jack,0800,1030
102,Bart,1030,1130
102,Jack,1130,1215)

Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table. I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.

View 2 Replies View Related

How To Find That The Table Column Exists Through Program

Jul 12, 2007

i need to check that the column exist in the table if yes the update/insert value in the column else i need to add the new column like new browser name in the table.

View 1 Replies View Related

How To Check If Column Exists In Temporary Table

Jun 13, 2008

I am trying
IF OBJECT_ID(''tempdb..#tempTable.Column'') IS NOT NULL



But its returning me a null every time. Is there any other way to check if column exists in temporary table.

View 4 Replies View Related







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