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.
What is the relationship between table1 and table2 ? which column will give you the "link" between these 2 tables
There is no relationship between table1 n table2. That's the reason I am bit struggled to construct it
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?
Insert into table1(col1, col2, col3)
select max_col1 + ROW_NUMBER() over(order by colz) - 1, colz,colyy
cross join (select MAX(col1) + 1 max_col1 from table1 with (tablockx)) as cj1
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 .
CREATE FUNCTION [dbo].[RemoveSpecialChars] (
WITH RETURNS NULL ON NULL INPUT
--Author: Scott Pletcher.
DECLARE @byte int
WHILE 1 = 1
SET @byte = PATINDEX('%[^0-9A-Za-z]%', @string)
IF @byte = 0
SET @string = STUFF(@string, @byte, 1, '')