Masking Table Of ID Numbers - Populating A Column

Aug 29, 2013

I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...

View 4 Replies


ADVERTISEMENT

Populating Empty Table With Sequential Numbers?

Jan 15, 2015

I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.

what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.

I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.

View 5 Replies View Related

Adding And Populating A Column To Existing Table

Dec 5, 2005

Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.

Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.

Any ideas?

View 2 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

Add New Table Column With Numbers

Jul 19, 2007

Hello.

Whats the easiest way to add a column with a numeration to my table?

I want the users to have an easier life finding on which row they are
talking.

I preffer of course doing it in reporting service and not adding a
column in my dataset.

Thanks.

View 1 Replies View Related

Updating Table Column With Cumulative Numbers

Nov 13, 2007

Hello,

One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?

Thanks a lot,
Andy

SQL version: 2005, Standard edition.

View 1 Replies View Related

Inputting Random Numbers To Table Column From Web Form

Feb 28, 2007

I'm grappling with this issue which I thought was basic VB programming; I'm trying to insert a random number (between 100 and 999) into a SQL table column (=Status_ID). This is input as part of a user submitting helpdesk requests via a APS.Net Web Form. The 'Status_ID' field is obviously not visible to the user but will help reference this Helpdesk request on the database.Here is the code:Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click        If Page.IsValid Then            ' Define data objects            Dim conn As SqlConnection            Dim comm As SqlCommand            ' Read the connection string from web.config            Dim connectionString As String = _            ConfigurationManager.ConnectionStrings("ITNet_Students").ConnectionString            ' Initialize connection            conn = New SqlConnection(connectionString)            ' Create command            comm = New SqlCommand( _            "INSERT INTO HelpDesk (First_Name, Last_Name, StudentID, PersonalEmail," & _            "CategoryID, SubjectID, Description, StatusID) " & _            "VALUES (@First_Name, @Last_Name, @StudentID, @PersonalEmail, " & _            "@CategoryID, @SubjectID, @Description, @StatusID)", conn)            ' Use randomize            Randomize()            Dim randomvalue As Integer            ' Generate random value between 999 and 100.            randomvalue = Int((900 * Rnd()) + 100)            ' Add command parameters            comm.Parameters.Add("@First_Name", System.Data.SqlDbType.NVarChar, 50)            comm.Parameters("@First_Name").Value = fnameTextBox.Text            .            .            .            comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int)            comm.Parameters("@StatusID").Value = randomvalue            'Enclose database code in Try-Catch-Finally            Try                ' Open connection                conn.Open()                ' Execute the command                comm.ExecuteNonQuery()                ' Reload page if the query executed successfully                Response.Redirect("HelpDesk.aspx")            Catch                ' Display error message                dbErrorMessage.Text = _                    "Error submitting the help desk request! Please try again later, and/or change the entered data!"            Finally                'close connection                conn.Close()            End Try        End If    End Sub----------------------------------------------------------------------------------------------------------------------I keep getting the error message under 'Catch'  and the page 'HelpDesk.aspx' is not reloading; the 'comm.ExecuteNonQuery()' is not executing.Can anyone spot any inconsistencies in the declaration of the 'randomvalue' variable?P.S: this code works fine if you replace 'randomvalue' with any integer in 'comm.Parameters("@StatusID").Value = randomvalue' 

View 2 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Best Way To Handle Populating Column?

Jul 2, 2014

Have two tables: table1 and table2

Working with two main columns in both tables: D_ID and C_ID.

Table1 has D_ID populated and C_ID is not.

Table2 is a cross reference table with both D_ID and C_ID values within.

Looking for best way to populate C_ID in table1 from C_ID values in table2 where table1.D_ID = table2.D_ID.

There are too many values to do a case in stored procedure which has been my best practice. Trying to up my game with using SSIS 2012 as well.

View 1 Replies View Related

Populating A Blank Column Beside A Populated One

Aug 23, 2006

I have a table with two columns:
OwnerName            Owner
John;Smith
Mary;Smith
 
OwnerName is populated. Owner is not.
I want to populate the Owner column with the OwnerName in alphabetical order.  I have already created a function to do this.
Select fnGetOwner(OwnerName) from OwnerTable.  This returns:
Smith, John
Smith, Mary
How do I populate the blank Owner field beside the OwnerName in the OwnerTable?
 

View 3 Replies View Related

Populating A Column With New Matching Values...

Apr 25, 2008

Hello, all.

I am new to SQL querying and I came across an issue while experimenting.

Say, I have two tables like the following, a Users table at the top, and a Roles table at the bottom (drawing the issue as I'm not fluent in English to explain):

(Users table)
UserId | RoleId | RoleOldId
-------+--------+----------
1......|x.......|11........
2......|y.......|22........
3......|z.......|22........

(Roles table)
RoleId | RoleOldId | RoleName
-------+-----------+-------------
10.....|11.........|Anonymous....
20.....|22.........|Superuser....
30.....|33.........|Administrator

The Roles table was changed to include a new column Roles.RoleId and the old column is named Roles.RoleOldId. Same is done for Users table.

Assume these are not foreign and/or primary keys. How can I fill Users.RoleId with new Roles.RoleIds, matching RoleOldIds at each tables? The resulting (x, y, z) set of Users table will be (10, 20, 20).

Thank you for your time.

View 3 Replies View Related

Transact SQL :: Populating A Datetime Column

Apr 29, 2015

I want to populate a datetime column on the fly within a stored procedure. Below is the query that I currently have that does same but slows down query performance.

CREATE TABLE #TaxVal
(
ID INT
, PaidDate DATETIME
, CustID INT
, CompID INT

[code]...

Which is the best way to write this query for better performance?

View 2 Replies View Related

OLE DB DataSource W Stored Procedure Not Populating Column Metadata

Mar 28, 2006

I'm having some issues getting OLE DB Data Sources to work w stored procs in SSIS. Here's the situation.

I have an OLE DB Data Source set up to call a stored proc w no parameters. The stored procedure loops through a set of databases and inserts data from each database into a results table. I'm attempting to return the results table to SSIS, but the Available External Columns are not populating. However, previewing the query in SSIS does show results. The insert in to the results table is done by a call to sp_executesql.

I've tried setting the results table up as a temp table, table variable, and static table. I have NOCOUNT set ON and am only returning one recordset. I've seen the other threads in here about similar problems, but none of their solutions seem to work for me.

Any help would be much appreciated....

View 2 Replies View Related

Populating A Table

May 19, 2004

I have a table with a list of products,once I enter the data into the table and start using it on my web site as a drop down list,the list is sorted as an alphabetical list,is there are way to have a single drop down list but still be able to group the those products,in order words force them not to get sorted aphabetically.


Thanks

View 11 Replies View Related

Populating A Table

May 11, 2007

Hi, I have two tables:

1. RubricReportDetail with columns LocalPerf, Age
2. SppIndicator with columns Pct, Age

How can I populate the values of LocalPerf with Pct by matching

RubricReportDetail.Age = SppIndicator.Age ??

Please help me. Thanks in advance.

View 15 Replies View Related

Populating A Table With A Dataset

Apr 17, 2007

I am running a program that populates tables on my local database by querying another database. 

View 2 Replies View Related

Error Populating Table

May 14, 2008

Hi all,

I have a table that looks something like this: -

county fname sport
------ ----- -----
surrey tara squash
surrey tara hockey
surrey tara tennis
kent tom tennis
kent tom football
kent tom rugby


I want to read through the sport table and create a distinct list of sports which can be used to create a new table that would look like: -

County fname squash hockey tennis football rugby
------ ----- ------ ------ ------ -------- -----
surrey tara YES YES YES
kent tom YES YES YES

I am using the following code: -
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'create table ey_report_temp (county nvarchar(100),fname nvarchar(100), ' +
STUFF((SELECT DISTINCT ',[' + sport + '] nvarchar(100) '
FROM ey_report FOR XML PATH('')), 1, 1, '') + ')'

SELECT @sql
exec sp_executesql @sql

SELECT * from ey_report_temp

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

DECLARE @county nvarchar(max)
DECLARE @fname nvarchar(max)
DECLARE @sport nvarchar(max)

DECLARE merge_cursor CURSOR FAST_FORWARD
FOR SELECT county, fname, sport from ey_report

OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
WHILE @@FETCH_STATUS = 0
BEGIN

select @sql = N'
update ey_report_temp
set ' + @sport + ' = ''YES''
where county = ''' + @county + '''
and fname = ''' + @fname + ''''
print @sql
exec sp_executesql @sql
if @@ROWCOUNT = 0

begin
select @sql = N'
insert into ey_report_temp (
county, fname, ' + @sport + '
) values ( ' + @county + ', ' + @fname + ', ' + @sport + ')'

exec sp_executesql @sql

end
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
select * from ey_report_temp

drop table ey_report_temp

This creates the new table fine however, when it trys to poulate I get the following EM, can anybody help? thanks in anticipation

(1 row(s) affected)

(0 row(s) affected)

update ey_report_temp
set squash = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set hockey = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set tennis = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set tennis = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set football = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set rugby = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

(0 row(s) affected)

View 7 Replies View Related

Populating A Lookup Table

Jul 9, 2006

I'm trying to do a table lookup in t-sql and runing into problems. I have two tables, City and County..



Table City:

CityID CountyID CountyName

1 3 NULL

2 2 NULL

3 1 NULL



Table County:

CountyID CountyName

1 Los Angeles

2 Contra Costs



I want to populate the NULL "CountyName" field in table City with the values from table County but I can't make it work! Any help appreciated.



Barkingdog

View 1 Replies View Related

Dataset Not Populating Table

Feb 7, 2008

Hello,

Just when I thought I was starting to understand SSRS just a little and then I encounter a strange (maybe not too strange) of an issue.

I have a DataSet which runs a stored procedure and it requires 10 parameters. When I run it in the dataset portion itself it runs fine however, it will not work when I choose to Preview it. I refreshed my dataset and even rebooted my machine. Can anybody shed some light on this?

Thanks

View 6 Replies View Related

Help On Populating Dimension Table

Dec 30, 2007



While populating dimsnion table the requirement is that the 1st record in all dimension should be " Not Available"

and if the fact table has a null value for that dimension it shouldbe tied to " Not Available" record in dimension
Please help hwo to do both the things.
JJ

View 5 Replies View Related

Populating A SQL Table With SmallDateTime Values

Mar 24, 2008

I have a table that I would like to update with the current dateTime of Today. The Table Field is called "DateTimeStamp" with a format of SmallDateTime -- I'm using VS 2008.
When I use the following command and then cmd.ExecuteNonQuery -- the table is correclty filled in with the exception of the DateTimeStamp field. Instead of 3/23/2008, I get the value 1/1/1900 ... 
SqlCommand cmd3 = new SqlCommand("UPDATE CameraPods SET CameraBlock1_ID = 98, CameraBlock2_ID = 99, Status = 1, DateTimeStamp = 3/3/2008");
any ideas?
    ---Jim

View 3 Replies View Related

Populating A Table With The Results Of A SQL Query

Jul 12, 2004

Is there any way i could take the first 50 results of a sql query and store them into 1 table in access and take the next 50 and store them into a second table in access? Is there any SQL statement that will direct where the output gets directed
to?

OR is there a way to have the sql reults paird up with a autonumbered ID?

Doc

View 2 Replies View Related

Populating Staging Table Using SSIS

Oct 6, 2006

Hi,

I have a flat file with columns from a geographical hierarchy such as:

Country Zone State County City Store Sub Store , etc.

The file also has data columns for months to the right of the above columns such as:

Jul Aug Sept ......... basically 25 of these columns for two years' data for one product and another set of 25 columns for another kind of product. A typical record in the file looks like:


Country Zone State County City Store Substore

USA Southeast FL Hillsborough Tampa walmart Fletcher



May04_ProdA ....Jun06_prodA May04_ProdB...Jun06ProdB
144 160 180 158

I need to upload this data into a staging table in SQL Server 2005 using SSIS, I created a table with the geographical hierarchy columns but am trying to figure out a way to load the monthly data. I can create 50 columns for the 50 months ( 25 months for each product) but that would be very crude.

Is there a better way of inserting data from this flat file into a destination table? I need all the data in the staging table in one upload.

Thanks.






View 11 Replies View Related

Populating FACT Table In ORACLE

Aug 1, 2006

I am having a torrid time trying to populate a FACT table in ORACLE.

I have so far tried two approaches -

1. Identified the change type for records - meaning I mark the record as inserts or updates based on the availability of the key in the FACT table. I used a conditional split and then used two OLE DB destination tasks to load the FACT table. But this failed as I was pretty much inserting on both the conditions (inserts and updates). So this attempt would count as a batch update process attempt.

2. Tried to also write a stored procedure in ORACLE destination database and then use that stored procedure to execute record by record using OLE DB Transformation task. But this did not fly as well due to the fact that SSIS could not understand the oracle SP and could not parse the information out.

Thank you

View 1 Replies View Related

XML Source Populating CR LF To SQL2005 Table

Oct 31, 2006

In my SSIS package I used the Web Service task to populate a SSIS variable with data from an XML feed. Then in the Data Flow I used a XML Source to import that data into a table on my SQL Server. When I looked at the data i noticed that the some of the fields contained CRLF pair. These CRLF pairs are being only populated by xml nodes that contain no data and are self-terminated. Below is a sample of the XML feed that is causing the problems. the DR_M_Initial and DR_Type1 nodes are populating CRLF's to the table.

Can anybody give me some insight on this problem. I am performing a workaround using the SQL REPLACE function in an Update statement to strip out ch(10) and ch(13), but this just seems a little cluegy to me.

Sample XML
---------------------------------------------------------
<?xml
version="1.0" encoding="utf-16" ?>

- <ROOT xmlns="">


<ROWRESULT>



<DR_NUMBER>1</DR_NUMBER>

<DR_L_NAME>SMITH</DR_L_NAME>

<DR_F_NAME>JOE</DR_F_NAME>

<DR_M_INITIAL />
<DR_TYPE1 />
</ROWRESULT>
</ROOT>

Thanks,
Tim Frakes

View 1 Replies View Related

Populating A Table In SQL Server 2005 Express From A Text File (*.txt)

Apr 10, 2008

I'm experienced using queries to extract data, but I'm new to actually creating tables, except through Access. I work with many records of data, so populating the data by hand is not an option. I created a test database (ValTest) and a test table within that database (ClaimTest). I created a text file (DataTest.txt) with the same layout as I defined for ClaimTest. I want to populate ClaimTest with the data from DataTest.txt. I was told the only way to do that using SQL Server Express was to use a utility called BCP. So I found a page on the Microsoft website talking about BCP. It gave some examples, so I opended up the Command Line window, pointed to the directory that contains the database and text file and typed "BCP ValTest.ClaimTest in DataTest.txt -T -c". I get 3 errors: Named Pipes Provider: Could not open a connection to SQL Server [2], Login timeout expired, and An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

I tried looking around and finding where I could change the settings, in case that was indeed the problem, but was unable to find anything. Does anyone have any suggestions?

Thanks

View 10 Replies View Related

Multiply Numbers In A Column

Mar 21, 2002

Hi

I'm trying to find a decent way of multiplying a set of numbers in a column without using a cursor in T-SQL.

There is no 'Product' aggregate function that I'm aware of in SQL 7 or 2000. The workaround I'm currently using is this :

SELECT EXP(SUM(LOG(ColumnName))) FROM tblName

This works fine, except when negative numbers are introduced. The LOG function does not allow negative numbers and therefore returns a domain error and the negative number is eliminated from the aggregate.

I could use a cursor to do the multiplication, however, this is proving too slow for the bulk calculations involved.

If anyone has any ideas or suggestions, then that would be much appreciated.

thanks....Tom

View 1 Replies View Related

Sequential Numbers In Column

Oct 25, 2013

I have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.

Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?

View 3 Replies View Related

STUCH WITH SSN NUMBERS COLUMN....HW TO DO

Apr 23, 2008



OK I HAVE AN INTERESTING SITUATION HERE.

I HAVE AN SSN COLUMN.

SSN NUMBER HAS 9 NUMERIC DIGITS.

NOW IN THE SSN COLUMN I HAVE A LOT OF BAD SSN WHICH CONTAIN ONLY 3 OR ONLY 4 OR ONLY 5 AND SO ON DIGITS.

HOW CAN I FILTER THESE ONES OUT SO THAT I CAN MAKE AN EXCELL REPORT OF THIS DATA.

I AM THINKGING OF USING CONDITIONAL SPLIT.

BUT HOW SHOULD I GO ABOUT IT.

PLEASE LET ME KNOW THE COMMAND

View 1 Replies View Related

Removing Numbers From A Column

Oct 11, 2007



Hi,

I have the following tables :




Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))

Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01 Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);

The final data looks like this :

Cary zzz NULL
01 Jack NULL
02 Tommy NULL
03 Ricardo NULL
04 Jack NULL
Les zzz NULL
05 Tim NULL






1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column




Code Block
Expected Output :

Jack NULL
Tommy NULL
Ricardo NULL
Jack NULL
Tim NULL






Can anyone help me please with the query?

thanks.

View 4 Replies View Related

Fact Table With 3 Keys From Dimension Tables - Avoid Populating NULLs

Jun 10, 2014

I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..

View 3 Replies View Related

Data Warehousing :: Populating Fact Tables With Surrogate Key From Dimension Table?

Sep 11, 2015

How do I correctly populate a fact table with the surrogate key from the dimension table?

View 4 Replies View Related

Convert Sql Smalldatetime Column Into Numbers

Aug 27, 2004

HI all;

I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.

Any help would be highly appreciated, thanks all.

View 1 Replies View Related







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