INSERT INTO tblUsers	(Ein, FirstName, LastName)
SELECT DISTINCT
    ein
   , first
   , last
FROM
    OLD_tblExtraPeople

INSERT INTO tblUsers	(Ein, FirstName, LastName)
SELECT DISTINCT
   o.ein
   , o.first
   , o.last
FROM
    OLD_tblExtraPeople o
    LEFT JOIN tblUser t ON t.[ein] = o.[ein]
WHERE
    o.[ein] IS NULL

quote:

---

DISTINCT gives me unique rows and I need rows with only one column unique

---

quote:

---

Originally posted by Transact Charlie

And if you don't want to insert any ein that already exists then:

INSERT INTO tblUsers	(Ein, FirstName, LastName)
SELECT DISTINCT
   o.ein
   , o.first
   , o.last
FROM
    OLD_tblExtraPeople o
    LEFT JOIN tblUser t ON t.[ein] = o.[ein]
WHERE
    o.[ein] IS NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Yes, there are some user entries with the same EIN - in that case I only want to copy the first instance and ignore the next ones

Please see the example I added to my last reply.

---

DECLARE @foo TABLE (
	[ein] INT UNIQUE
	, [firstname] NVARCHAR(255)
	, [surname] NVARCHAR(255)
	)

DECLARE @bar TABLE (
	[ein] INT
	, [firstname] NVARCHAR(255)
	, [surname] NVARCHAR(255)
	)

-- Populate @foo
INSERT @foo ([ein], [firstname], [surname])
      SELECT 1, 'Fred', 'Flintstone'
UNION SELECT 2, 'Barney', 'Rubble'
UNION SELECT 4, 'Jonus', 'Venture'

INSERT @bar ([ein], [firstname], [surname])
          SELECT 1, 'Scooby', 'Doo'
UNION ALL SELECT 1, 'SCOOBy', 'DOO'
UNION ALL SELECT 3, 'Brock', 'Samson'
UNION ALL SELECT 3, 'b', 'Samson'
UNION ALL SELECT 3, 'Samson,', 'Brock'

SELECT 'FOO', * FROM @foo
SELECT 'BAR', * FROM @bar

INSERT @foo ([ein], [firstname], [surname])
SELECT
	a.[ein]
	, a.[firstname]
	, a.[surname]
FROM
	(
		SELECT
			ROW_NUMBER() OVER(PARTITION BY [ein] ORDER BY [surname], [firstname]) AS [pos]
			, [ein] AS [ein]
			, [firstname] AS [firstname]
			, [surname] AS [surname]
		FROM
			@bar
	)
	a
	LEFT JOIN @foo f ON f.[ein] = a.[ein]
WHERE
	a.[pos] = 1
	AND f.[ein] IS NULL

SELECT 'FOO AFTER INSERT', * FROM @foo

quote:

---

The order of inserting doesn't really matter as long as we only get unique EINs in the destination table.

I assume that the source table will not be sorted, so the first instance of EIN would be inserted, and next ones would be ignored, but we may as well do some sorting on regDate column.

---

DECLARE @foo TABLE (
	[ein] INT UNIQUE
	, [firstname] NVARCHAR(255)
	, [surname] NVARCHAR(255)
	)

DECLARE @bar TABLE (
	[personId] INT IDENTITY(1,1)
	, [ein] INT
	, [firstname] NVARCHAR(255)
	, [surname] NVARCHAR(255)
	, [regDate] DATETIME
	)

-- Populate @foo
INSERT @foo ([ein], [firstname], [surname])
      SELECT 1, 'Fred', 'Flintstone'
UNION SELECT 2, 'Barney', 'Rubble'
UNION SELECT 4, 'Jonus', 'Venture'

INSERT @bar ([ein], [firstname], [surname], [regDate])
          SELECT 1, 'Scooby', 'Doo', '20090101'
UNION ALL SELECT 1, 'SCOOBy', 'DOO', '20090101'
UNION ALL SELECT 3, 'Brock', 'Samson', '20090101'
UNION ALL SELECT 3, 'b', 'Samson', '20090401'
UNION ALL SELECT 3, 'Samson,', 'Brock', '20090401'
UNION ALL SELECT 10, '', '', '20090401'

SELECT 'FOO', * FROM @foo
SELECT 'BAR', * FROM @bar


INSERT @foo ([ein], [firstname], [surname])
SELECT
	b.[ein]
	, b.[firstname]
	, b.[surname]
FROM
	@bar b

	JOIN (
		SELECT
			[ein] AS [ein]
			, MAX([personId]) AS [personId]
		FROM
			@bar
		GROUP BY
			[ein]
	)
	maxPId ON maxPId.[personId] = b.[personId]

	LEFT JOIN @foo f ON f.[ein] = b.[ein]
WHERE
	f.[ein] IS NULL

SELECT 'FOO AFTER INSERT', * FROM @foo