March 20, 2009 at 2:58 am
Hi guys
CREATE TABLE [dbo].[b1](
[b1] [int] NOT NULL,
[b12] [varchar](10) NOT NULL
)
GO
CREATE TABLE [dbo].[b2](
[b2] [int] NOT NULL,
[b22] [varchar](50) NOT NULL,
[b1ref] [int] NOT NULL
)
GO
truncate table [bob].[dbo].[b1]
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (1,'bob11')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (2,'bob12')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (3,'bob13')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (4,'bob14')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (5,'bob15')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (6,'bob16')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (7,'bob17')
GO
truncate table [bob].[dbo].[b2]
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (1,'bob21', 1)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (2,'bob22', 2)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (3,'bob23', 3)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (4,'bob24', 4)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (5,'bob25', 5)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (9,'bob29', 9)
GO
select bob.dbo.b2.b1ref, bob.dbo.b2.b22 from bob.dbo.b2 where bob.dbo.b2.b1ref in (select bob.dbo.b2.b1ref from bob.dbo.b2 except select bob.dbo.b1.b1 from bob.dbo.b1)
/*
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES
(select bob.dbo.b2.b1ref, 'HelloWorld' from bob.dbo.b2 where bob.dbo.b2.b1ref in (select bob.dbo.b2.b1ref from bob.dbo.b2 except select bob.dbo.b1.b1 from bob.dbo.b1))
*/
I'm a bit of a TSQL n00b, so I apologise for what I assume is a rather basic question.
As you can see from the commented out statement above, I need to insert rows (the rows returned in the select statement) into an existing table, not a new table. The documentation says that SELECT INTO creates a new table and that INSERT INTO cannot use SELECT as part of its expression. What would you recommend that I do?
Thank you.
March 20, 2009 at 3:09 am
You can use SELECT in combination with an INSERT only you don't use the VALUES part.
This should work for you:
INSERT INTO [dbo].[b1] ([b1],[b12])
SELECT b2.b1ref, b2.b22
FROM dbo.b2 WHERE dbo.b2.b1ref
IN (SELECT dbo.b2.b1ref
FROM dbo.b2
EXCEPT
SELECT dbo.b1.b1
FROM dbo.b1)
[font="Verdana"]Markus Bohse[/font]
March 20, 2009 at 3:19 am
Thank you very much MarkusB!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply