You may want to consider partitioning instead of a scalar query.
So something like
insert into New_Table
select
[fst].Account_ID,
[sst].Code
from
First_Source_Table as [fst]
inner join (select
row_number() over(
partition by Account_ID
order by Account_ID ) as [topN],
Account_ID,
Code
from
Second_Source_Table) as [sst]
on ( [sst].Account_ID = [fst].Account_ID )
where
( [topN] = 1 ) --This is your topN query
This should do it:
EXEC sp_configure 'user connections', 0;
RECONFIGURE
That will set the number of connections back to the default, which is 32767.
Once you have SSMS open, disconnect from the server in the "Object Explorer" Window. Then you should be able to create a new query window and connect to it there. Alternately, you could use sqlcmd.exe to connect from the command-line without having SSMS open, then run the command from there.
The setting is stored in the master database, and can be seen through the following statement:
SELECT *
FROM sys.configurations c
WHERE c.name = 'user connections';
sys.configurations
is actually a view, defined as:
CREATE VIEW sys.configurations AS
SELECT configuration_id,
name,
value,
minimum,
maximum,
value_in_use,
description,
is_dynamic,
is_advanced
FROM sys.configurations$
WHERE is_not_use = 0
The only way to see the sys.configurations$
table is through the Dedicated Admin Connection, more commonly known as the DAC. The DAC needs to be turned on through SQL Server Configuration Manager, since it is off by default in SQL Server Express.
Best Answer
You can use SET IDENTITY_INSERT my_table ON before performing the insertion of the data in you local table and on finish use SET IDENTITY_INSERT my_table OFF and your column will take the following value.
Example:
SET IDENTITY_INSERT [dbo].[table] ON
GO
INSERT INTO [dbo].[table] ([CustomerID] ,[FirstName]) VALUES (1 ,'qqq');
INSERT INTO [dbo].[table] ([CustomerID] ,[FirstName]) VALUES (2 ,'eee');
SET IDENTITY_INSERT [dbo].[table] OFF