Select Statement Not Importing All Rows From AS400

Oct 4, 2006

Hello all,
I am trying to select a specific amount of rows from an AS400 table and when I do so it only shows a specific amount of rows regardless of how many there actually is. If we run it multiple times, it displays the same amount of rows. We went into the iSeries ODBC and changed the timeout setting and the Record Blocking Size (increased it) and it did pull more rows but still not what we were looking for. We suspect it has something to do with pulling the specific amount of blocks and instead of continue to pull, it stops when it reaches the first blocking limit. I can however pull the information through MS Access with the same settings. The query we are using to pull the information is

Select * Into Table From Openquery(AS400,'Select * From Tablename')

Was wondering if anyone has seen this before and if so if they found a fix so we can pull everything we are looking for.

Thanks!

View 2 Replies


ADVERTISEMENT

Using A Select Statement To Only Insert In Certain Rows

Jan 14, 2005

Hey,

I am not sure how to really explain this, but I'll give it a try.

I am looking to use a select statement in a way that I can tell it which rows to insert in depending on when only one result is returned. For example, if I run this statement:

SELECT Column1, Column2, Column3
FROM #Temp1

The result set is:

Column1---Column2---Column3
99--------6756756---55555
44--------55---------NULL

Column3 as only the one returned value, so I do not want it associated with any of the other rows, so I need this:

Column1---Column2---Column3
NULL------NULL------55555
99--------6756756---NULL
44--------55---------NULL

Another example:

The returned result now is:

Column1---Column2---Column3---Column4
99---------6756756---55555-----NULL
42---------55---------NULL------12345

So I need:

Column1---Column2----Column3----Column4
NULL-------NULL-------55555------NULL
NULL-------NULL-------NULL-------12345
99---------6756756----NULL-------NULL
44---------55----------NULL-------NULL


Does this make sense, and/or is it even possible?

I know it could be more of a presentation thing, but I would like to know how to do it in the code behind.

Thanks

View 2 Replies View Related

Select Statement Combining Rows

Mar 30, 2008

I need some help with a query . I have two tables "config" and "item".

I have table config as follows:
ConfigID
ItemID

With Values
ConfigID ItemID
14583 2776
14583 2798
14583 3112

And table item as follows:
ItemID
ItemTypeID
ItemValue

With Values
ItemID ItermTypeID ItemValue
2776 1 123
2798 2 ABC
3112 3 789

So the query:
SELECT ConfigID,
(SELECT ItemValue WHERE ItemTypeID = '1') AS Model,
(SELECT ItemValue WHERE ItemTypeID = '3') AS Minor,
(SELECT ItemValue WHERE ItemTypeID = '2') AS Customer
FROM config c, item i
WHERE ConfigID = '14583'
AND c.ItemID = i.ItemID

Produces the result:
ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL

How do I change the above query to get one row:
ConfigID Model Minor Customer
14583 123 789 ABC

Thanks for your help

View 6 Replies View Related

Return Number Of Rows In Select Statement As Value

Sep 3, 2007

Can anyone just point me in the right direction.  All I want to do is add some T-SQL to an existing stored procedure to return the number of rows selected into a return value.Does anyone know how to do this? 

View 4 Replies View Related

Retrieve Distinct Rows From Select Statement

Jan 10, 2014

Is it possible to retrieve Distinct rows from this Select Statement?

I'm getting the correct results, but duplicate rows because some customers place more than one order on the same day.

Code:
SELECT dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.CustomerEmail, dbo.Customers.DateCreated,
CONVERT(char, dbo.Customers.DateCreated, 103) AS [DD/MM/YYYY], dbo.loyalty_points.LPoints, dbo.Orders.OrderID
FROM dbo.Customers INNER JOIN
dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (CONVERT(char, dbo.Customers.DateCreated, 103) = CONVERT(char, GETDATE() - 6, 103))

View 8 Replies View Related

Select Statement That Will Output Related Rows With Different Column Data Per Row?

Apr 27, 2008

Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:


rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...

where for each ID, I have 3 rows that are associated with it and with different corresponding values.

I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.

i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..

After my loop, I will just do a select * from #someTempTable

Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005

View 2 Replies View Related

Transact SQL :: Transpose Part Of Rows To Columns In Single Select Statement

Aug 31, 2015

Below. I have also pasted the current result of this query and the desired result. 

Query can be updated to get the desired result as given below?

Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'

Current Result which I am getting with this query

OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01

Desired/Expected Result for which I need updated query

OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3

5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01

Using DB2.

View 2 Replies View Related

Insert Rows To AS400 DB2 Now Fails Following Upgrade

Apr 20, 2007

I have an SSIS job that selects rows from a Sql Server table and inserts them into a table on an AS400 DB2 instance. This process has been running correctly for many months. Last week, the AS400 operating system was upgraded from V5r3 to V5r4 and since that upgrade, the SSIS job is failing on the insert step with the following error:

OLE DB provider "DB2OLEDB" for linked server "BKL400" returned message "SQLDA or descriptor area not valid. SQLSTATE: 07002, SQLCODE: -804".

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "DB2OLEDB" for linked server "BKL400" could not INSERT INTO table "[BKL400].[BKL400].[MM4R4LIB].[INV911WK]".



The insert is in an Execute SQL task and uses a linked server definition to access the AS400.



As I said, this process has been working well for many months until the OS upgrade. Any idea on what is causing this and how to correct it will be Greatly appreciated!

View 7 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

SQL Server 2014 :: Select Statement Returning Too Many Rows For Backup Start Date

Jun 3, 2015

Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.

select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'

[Code] .....

View 8 Replies View Related

AS400 And SELECT *

Jul 31, 2007

I am migrating from 2000 to 2005 and dts to SSIS on several projects. I have not been able to successfully (without error) pull from an AS400 table using "SELECT *". If I reference all field names, it works fine. SELECT * actually works in the sense that it pulls over all the data, but it fails at the end, almost like an unepected end of stream. Normally it wouldn't be a problem tp qualify each field, but when I do the data gets sorted. In this instance I can't have it sorted because (without going into the stupid details) the production needs to match the development to suit my boss.

Connection = .Net Provider for OleDb/IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
Data Source = DataReader Source (I have yet to be able to use and OLE Source for AS400 without error, although my colleagues can - weird)

Here is the error I get when I change:

SELECT FieldName1, FieldName2, FieldName3 FROM LIBRARY.FILENAME

to

SELECT * FROM LIBRARY.FILENAME


Error: 0xC0047038 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE_SRC_Tebosspf8H" (4492) returned error code 0x80004003. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

I

Any suggestions? Is there a property on the task or connection that will fix this? Or any idea why I can't use an OLE Source to pull from AS400?

View 1 Replies View Related

How To Select A Different Member Name When Transfering Data From AS400 To SQL

Apr 4, 2007

I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.



The name of the file is:

Library = MTGLIBP2

File Name = CHSAVQPL

Member Name = INS



this is the query I have so far but I still need to reference the Member Name



SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')

View 1 Replies View Related

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

Rows Not Importing Through BCP

Jul 20, 2005

Hello,I am importing a file using BCP, with a format file. It is afixed-width file, which I am importing into a table that has a columnfor each field in the file. The columns in my import table are alldefined as CHAR. I use it as a staging area to check the data (aredates formatted correctly, etc.) before moving it from there.In one of my files I have six records that contain strange characters(ascii codes of 03 and 02 if I am remembering correctly). When Iimport the file, these six records do not get imported. Not only that,but if I use the error file option with BCP, nothing is put in theerror file either. As far as I can tell, it is just skipping rightover the rows. If I import the file into a table with a single columnthat holds the entire row of data, all rows get imported.I've included a sample of the format file below (there are 106 fieldsin the real one, so I've abbreviated it for space). I've also includeda table definition as well.Has anyone seen this behavior before? Any ideas?Thanks and Happy New Year!-Tom.Table Definition:-----------------------------------------------------------CREATE TABLE [dbo].[BIF_Pre_Staging] ([dda_num] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[account_app_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[cust_num] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CUST_ACT_OWNER_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[do_not_mail] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[employee_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ACCOUNT_TAX_NUM] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[CUS_PFS_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SUB_TIER_VALUE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO-----------------------------------------------------------Sample Format File:-----------------------------------------------------------8.091 SQLCHAR018""1 dda_num ""2 SQLCHAR04 ""2 account_app_id ""3 SQLCHAR010""3 cust_num ""4 SQLCHAR01 ""4 CUST_ACT_OWNER_TYPE ""5 SQLCHAR06 ""5 do_not_mail ""6 SQLCHAR01 ""6 employee_code ""7 SQLCHAR09 ""7 ACCOUNT_TAX_NUM ""8 SQLCHAR01 ""8 CUS_PFS_FLAG ""9 SQLCHAR05 "
"9 SUB_TIER_VALUE ""-----------------------------------------------------------

View 1 Replies View Related

ASP.net And MS SQL - Importing 3000-100000 Rows

Jul 6, 2006

Hi there,

 

I am
currently planning an ASP.NET and MS SQL project to distribute customer codes
to my customers. All the codes will be stored in the database, and then when a
customer registers, the next available code is linked to their account. Does
this sound like a feasible solution? The number of codes (ie. rows) could be
anywhere from 3,000 to 100,000 – but will only contain a couple of fields.

 

Also, it is
a separate company that is producing the codes (we’re offering them as an
incentive) and I was wondering the best way for them to get the codes to me, so
I can easily import them into MS SQL? CSV / Excel ??

This is the
main problem I need help with.

 

 

 

Thank you
all for your continued support, and all the work you kind guys do here!

 

Nathair

View 1 Replies View Related

Excluding Rows On A Table While Importing

Jul 11, 2007

I'm using DTSWizard to import a table from my main database to Temp.



This is the SQL statement...



CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)



What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.

Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?



This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.



Thanks,

Bill

View 16 Replies View Related

Importing Non-hidden Rows From Excel?

May 15, 2006

I have a need to import only non-hidden rows from an excel spreadsheet. When I create the package in SSIS, it imports everything. Due to the use of the data on the spreadsheet, we cannot simply delete the data.

Is there a special setting in the Excel Source or Connection manager that can be set to "only import non-hidden rows"?

Also, how do I go about setting the sheet with an index instead of the actual Sheet name? The user changes the sheet name at random, but I know I only need the first two sheets on the file.



Thanks!

Matt Michuta

View 2 Replies View Related

Importing Excel Data That Spans Rows

Mar 7, 2006

Hi,

I need to import and transform data from an Excel spread sheet where the information spans two rows. The file layout is something like:

Row1Product1 QTY Store1 Store2 Store3 ...
Row2Product1 AMT Store1 Store2 Store3
Row3Product2 QTY Store1 Store2 Store3
Row4Product2 AMT Store1 Store2 Store3

The output would look like

Product1 Store1 QTY AMT
Product1 Store2 QTY AMT
...
Product2 Store1 QTY AMT
Product2 Store2 QTY AMT
...

We currently use a VB6 program using Office Tools to handle this. Is there a way to handle this with the out-of-the-box SSIS?

Thanks in advance,

John

View 6 Replies View Related

Limit Number Of Rows When Importing A Flat File

Nov 22, 2006

Hello,



Using ssis, how can I limit the number of rows I wish to import from a flat file?



thanks in advance

View 4 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

Select Distinct Rows From Duplicate Rows....

Nov 28, 2007

Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!

View 4 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Is There A TSQL Statement For Importing A Singular Table Into Sql Server 2005 From Access?

Jan 18, 2008

Hello all.

I was wondering if there was a simple Import statement I could use in SQL to import an Access Table into SQL Server 2005.

I know how to use the SSIS Import/Export Wizard, but that seems excessive to import a single 204 record table

Any help on this would be greatly appreciated.

View 3 Replies View Related

SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows

Dec 12, 2014

I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).

SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

View 5 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

Help With Delete Statement/converting This Select Statement.

Aug 10, 2006

I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID

I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:

SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180

View 1 Replies View Related

Select Statement Problem - Group By Maybe Nested Select?

Sep 17, 2007

Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt 

View 2 Replies View Related

SELECT * Not Returning Any Rows, But SELECT COL_NAME Does!

Jul 20, 2005

I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.

View 9 Replies View Related

SQL Server 2012 :: SELECT INTO - Importing A Text File Into A New Table?

Jun 6, 2015

how to import a text file with a list of NI numbers into a new table with a column to list all the NI numbers? I think I use the Select INTO clause, but not sure how to do this?

View 1 Replies View Related







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