Insert from two tables

Transact-SQL
1

Hi,
I am new to sql and I am struck in one of the requirement where I need to insert data into a table table1(col1,col2,col3) where col1 is primary key, unique number, and col2 and col3 are string. I will be getting col2 and col3 from table2 where col3 is unique column in table2. I am using something like this but getting some errors. Could you correct me ?

Insert into table1(col1, col2, col3) select max(col1)+1, colz,colyy from table2, table1 ;

Any help would be appreciated.

2

What is the relationship between table1 and table2 ? which column will give you the "link" between these 2 tables

3

There is no relationship between table1 n table2. That's the reason I am bit struggled to construct it

4

So what you want is to find the max value for Col1 in Table1 the use it to seed the value being inserted along with the values from Table2?

5

Insert into table1(col1, col2, col3)
select max_col1 + ROW_NUMBER() over(order by colz) - 1, colz,colyy
from table2
cross join (select MAX(col1) + 1 max_col1 from table1 with (tablockx)) as cj1

1 Like
6

Thanks Scott, that works. Do you know is there a way to remove all special characters from a givnen string ? like it should have only [0-9A-za-z] ? I do not think we can combine replace with regex .

7 
CREATE FUNCTION [dbo].[RemoveSpecialChars] ( 
	@string varchar(200)
	) 
RETURNS varchar(200)
WITH RETURNS NULL ON NULL INPUT 
AS
--Author: Scott Pletcher.
BEGIN
DECLARE @byte int
WHILE 1 = 1
BEGIN
	SET @byte = PATINDEX('%[^0-9A-Za-z]%', @string)
	IF @byte = 0
		BREAK;
	SET @string = STUFF(@string, @byte, 1, '')
END --WHIILE
RETURN @string
END --FUNCTION