insert rows based on two keys if not exist? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2012 Forums

Transact-SQL (2012)

insert rows based on two keys if not exist?

Author

Topic

jimib
Starting Member

3 Posts

Posted - 2014-07-04 : 11:35:16

I have a table:

id text
1 x
1 y
2 x
3 x
3 y

I need sql that will insert all the text values with id 1
into every other id if they are not already there, so the result would be

id text
1 x
1 y
2 x
2 y
3 x
3 y

any help is appreciated

jimib
Starting Member

3 Posts

Posted - 2014-07-04 : 14:10:52

Closest I can get is :
INSERT INTO aaTable1
(id, text)
SELECT 2 AS Expr1, text
FROM aaTable1 AS aaTable1_2
WHERE (id = 1) AND (text NOT IN
(SELECT text
FROM aaTable1 AS aaTable1_1
WHERE (id = 2)))
which works because 2 is the id of the missing text. I suppose I could do a loop ...

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-05 : 00:45:32

[code]

DECLARE @aTable AS TABLE
(
id INT
,[text] VARCHAR(30)
)

INSERT INTO @aTable(id,text)
SELECT 1 AS [id],'x' AS [text] UNION ALL
SELECT 1,'y' UNION ALL
SELECT 2, 'x' UNION ALL
SELECT 3, 'x' UNION ALL
SELECT 3, 'y'

INSERT INTO @aTable(id,text)
SELECT
V.number AS [ID]
,T.[text] AS [text]
FROM
(SELECT DISTINCT
[text]
FROM
@aTable) AS T
INNER JOIN master.dbo.spt_values V
ON V.type ='p'
AND V.number between 1 and 3
FULL JOIN @aTable AS ExT --ExistentTable
ON T.text=ExT.text
AND V.number=ExT.ID
WHERE
ExT.id IS NULL
ORDER BY
V.number ,T.[text]

SELECT * FROM @aTable ORDER BY ID,text

[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources