Identity column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Identity column

Author

Topic

MariusC
Starting Member

16 Posts

Posted - 2014-06-26 : 06:01:26

Hi guys,

I want in the identity column to repeat the same value 3 times. I didn`t figure out so far for an option.
I`ll give an example to make it more clear:

nr_rec| id_param |
+-----+----------+
1 | no_tel
1 | sex
1 | age
2 | no_tel
2 | sex
2 | age
3 | no_tel
3 | sex
3 | age

the nr_rec will go so on.

Need help!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-06-26 : 06:03:13

if you want to explicitly insert values into identity column you need to SET IDENTITY_INSERT OFF and then insert.

Javeed Ahmed

MariusC
Starting Member

16 Posts

Posted - 2014-06-26 : 06:28:34

No..I want, if it is possible, to insert automatically just like in example. Or maybe it is another method that can help me.
I have a large number of data that repeats over and over and I want for each set of data (no_tel,sex,age) to associate a nr_rec.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 06:36:47

[code]
;with cte
AS
( SELECT 1 as nr_rec,'no_tel' as id_param UNION ALL
SELECT 1 , 'sex' UNION ALL
SELECT 1 , 'age' UNION ALL
SELECT 2 , 'no_tel' UNION ALL
SELECT 2 , 'sex' UNION ALL
SELECT 2 , 'age' UNION ALL
SELECT 3 , 'no_tel' UNION ALL
SELECT 3 , 'sex' UNION ALL
SELECT 3 , 'sex' UNION ALL
SELECT 3 , 'age')

SELECT
nr_rec
FROM
(
SELECT
nr_rec
,SUM(CASE WHEN id_param ='no_tel' THEN 1 ELSE 0 END) as isTel
,SUM(CASE WHEN id_param ='sex' THEN 1 ELSE 0 END) as isSex
,SUM(CASE WHEN id_param ='age' THEN 1 ELSE 0 END) as isAge
FROM
cte
GROUP BY nr_rec)A
WHERE
A.isTel >=1
AND A.isSex >=1
AND A.isAge>=1
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 06:37:17

and the output:


1
2
3

sabinWeb MCP

MariusC
Starting Member

16 Posts

Posted - 2014-06-26 : 06:52:23

@stepson You didn`t understood what I want.
I want to insert the numbers one after other.As in example..I want next to be 4 4 4 and then 5 5 5 and so on.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 06:59:24

so on until ?


;with cteValue
AS
	(
		SELECT 'no_tel' id_param UNION ALL
		SELECT 'sex' UNION ALL
		 SELECT 'age')
	


SELECT
	number
	,id_param
FROM
	cteValue
	CROSS JOIN
	MASTER..spt_values
WHERE TYPE= 'P'
	AND number>3

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 06:59:57

output:


number	id_param
4	no_tel
4	sex
4	age
5	no_tel
5	sex
5	age
6	no_tel
6	sex
6	age

sabinWeb MCP

MariusC
Starting Member

16 Posts

Posted - 2014-06-26 : 07:07:04

In this case I want until 4500.
It is almost what I want, but can the output be in the order that is in my example?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 07:09:32

order : no_tel , sex , age ?

sabinWeb MCP

MariusC
Starting Member

16 Posts

Posted - 2014-06-26 : 07:11:13

Yes..when I run your code it goes like no_tel for 2047 times then sex for 2047 and age for 2047 times.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 07:12:11

[code]
;with cteValue
AS
(
SELECT 'no_tel' id_param ,1 as ord UNION ALL
SELECT 'sex' , 2 UNION ALL
SELECT 'age' ,3 )

SELECT
number
,id_param
FROM
cteValue
CROSS JOIN
MASTER..spt_values
WHERE TYPE= 'P'
AND number>3
ORDER BY number,ord
[/code]

sabinWeb MCP

MariusC
Starting Member

16 Posts

Posted - 2014-06-26 : 07:13:09

This is the result I wanted.

Thank you very much!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 07:14:17

Cu placere!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources