|
Hi,
I am working on SQL Server 2005 tables. Here I need to add a column named ‘ID’ as ‘IDENTITY’ column (With starting and incrementing values as 1, 1).
Now my problem is that these tables already have thousands of records. So could you please suggest the best and easy way to perform this job?
Many Thanks,
Regards.
Anusha.
|
|
|
|
|
Just add the field, SQL server will insert the ID values for existing records (It does in 2008 and I think it did in 2005). If not add the field, use row_number() to update the value and then add the identity constraint.
|
|
|
|
|
Suppose I have a table
declare @tbl1 table(name varchar(20), address varchar(50))
insert into @tbl1
select 'John','302 ABC Street' union all select 'Smith','412 DEF Street'
select * from @tbl1
The output is
name address
John 302 ABC Street
Smith 412 DEF Street
Now I want to insert identity
Change the table definition by adding an identity field like this
declare @tbl1 table(id int identity,name varchar(20), address varchar(50))
The output is
id name address
1 John 302 ABC Street
2 Smith 412 DEF Street
Else go ahead with the Row_Number() function(since sql server 2005 onwards)
select ROW_NUMBER() over(order by name) id, * from @tbl1
Niladri Biswas
|
|
|
|
|
Just add the IDENTITY column, SQL Server will fill the values automatically.
|
|
|
|
|
I have 2 tables, Products and Receipts. In need to select all rows in the Products table and link it to the Receipts tables where the ReceiptId equals a given value.
Products Table
---------------
ProductId BIGINT
Receipts Table
--------------
ReceiptId BIGINT
ProductId BIGINT
Quantity DECIMAL(15,4)
If I execute a SELECT query on the products table I get 126 rows returned
If I execute a SELECT query on the receipts table WHERE ReceiptId = 330804 , I get 2 rows.
I created a new view as follows
SELECT TOP (100) PERCENT dbo.Products.ProductId, dbo.Receipts.ReceiptId, dbo.Receipts.Quantity
FROM dbo.Products LEFT OUTER JOIN
dbo.Receipts ON dbo.Products.ProductId = dbo.Receipts.ProductId
Then if I perform a SELECT query on the new view as follows :-
SELECT * FROM vuProductReceipts WHERE ReceiptId = 330804 OR ReceiptId IS NULL
I would expect to get 126 rows returned, but I am only getting 63.
Any help would be appreciated.
Thanks
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Steven J Jowett wrote: If I execute a SELECT query on the receipts table WHERE ReceiptId = 330804 , I get 2 rows.
Steven J Jowett wrote: SELECT * FROM vuProductReceipts WHERE ReceiptId = 330804 OR ReceiptId IS NULL
Steven J Jowett wrote: I would expect to get 126 rows returned, but I am only getting 63.
Based on the above three asserions, im guessing there are 61 products without any receipts.
What's your question?
|
|
|
|
|
Hi,
But I have used your code only and is getting the correct result.
declare @tblProduct table(Productid int )
insert into @tblProduct select 1 union all select 2 union all select 3 union all
select 4 union all select 5
declare @tblReceipts table(Receiptid int,Productid int,Quantity decimal )
insert into @tblReceipts
select 1,1,20 union all
select 1,1,30 union all
select 2,1,40 union all
select 2,1,12 union all
select 3,1,45 union all
select 2,2,45 union all
select 1,1,20 union all
select 1,1,30 union all
select 2,1,40 union all
select 2,1,12 union all
select 2,1,45 union all
select 2,2,45
select ProductId,ReceiptId,Quantity from
(SELECT TOP (100) PERCENT p.ProductId, r.ReceiptId, r.Quantity
FROM @tblProduct p LEFT JOIN
@tblReceipts r
ON p.ProductId = r.ProductId ) X(ProductId,ReceiptId,Quantity)
where X.ReceiptId = 2 OR X.ReceiptId is NULL
The output is as desired:
ProductId ReceiptId Quantity
1 2 40
1 2 12
1 2 40
1 2 12
1 2 45
2 2 45
2 2 45
3 NULL NULL
4 NULL NULL
5 NULL NULL
So what is ur doubt? Can u give us some datapoints so that we can give a better insight!
Niladri Biswas
|
|
|
|
|
You can't really figure out what's going on here without knowing more about the data that is actually in the tables.
There are two rows in the Receipts table with ReceiptId 330804. Do these have the same ProductId or different ProductIds?
My guess is that there are two products for receipt id 330804 and 61 products with no receipt id. Total, 63 rows in your result set.
|
|
|
|
|
I have a table called products with a unique id column called ProductId.
The products table contains 124 rows.
I have another table called receipts containing
ReceiptId
ProductId
Quantity
The ProductId would contain a ProductId from the Products table.
So what I want is a list of 124 products and the quantities received next to each product with a particular ReceiptId
Basically
ProductId Quantity
1.........10
2.........NULL
3.........NULL
4.........5
5.........NULL
NULL being where there is no receipt
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
|
Dave
Thanks for the help.
My solution is as follows :-
SELECT NULL as TicketId, *, NULL as Quantity
FROM vuDepotProducts
WHERE NOT EXISTS(
SELECT * FROM vuReceipts WHERE DepotId = vuDepotProducts.DepotId AND ProductId = vuDepotProducts.ProductId AND TicketId = 330804
)
UNION
SELECT * FROM vuReceipts WHERE vuReceipts.TicketId = 330804
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Hi
I am trying to import a CSVs file using OPENROWSET and import this into a database
my problem is the CSV files may contain a varying number of columns
i have tried using
SELECT BulkColumn
FROM OPENROWSET (BULK '\\10.1.2.107\rp_uploaded_files\file.csv', SINGLE_CLOB) MyFile
but this puts all the values into one fields
i have also tried
set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
exec (@string)
but this requires the destintation table to have the same number of columns as the import file
Can anyone advise
thanks
Simon
|
|
|
|
|
|
HI Guys,
I have problem reading images from ms access database!
there is a clinic software who was written by a bankrupted company,
and now it doesn’t work by the some reason, and there is no support on this software,
All things that I should do are recover the patient’s data that was written by doctor handwritings in his notebook tablet-pc.
I think the doctor's handwriting must be saving as images in the patient’s records. The problem is I couldn’t read it from database because the data are like this:
AIfIAR0E2A/uBgIZAkgRBUgRRP8BBEgQRTUKSBFXDQAAAFD/AQQDATgAGCIgMgkA9P8CAekiOkUzCQD4nwIBWiA6RTgIAP4DAAAAgH8ADw4S+SONP88jjT8RAACAPx4+4gKD/glK/glJAAAAAAAAAAAYyAAAAAAAAAAAAAAAAAiQIkAAAAAAVMgAAAAESAEJAAAAAAxkAAAAAAIfEAAKLw+E+44+492VloilKFtOjSCG4PDhA6KAjpKCkI
I think images must be converted from binary to another type.
If anyone could help on this he/her save my life!
i attached the database file here/:
http://rapidshare.com/files/302706380/test.accdb.html
there is a tables in the database, and all thing that i need is the "VISIT Table" 's information on the column by the name "NOTES", i think it must be image.
HELP ME,THis IS UrGENT Problem!
|
|
|
|
|
Hi,
I am trying to install sql server 2008 from the last three days..but there was no progress.Earlier,I have SqlServer 08,but that was crashed,So I am trying to install sql server 08 from the begining.
So,
1)I have uninstalled the existed Sql server 08
2)Removed all the features from control panel->Programs and features and no sql services existed in the Services
3)Completely removed the folder"Microsoft SQL Server" from C:\Program Files.
4)Removed sql related registry keys from HKEY_LOCAL_MACHINE\Software\Mocrosoft\
When I am Installing ,I am able to install till support Files only.Supporting files are successfully getting installed .After support files,It is not showing any further windows (like Feature Selection Window etc),and again it is showing the starting window .So,I couldnot move forward.Please help in this to go ahead.
Thanks,
Krishna
|
|
|
|
|
Hi,
I my stored procedure I have an insert query. For this I have to get one value from a sub query. But it is not accepting that query. So I tried to declare a separate variable and assign the subquery value to this and insert it in the table.
Create Procedure [dbo].[AddOrRemoveFavourites]
(
@UserId bigint,
@VideoName varchar(50),
)
as
Declare @VideoId int
Set @VideoId = Select VideoId from Videos where Title='Monkeydog'
Insert into Favourites(UserId,VideoId) values(@UserId,@VideoId)
but in this I am getting error like "Incorrect syntax near the keyword 'Select'."
If anyone have any idea to solve this please reply me.
Thanks in advance.
|
|
|
|
|
Try this one;
SELECT @VideoId = VideoId
FROM Videos
WHERE Title = 'Monkeydog'
I are Troll 
|
|
|
|
|
Hi,
You need to enclose your select in parenthesis and include a top(1) clause, like this:
Set @VideoId = ( Select top(1) VideoId from Videos where Title='Monkeydog' )
Regards,
Syed Mehroz Alam
My Blog | My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Hi,
Set @VideoId = Select VideoId from Videos where Title='Monkeydog' is wrong
use
Select @VideoId = VideoId from Videos where Title='Monkeydog'
The reason is if we want to assign values to more than one variable at a time then go with Select.
SET allows you to assign data to only one variable at a time.
For more information visit here http://vyaskn.tripod.com/differences_between_set_and_select.htm[^]
Also searching in google will reveal more info
Niladri Biswas
|
|
|
|
|
Hi...
I want to know the diffrence between SQL Express and SQL mangment studio.
On my PC which is standalone ,SQL server 2008 Insrtalled but I unable to see any database
or anything.I can see only Configration manager.
So On my pc SQL express installed or Only Managment studio installed ?
Which application I have to install so I can create the database ?
|
|
|
|
|
One is a database edition
The other is a management toll for the database (create and test database objects, tables, views, procedures etc)
|
|
|
|
|
|
SQL Express[^] is the "express" version of SQL Server, meaning that it's a trimmed down version of SQL Server and available as a free download.
SQL Management Studio[^] is an IDE to manage databases.
In other words, the first thing is a database-server, the second thing is a GUI to manage that server.
I are Troll 
|
|
|
|
|
sql query aur ms access main
when i pass "select maid) from enquiry where regdate=#05/11/2009#" from sql
access treate it as 11/05/2009
why???
and wats the solution
|
|
|
|
|
Try this
select maid) from enquiry where regdate=#05/Nov/2009#
or this
select maid) from enquiry where regdate=#2009/11/05#
Read up on datetime
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
