Is there a way to associate IDENTITY columns from two different tables, so that new records created in either table will have mutually unique values?
That is, a new record in table A will be given value 1, and then a new record created in table B will be given value 2 (because value 1 was already used by the IDENTITY column in table A). Can this be done?
No, identity fields are only unique within the table.I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.
Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?
No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.
Code Snippet
declare @.a table (x int, y varchar(200))
declare @.b table (x int, i int, j char(2), k bit)
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.a select 3, NULL
insert @.a select 4, NULL
insert @.a select 5, NULL
insert @.b select 1, 55, 'AB', 0
insert @.b select 2, 66, 'CD', 1
insert @.b select 3, 77, 'EF', 1
insert @.b select 4, 88, 'GH', 0
insert @.b select 5, 99, 'IJ', 1
update a
set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))
from @.a a join @.b b
on a.x = b.x
select * from @.a
|||
Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.
Code Snippet
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'
--The Following select will fail to return
--concatonated values in a.y because the
--UPDATE can't update the same row value
--twice in the same UPDATE statement.
update a
set a.y = ISNULL(a.y,'') + b.j
from @.a a join @.b b
on a.x = b.x
--column y only holds the first value for
--group 1 and group 2
select * from @.a
|||
I am sure, there will be better ways than this code, you can do something like this.
Code Snippet
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
declare @.temp table (x int)
declare @.x int
declare @.y varchar(200)
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'
insert @.temp
select distinct x from @.a
while exists (select 1 from @.temp)
begin
select top 1 @.x = x from @.temp
select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x
select @.y as 'the value'
update a
set y = ISNULL(y, '') + @.y
from @.a a
where a.x = @.x
delete from @.temp where x = @.x
set @.y = ''
end
select * from @.a
|||
Here is an old post that should help. Basically, you create a udf to concatenate the values.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90
|||Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.
set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'
update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)
select *
from @.a
Returns:
x y
-- -
1 AB,CD,EF
2 ZY,RX
|||First, thanks to Sankar and oj for your assistance.
Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.
Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.
I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.
Thanks again for the help.
Hugh
|||oj,
On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.
Thanks.
I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.
Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?
No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.
Code Snippet
declare @.a table (x int, y varchar(200))
declare @.b table (x int, i int, j char(2), k bit)
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.a select 3, NULL
insert @.a select 4, NULL
insert @.a select 5, NULL
insert @.b select 1, 55, 'AB', 0
insert @.b select 2, 66, 'CD', 1
insert @.b select 3, 77, 'EF', 1
insert @.b select 4, 88, 'GH', 0
insert @.b select 5, 99, 'IJ', 1
update a
set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))
from @.a a join @.b b
on a.x = b.x
select * from @.a
|||
Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.
Code Snippet
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'
--The Following select will fail to return
--concatonated values in a.y because the
--UPDATE can't update the same row value
--twice in the same UPDATE statement.
update a
set a.y = ISNULL(a.y,'') + b.j
from @.a a join @.b b
on a.x = b.x
--column y only holds the first value for
--group 1 and group 2
select * from @.a
|||
I am sure, there will be better ways than this code, you can do something like this.
Code Snippet
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
declare @.temp table (x int)
declare @.x int
declare @.y varchar(200)
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'
insert @.temp
select distinct x from @.a
while exists (select 1 from @.temp)
begin
select top 1 @.x = x from @.temp
select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x
select @.y as 'the value'
update a
set y = ISNULL(y, '') + @.y
from @.a a
where a.x = @.x
delete from @.temp where x = @.x
set @.y = ''
end
select * from @.a
|||
Here is an old post that should help. Basically, you create a udf to concatenate the values.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90
|||Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.
set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'
update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)
select *
from @.a
Returns:
x y
-- -
1 AB,CD,EF
2 ZY,RX
|||First, thanks to Sankar and oj for your assistance.
Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.
Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.
I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.
Thanks again for the help.
Hugh
|||oj,
On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.
Thanks.
Hi,
I am using the EDB as a database in smart phone applications.I can able to mount the database , create the tables , write the records into the tables and read from the table with single sort order specification.But if i am using more than one sort order specification seek database is throwing an error message "The drive cannot locate a specific area or track on the disk".
I created one table named as Icon with more than one sort order specification.In this table i am giving pageid and iconid as sort order specifications. While saving the data into the database i am opening the database with pageid sort order and storing into the table.while reading from the database i am opening the database with iconid as sortorder.So here while seeking the database the above error is displaying.And i tried to read the data from the table by using pagid as sortorder speicification.It is working fine.I am applying the 3 sort order specifications for another table named as contentTable. but it is working fine with 3 sort order specifications.I had done the samething for icon table .But no use.Please help me regarding this.
Thanks in Advance,
Thanks & Regards,
Prasanna Kumar
Moving to Sql Server compact edition forum where it has got better chances of being answered.
-Thanks,
Mohit
