can somebody tell me what the code would be to remove all the values
in a given column and replace them with the associated number of the row
with each execution.
so, if i have a column:
nums
|1|
|2|
|3|
|4|
and somebody deletes record |2|
i would like the nums colum to update to
|1|
|2|
|3|
not:
|1|
|3|
|4|
it seems simple but i am having a hard time with this.
how is it done?
I have a task where I need to process roughly 60000 excel spreadsheets and bring them into a SQL Server 2014 database. All excel files have the same format and same number of identical columns. I have set up an SSIS package that is using Foreach Loop Container to look into a folder and process these files one at a time and load them to a table. The mappings are straight-forward, no problems there.
I am attaching a sample spreadsheet with two tabs - current structure and desired structure.
Basically what I need to do is to repeat the first 7 columns based on the number of lines in a transaction.
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow. I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups
How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Just to confirm, do identity columns and XML columns work OK with database mirroring ? That is, all data types are supported with mirroring, and identities aren't an issue ?
Transactional replication with identity columns was a right pain in the **** in SQL 2000. I'm assuming that mirroring doesn't have these issues, but want to be sure.
All of my tables in my database have keys that are autonumbered (datatype int with identity set to 1). Whenever I go to insert a new entry into the table I execute an INSERT INTO command and leave off the key field, so that it is automatically inserted with the new row. However, I need to that number, so that I may insert it as foreign key into another table. How would I go about retrieving this number? I thought about doing a Max() on that field, but I am not confident that SQL server would always use a higher number than everything previous. Is there a better way of accomplishing this? Is my design flawed from the start? Any feedback would helpful. Thank you.
create table RECORD ( ID int not null, Issue_descr varchar(256) not null, Priority varchar(5) not null, Status varchar(12) not null, Date_add varchar(10) not null, Date_due varchar(10) not null, Date_complete varchar(10), PName varchar(32), primary key(ID), foreign key(PName) references PROJECT(PName) );
and
Code:
create table STEPS ( ID int not null, Num int not null, Descr varchar(256), Date_due varchar(10) not null, Date_complete varchar(10), Status varchar(12), primary key(ID, Num), foreign key(ID) references RECORD(ID) );
I have set PK "ID" in table RECORD to auto identity(1,1). I have done the same for PK "num" in table STEPS. However I am seeking this behavior in STEPS: ID num -- ---- 19 1 19 2 19 3 20 1 20 2 21 1
but what I'm getting is PK num doesn't "reseed" or reset to 1 as "ID" changes. PK num just auto-increments regardless of ID. Is there a workaround?
How do I output a row number for a table solely for the purpose ofquerying for a unique row?In my problem, the table from a legacy system does not have a primarykey, so it limits various querying I'd like to do that identifiesuniqueness in the table.The problem is that since I'm using DTS to simply copy the table toSQL, I don't want to create identity rows.
I am migrating a web application I wrote from ASP to ASP.Net, and fromAccess to MS SQL server.In the Access version, I did not use the auto number for creatinginvoices and other documents, because I heard somewhere (perhapsincorrectly) that if the db was ever compacted or otherwise changed,it could change the values of the auto-numbers. Not a good thing.So I wrote a routine that, just before creating a new record, wouldlook for the highest value in the table and create the new record withthe next number.So my question is, am I safe in assuming that in MS SQL that I can seta starting number for the next, let's say, invoice and that newnumbers will be issued in sequence, and that these numbers will neverchange? What happens if an invoice is deleted? is the number goneforever? Just wondering how others deal with these issues...thanks.Larry- - - - - - - - - - - - - - - - - -"Forget it, Jake. It's Chinatown."
I am attempting to import data from a Lotus Notes database using DTS. The SQL table I am importing to has an identifying auto-number. I can't insert directly into it because the SQL server should, however I get an error if I ignore it in the DTS package. Is there any way to get around this?
Can anyone describe how SQLServer calculates identity columns? Does it use some internal counter when generating the next identity, or something a little more mundane such as gets the highest existing identity value at the point of the insertion and increments it by the IDENT_INCR value of the identity column?I’m not worrying about reliability or gaps in values, but i am wondering if it would be less efficient for me to manually manage the identity/primary key in the form of a counter in another table used to generate the new identity, or simply let the DB do it for me. I dont mind if there are gaps in the sequence etc. so would it be less efficient for me to calculate the field than SQLServer itself? Basically, is the overhead to the DB of me doing it greater than the overhead of the app doing it...Thanks
I have a question about IDENTITY columns. I am working for a client that has an entire employee database that uses IDENTITY columns without any Primary keys defined. I have never seen this done. Is it ok or should I recommend that it be changed to use Primary keys?
The DBA that built the database is no longer with the company and the client has no DBA. Where can I get some information on how to use IDENTITY columns? I did not get much from the help file.
I have been using the following query to identify the IDENTITY columnsin a given table. (The query is inside an application.)select column_namefrom information_schema.columnswhere table_schema = 'user_a' andtable_name = 'tab_a' andcolumnproperty(object_id(table_name), column_name, 'IsIdentity') = 1This works. When "user_a" performs the query, everything is OK.Now, another user wanted to use the same application. So, "user_b"clicks on a button, and the exact same query as above is run. (Nosubstitutions are made; user_b is trying to see the identity column in[user_a].[tab_a]). However, the query returns null, instead of theidentity column name. User_b can read the table and select from itjust fine.Why am I getting two different results against the same query? Do Ineed to rewrite the query to go against different information schemaviews?
I'm seeing some weird behavior regarding identity columns in MSSQL 2000.In a specific client database we have this table: ID Name SecLevDG Flags ----------- ----------------- ----------- ----------- 1029528 xxx 0 01029529 xxx 0 01049676 xxx 0 0 While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are
alter table Authority drop constraint apkAuthorityId alter table Authority drop column SecLevDg alter table Authority add new_id integer identity
This code has worked perfectly for years, and even in this particular database, there are no error messages. However, the result isn't quite the expected:
ID Name Flags new_id ----------- ---------------- ----------- ----------- 1029528 xxx 0 167772201029529 xxx 0 167772201049676 xxx 0 16777220
Notice that the new column did NOT get unique values 1, 2, 3, etc... In other tests I manage to get different values, but still not the expected ones. Is this a bug in MSSQL 2000?
DBCC CHECKIDENT returns: Checking identity information: current identity value '1', current column value '1'.
DBCC CHECKDB returns no errors before running the above statement. Afterwards it returns this (only relevant messages included): Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row. Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 1. Column 'new_id' was created NOT NULL, but is NULL in the row. Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:1145), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row. [...] CHECKDB found 0 allocation errors and 3 consistency errors in table 'Authority' (object ID 293576084).
I have an app that has multiple users inserting and updating from a couple key tables using SQL Server 2005. In my previous SQL coding life, I was able to make use of sequence.NextVal to find and lock the next available sequence value for a table. Currently I am in the SQL Server 2005 world and cannot do this. I have found all sorts of help for Ident_currentand Identity columns, but nothing on how to accomplish the same as a NextVal did. I can add one to the Ident_Curr but I think I run the risk of that value being used by another user before the current user can get his update in. Is that correct? Is there a way to accomplish what I am trying to do? Basically what I need to do is when a user inserts a record in table "Loads" I need to insert behind the scenes to table "Comments" with the ID of the row created in the "Loads" table. Thank you in advance, Garth
I am using sql 2000 dts package to migrate a databse to SQL Server 2005, everything works except for identity columns, SQL Server reorder the columns, and this breaks the referential integrity. Is there a way to stop that? Your help is appreciated.
Table structure: col1 IDENTITY (seed=1 increment=1) + few other columns (col2...col7) + one text column (col 8) I have around 50,000,000 rows per day inserted in the table T1. At the end of the day 40,000,000 rows are deleted. I have to keep the records for 12 months and then archive it. Database is 24/7 web serving and there is no down time allowed. IDENTITY column will go out of range (overflow) after less than two years, unless the identity seed is reset to the start value (seed=1, increment=1). At the end of 12th month data is archived in another table and only last month is kept in the table T1. So table T1 enters new year with data from last month of the previous year. There are few other tables that refer to this table by using there own field with values from T1.IDENTITY column (referential integrity is not enforced). Identity column in T1 is needed as a unique id for some search actions. Performance is an issue therefore bigint data type is used for this identity column rather than decimal.
Another problem I have is how to do table update on one column (1 mil rows to be updated out of 2 mil of rows) with the minimum impact on the users who are querying this table heavily. Not need to mention that it is web app 24/7 no down time.
Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to show the user's pictures in perfect order (1,2,3,4,5,6...).
The Jist of my problem... When a user first inserts six pictures, he gets:
|1| |2| |3| |4| |5| |6|
All is good. But, say he deletes picture |3|. Now the list order looks like this:
|1| |2| <- |3| is removed |4| |5| |6|
And, then he inserts two more pictures, now he his this:
|1| |2|
|4| |5| |6| |7| <- |7| & |8| are added |8|
What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.
I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:
BEGIN SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109)) END
BEGIN SET @photo_number = 1 SELECT
@photo_number = ( SELECT COUNT(*) FROM dbo.PersonalPhotos b WHERE a.photo_date < b.photo_date ) FROM dbo.PersonalPhotos a ORDER BY a.photo_date END
BEGIN
My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:
Need help on the Auto Number or Identity Seed on the Oracle DatabaseI got an Access database that need to be converted to Oracle 9i.Somehow the Trigger we created to simulate the "AUTO NUMBER" on Accesscould not create the sequence number as soon as the value has beeninserted. The sequence number can only be created after we go to thesecond line. Please see the trigger below.Is there anyway we could create a trigger that could create thesequence number as soon as we enter a value? It should be verysimilar to the "Auto Number" on Access, or "Identity Seed" on SQLServer.----------------------------------------------------------1. sequence SNP.SECTION_ID_SQ:CREATE SEQUENCE SNP.SECTION_ID_SQSTART WITH 1INCREMENT BY 1NOMINVALUENOMAXVALUENOCYCLECACHE 20NOORDER/GRANT SELECT ON SNP.SECTION_ID_SQ TO "PUBLIC"/2. Trigger SNP.SNP001_T_I_GET_NEXT_SECTION_ID:CREATE OR REPLACE TRIGGER SNP.SNP001_T_I_GET_NEXT_SECTION_IDBEFORE INSERTON SNP.SNP001_SECTIONREFERENCING OLD AS OLD NEW AS NEWFOR EACH ROW WHEN (new.section_id IS NULL)BEGINSELECT section_id_sq.nextvalINTO :new.section_idFROM dual;END;
I use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.
The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.
It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.
I have a production database with about half the tables using IDENTITY columns for PRIMARY KEYS. This system is configured as both a Publisher and a Distributor. We are using Transactional Replication without updates. The SQL Server Agent runs every hour to pick up any changes and replicate them to the Subscriber (another SQL Server machine configured as our failover server).
Both servers are running SQL Server 7.0 (original, no service packs) under Windows NT 4.0 (SP4).
The failover server (the replication Subscriber) will only be used if the primary server goes down. And hopefully, only for a short time before the primary server comes back online. During the time that the failover is actually being used, the application will not make any changes to the database.
The IDENTITY values that are replicated need to stay with their original values. The replication process CANNOT assign new values to these columns when there are inserted into the database on the Publisher (i.e. failover server)
My question is: According to the documention, I can add 'NOT FOR REPLICATION' to the IDENTITY columns and the values will be preserved. But a collegue of mine says that resets the IDENTITY sequence on the subscriber and the 2nd time a row gets inserted on the Publisher, the values get messed up. On his system, he calls a stored procedure for the tables with IDENTITY columns, and in the stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this approach solved his issues with IDENTITY columns.
Who is right? Do I have to create a stored procedure for replication for every table with an IDENTITY column, or can I just add 'NOT FOR REPLICATION' and SQL Server will handle the rest?
NOTE: Upgrading to SQL Server 2000 is NOT an option right now. Although, if a Service Pack for 7 fixes this, that might be an option.
Thanks in advance for any help you can shed on this issue.
We are trying to run replication from one server to another. Most of the tables in the publishing database have identity columns set. At one point the subscribing database had a match of identity columns and primary keys matching the publishing database. Obviously with a primary key set on the subscriber there would be conflicts with duplicate key inserts. We disabled the identity columms and droped the indexes and the data was able to replicate over. However, we discovered that the identity columns, which are used to generate id's on many of our tables, were not replicating over to the subscriber. In fact a null value was being inserted into the subsriber database.
Anyone seen this before? What, if there is one, is the solution?
hi there. I was wondering is there any way that you can use an Identity Column on both a subcribing and Publishing table in Replication, I am receiving errors when I run Replication with Identity Columns, Thanks in advance Fin
Hi folks! I've a merge replication setup b/w two servers. Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]). Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message: "Checking identity information: current identity value '1', current column value '999'." How do i square this away?
Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.
For exameple: Let's say I have a client table with a ClientID column as it's PRIMARY KEY. This column can be an auto-incrementing IDENTITY column.
Then I have an orders table. The PRIMARY KEY for the orders table is composed of (ClientID, OrderID). I would like the OrderID to be an IDENTITY field that increments by an arbitrary value (1 in this case) for every new value of ClientID...therefore creating a unique PRIMARY KEY.
MySQL (and maybe other RDMS's...I haven't checked) seems to do this automatically when you set a column as AUTOINCREMENT and then define a composite PRIMARY KEY on two fields. I know this can be done manually using triggers, but I was wondering if there was a better way...
Hi, I noticed that tables containing IDENTITY columns skip IDENTITY values when a transaction is rolled back. Is there any way to avoid this? By "avoid" I mean make the table continue generating IDENTITY values from where the last COMMITTED insert took place.
Example:
CREATE TABLE ATable (A INT IDENTITY, B INT);
INSERT INTO ATable (B) VALUES (39); INSERT INTO ATable (B) VALUES (51); INSERT INTO ATable (B) VALUES (62); INSERT INTO ATable (B) VALUES (93); INSERT INTO ATable (B) VALUES (10);
Is there a way to have SQL-Server continue from where it left off (IDENTITY VALUES 6, 7, 8) without having to generate the numbers manually or occasionnally turning on IDENTITY_INSERT on the relevant table(s)?
