I mean, if type =1 than join with T1 and if type = 2 then join T2. I have one table T and there is a field type, depending on the value in the type field I have to join with one table T1 or T2. Now it is time to copy the data from the source table with the following statement: We also the "1=2" in the where clause in order to make sure that only the table structure is copied without the data.If we would have more varchar columns in the table, we'll have to repeat this trick for every column.I actually do not mind if we get 1 result back, but in case the inner select would return more than 1 row it will fail the whole statement. I have added the logic "where 1=2" in the inner select statement to make sure that we do not get any records back in the inner select.The inner select is followed by "customer_name" making sure that the table created will have this name for the column created.The customer_name is fetched from the target table and in this way is also created with the same collation as the customer_name column in the CUSTOMERS table in the target database.Here are a few things to note about the statement above: If we go one step further, and create the temporary table ourselves, this may yield another problem, take a look at the script below:ĭropping and re-creating the #TMP_CUSTOMERS prior to inserting records
#Collate definition when copying update
Later on, when we run the update statement the column customer_name in the temporary table's collation setting collide with the target database CUSTOMERS table's customer_name column that has Latin1_General_CI_AI set as the collation for the customer_name column. Latin1_General_CI_AI), so when we copy the data from the source database the #TMP_CUSTOMERS temporary table inherits the collation setting for the customer_name column from the source database which is Latin1_General_CI_AS. In the example above, I have created the source and target tables with different collations (Latin1_General_CI_AS vs. The error is raised because we asked SQL Server to compare two columns (customer_name in table CUSTOMERS and customer_name in table #TMP_CUSTOMERS) which have different collation settings, so SQL Server tells us it cannot compare two strings with different collations. Select * into #TMP_CUSTOMERS from source.CUSTOMERS Īssuming that the source database is called "source" and that the target database is called "target" the query from step 2 above would look like:Ĭannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. If OBJECT_ID('tempdb.#TMP_CUSTOMERS') is not null Insert into CUSTOMERS (customer_id, customer_name, phone_number) Insert into CUSTOMERS (customer_id, customer_name, sales_2010)Ĭustomer_name varchar(20) not null unique, If OBJECT_ID('CUSTOMERS') is not null drop table CUSTOMERS Ĭreate table CUSTOMERS(customer_id int not null primary key,Ĭustomer_name varchar(20) not null unique, sales_2010 int) If exists (select null from sys.databases where name like 'target')Ĭreate database source collate Latin1_General_CI_AS Ĭreate database target collate Latin1_General_CI_AI If exists (select null from sys.databases where name like 'source')
0 kommentar(er)
